Macro Hyperlinks

Closed
Boffee95 Posts 1 Registration date Monday January 6, 2014 Status Member Last seen January 6, 2014 - Jan 6, 2014 at 03:48 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jan 16, 2014 at 11:57 AM
hello
i already have this code in my macro that works fine
s
ub New_Personal_Profile()
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Sheet1").Range("D12")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
ActiveWorkbook.Sheets(MyCell.Value).Visible = xlSheetHidden

Next MyCell

End Sub



I am now trying to add a new stage

sheet1 has a list of about 200 names and i need a macro that will make a new sheet, name that new sheet then hide it (above code works fine) but i now want it to make a link from the name in sheet 1 to the sheet for that name and am struggling to find a way

Names are in D12 downwards


Thanks for any help much appreciated

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jan 16, 2014 at 11:57 AM
Hi Boffee,

You can't use hyperlinks on hidden sheets.

Try this code by implementing it in sheet1:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range(Cells(12, "D"), Cells(12, "D").End(xlDown))) Is Nothing Then Exit Sub
Sheets(Target.Value).Visible = True
Sheets(Target.Value).Activate
End Sub

Whenever you double click on a sheet name in column D (row 12 and down) then the sheet will be made visible and activated.

You probably want to add another code to each individual sheet to hide the sheet again, when the sheet is deactivated for example.
Or you can hide all sheets again whenever sheet1 is selected. But that is up to you.

Best regards,
Trowa
0