Creating hyperlinks to workbook tabs using VBA but not using the full file path name as the address?

%3CLINGO-SUB%20id%3D%22lingo-sub-1426849%22%20slang%3D%22en-US%22%3ECreating%20hyperlinks%20to%20workbook%20tabs%20using%20VBA%20but%20not%20using%20the%20full%20file%20path%20name%20as%20the%20address%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1426849%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20created%20a%20contents%20list%20of%20all%20the%20tabs%20in%26nbsp%3Bthe%20workbook%20using%20VBA%20code%20with%20hyperlinks.%3C%2FP%3E%3CP%3EThe%20problem%20is%20that%20if%20the%20file%20name%20changes%2C%20or%20it%20gets%20moved%20the%20hyperlink%20no%20longer%20works.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20use%20the%20%22%3DHYPERLINK(%22%23Sheet2!A1%22%2C%22Sheet2%22)%20formula%20in%20a%20cell%20it%20creates%20a%20link%20that%20is%20independent%20of%20the%20file%20path%20address%20or%20name.%20Using%20the%20VBA%20code%20below%20I%20cannot%20find%20a%20way%20of%20creating%20a%20similar%20hyperlink%20without%20the%20full%20file%20path.%26nbsp%3BI%20have%20also%20tried%20to%20use%20the%20VBA%20code%20to%20create%20the%20above%20formula%20but%20it%20will%20not%20work%20for%20me%26nbsp%3Bas%20a%20result%20of%20the%20%22%23%22%20character.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EActiveCell.Offset(3%2C%201).Activate%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20For%20Each%20wSheet%20In%20Worksheets%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ActiveSheet.Hyperlinks.Add%20Anchor%3A%3DSelection%2C%20Address%3A%3D%22%22%2C%20SubAddress%3A%3DwSheet.Name%20%26amp%3B%20%22!A1%22%2C%20TextToDisplay%3A%3DwSheet.Name%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ActiveCell.Offset(1%2C%200).Select%20'Moves%20down%20a%20row%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Next%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20is%20best%20to%20sort%20this%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3Erob%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1426849%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1430253%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20hyperlinks%20to%20workbook%20tabs%20using%20VBA%20but%20not%20using%20the%20full%20file%20path%20name%20as%20the%20addr%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1430253%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F683607%22%20target%3D%22_blank%22%3E%40Rob_embry%3C%2FA%3E%26nbsp%3BHave%20you%20tried%20to%20wrap%20your%20codes%20with%20%22With%20ThisWorkbook%22%20and%20%22End%20With%22%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlternatively%20if%20you%20want%20to%20use%20Hyperlink%20formula%20in%20your%20VBA%3A%3C%2FP%3E%3CP%3EWith%20ThisWorkbook%3CBR%20%2F%3EActiveCell.Formula%20%3D%20%22%3DHYPERLINK('%22%20%26amp%3B%20wSheet.Name%20%26amp%3B%20%22'!A1%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20wSheet.Name%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I have created a contents list of all the tabs in the workbook using VBA code with hyperlinks.

The problem is that if the file name changes, or it gets moved the hyperlink no longer works.

 

If you use the "=HYPERLINK("#Sheet2!A1","Sheet2") formula in a cell it creates a link that is independent of the file path address or name. Using the VBA code below I cannot find a way of creating a similar hyperlink without the full file path. I have also tried to use the VBA code to create the above formula but it will not work for me as a result of the "#" character.

 

ActiveCell.Offset(3, 1).Activate
    For Each wSheet In Worksheets
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=wSheet.Name & "!A1", TextToDisplay:=wSheet.Name
        ActiveCell.Offset(1, 0).Select 'Moves down a row
    Next

 

What is best to sort this out.

 

Thanks

rob

 

1 Reply
Highlighted

@Rob_embry Have you tried to wrap your codes with "With ThisWorkbook" and "End With"?

 

Alternatively if you want to use Hyperlink formula in your VBA:

With ThisWorkbook
ActiveCell.Formula = "=HYPERLINK('" & wSheet.Name & "'!A1," & Chr(34) & wSheet.Name & Chr(34) & ")"
End With