Macro HyperlinksClosed

Question

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

TrowaD · Answer

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 SubSheets(Target.Value).Visible = TrueSheets(Target.Value).ActivateEnd 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

Macro Hyperlinks

1 response

Similar discussions

Subscribe To Our Newsletter!