CODEWITHSUNDEEP
excelvba
kazSone
kazSone

Reputation: 105

hyperlink in visual basic excel

what i want in the code is that when i click this cell B3 in sheet1 it will direct me to A5 in sheet2 then vice versa, when i click A5 in sheet2 it will bring back me to B3 in sheet1 https://i.sstatic.net/qUngD.jpg

Sub Macro3()
'
' Macro3 Macro
'

'
    Range("B3").Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    "Sheet2!A5", TextToDisplay:="gg"
    Sheets("Sheet2").Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    "Sheet1!B3", TextToDisplay:="gg"
    Sheets("Sheet1").Select
End Sub

now my problem is what if the name of the sheet is userdefined? there's a messagebox that says you should input the name of the sheet. so the sub address now changes it is not just "Sheet2" anymore. for instance you put in the msgbox "123", the name of the sheet would now become "Sheet2 123".

Upvotes: 1

Views: 4307

Answers (1)

user2140173
user2140173

Reputation:

You should take advantage of indexing of worksheets collection.

Sub IndexingSheets()
    Sheets(1).Range("B3").Formula = _
     "=HYPERLINK(""#" & ThisWorkbook.Sheets(2).Name & "!A5"", ""TextToDisplay"")"
    Sheets(2).Range("A5").Formula = _
     "=HYPERLINK(""#" & ThisWorkbook.Sheets(1).Name & "!B3"", ""TextToDisplay"")"
End Sub

This code assumes your sheet1 and sheet2 are always the first two sheets in the workbook.

But if you wanted to ask for the name then you could use this instead:
code checks if the sheet already exists ( so you can refer to it ). If it does then it executes the macro and if it doesn't then it calls the procedure recursively to ask for another name.

Dim sheetExist As Boolean

Sub PrefNamedSheets()

    Dim shName$, i&
    shName = InputBox("Whats the second sheet name?")
    For i = 1 To Worksheets.Count
        If StrComp(CStr(Sheets(i).Name), shName, vbTextCompare) = 0 Then
            sheetExist = True
        End If
    Next i
    If sheetExist Then
        ActiveSheet.Range("B3").Formula = _
         "=HYPERLINK(""#" & shName & "!A5"", ""TextToDisplay"")"
        Sheets(shName).Range("A5").Formula = _
         "=HYPERLINK(""#" & ThisWorkbook.Sheets(1).Name & "!B3"", ""TextToDisplay"")"
    Else
        Call PrefNamedSheets
    End If
End Sub

You may also find this link useful!

Upvotes: 1

Related Questions