Macro Hyperlinks [Closed]

Report
Posts
1
Registration date
Monday January 6, 2014
Status
Member
Last seen
January 6, 2014
-
Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
-
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 reply

Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
448
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!