CODEWITHSUNDEEP
vbaexcel
Bill Woodward
Bill Woodward

Reputation: 49

edit hyperlink and display specific text from partial entry of userform textbox

I have a userform that has a textbox entry and then sends it to a specific location. The contents of the textbox is a hyperlink. What I want it to do is update the hyperlink in the excel form and display the last portion of the URL. For example https://stackoverflow.com/questions/ask ............ the Hyperlink I update is always the same and the last portion is what changes. for example:

https://stackoverflow.com/questions/ask

https://stackoverflow.com/questions/this

https://stackoverflow.com/questions/question

https://stackoverflow.com/questions/yippeeee

I know I can call another textbox entry in my userform, but I am trying to avoid more steps for the user as much as possible. Is there anyway to do this? Here is what code I have:

Sub hyperlink()

' hyperlink Macro

Dim rng As Range
Set rng = Range("C3:I13")

Str = "https://somelink.com/blahblah/THIS_IS_WHAT_I_WANT_TO_DISPLAY"

Range("C3:I13").Select
Application.CutCopyMode = False
Selection.Hyperlinks(1).Address = "textbox2.text"
Selection.Hyperlinks(1).TextToDisplay = "THIS_IS_WHAT_I_WANT_TO_DISPLAY"
End Sub

Thank you very much in advance.

Upvotes: 0

Views: 218

Answers (1)

paul bica
paul bica

Reputation: 10705

Extract the last part of the string like this

  • str = Right(str, Len(str) - InStrRev(str, "/"))
Option Explicit

Public Sub UpdateHyperLink()

    Dim rng As Range, str As String
    Set rng = Range("C3:I13")

    str = "https://somelink.com/blahblah/THIS_IS_WHAT_I_WANT_TO_DISPLAY"

    str = Right(str, Len(str) - InStrRev(str, "/"))

    Application.CutCopyMode = False

    With rng.Hyperlinks(1)
        .Address = "textbox2.text"
        .TextToDisplay = str        '<--- str = "THIS_IS_WHAT_I_WANT_TO_DISPLAY"
    End With
End Sub

Upvotes: 1

Related Questions