How To Hyperlink to a Hidden Worksheet - Excel VBA

**Adsk2007** · 05-12-2022, 10:25 AM

Good day to all,
I have an issue that I hope someone here can help me resolve it. I looked at other similar threads but none seem to solve my issue. The difference between other threads and my problem is that I am using a "Symbol" for all my hyperlinks to go to a specific hidden sheet. How do I define each symbol in a different cell to link to it's related hidden sheet? Please see attached image to understand the issue better.

TYIAMenu Page.JPG

**carlmon** · 05-12-2022, 03:27 PM

A simple macro will accomplish this... not sure about hyperlinking. I've never looked into that.

right-click the image (little orange triangles) and assign it a macro:

Edit: I see they are not images but characters... you could draw in little clear boxes behind the triangles and assign a macro to that instead. There are ways to click on a cell and perform an action but I think a shape is easier.

Sub gotosheet()


with worksheets("Whatever Sheet You want the button to go to")
.Visible = xlSheetVisible
.activate
end with

'When you leave this sheet you could have a code that hides the sheet again:

'with worksheets("Whatever Sheet You want the button to go to")
'.Visible = xlSheetHidden
'end with

end sub

**Adsk2007** · 05-12-2022, 03:36 PM

Hi Carlmon

Ok, I just saw your full code. I will give it a try and let you know.

Thank you

**carlmon** · 05-12-2022, 03:37 PM

Sounds good. If you like this approach, I'll write you a code that hides the cell when selecting the main tab.

P.S I've worked in an aerospace nut/bolt manufacturing plant... I hope you like your job more than I liked mine! lol

**Adsk2007** · 05-12-2022, 03:41 PM

I think I will go with your code. I could import the triangle as an image and use your code that way. I'll appreciate if you also give me the code to hide the sheet.
Thank you very much for your help

**Adsk2007** · 05-12-2022, 03:44 PM

HTML Code:

I know exactly what you mean. Where I work, those who assigned part numbers to each part did a terrible job. It is a nightmare to organize them properly

**carlmon** · 05-12-2022, 04:04 PM

Originally Posted by Adsk2007

HTML Code:

I know exactly what you mean. Where I work, those who assigned part numbers to each part did a terrible job. It is a nightmare to organize them properly

Yeah, my place had over 800 employees & ran 24/7, 7 days a week, 365 --- things got chaotic..

Give this code(below) a go; just make sure you put it on the Main sheet code found in the project explorer or by right-clicking the tab at the bottom. I didn't debug it, but I will.

Private Sub Worksheet_Activate()

    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
If ws.name <> "MENU" then
ws.visible = xlSheetHidden
next ws
    Next 
End Sub

**Adsk2007** · 05-12-2022, 04:08 PM

Thank you, I will give it a try as soon as I get home and let you know by tomorrow.

Best Regards

**Adsk2007** · 05-12-2022, 04:36 PM

Your code works like a charm. One small error i found in your second code. You didn't end the IF statement. Below is what I changed it to and It worked fine.

Thank you for all your help

Private Sub Worksheet_Activate()

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "MENU" Then
ws.Visible = xlSheetHidden
    End If
Next ws

End Sub

**Greg M** · 05-12-2022, 04:52 PM

Hi there,

Here's an approach which allows you to keep sheetnames out of your code, and uses only a single common routine for all of the sheet selection buttons in the workbook.

The destination sheetnames are entered in the cells immediately to the left of the sheet selection buttons. The same process is used for the "Return To Menu" button on each of the worksheets. The columns which contain the sheet names (C for the Menu worksheet and A for each of the other worksheets) may be hidden if required.

The attached workbook uses the following code:




Option Explicit


Private Sub SelectWorksheet()

    Dim rSheetNameCell  As Range
    Dim sShapeName      As String
    Dim sSheetName      As String
    Dim shp             As Shape
    Dim wks             As Worksheet

    sShapeName = Application.Caller
    Set shp = ActiveSheet.Shapes(sShapeName)

    Set rSheetNameCell = shp.TopLeftCell.Offset(0, -1)

    sSheetName = rSheetNameCell.Value

    Worksheets(sSheetName).Visible = xlSheetVisible

    For Each wks In Worksheets

        If wks.Name <> sSheetName Then
            wks.Visible = xlSheetHidden
        End If

    Next wks


End Sub

Hope this helps - please let me know how you get on.

Regards,

Greg M

**carlmon** · 05-12-2022, 04:59 PM

@Greg

My code only references the sheet name of the MAIN sheet that he won't change... it doesn't matter what the rest of the sheets are called - they will be hidden...

Apologies, I see what his code does... looks good.

**Adsk2007** · 05-12-2022, 04:55 PM

Hi Greg

Thank you for helping with this issue. I will try the code and let you know

Regards

**Adsk2007** · 05-12-2022, 04:57 PM

Greg,

Wow, that is also a great way of doing it.

Regards

**Greg M** · 05-12-2022, 05:05 PM

Thanks to you both for your feedback - glad you like it!

Regards,

Greg M

How To Hyperlink to a Hidden Worksheet - Excel VBA

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How To Hyperlink to a Hidden Worksheet - Excel VBA

Re: How To Hyperlink to a Hidden Worksheet - Excel VBA

Re: How To Hyperlink to a Hidden Worksheet - Excel VBA

Re: How To Hyperlink to a Hidden Worksheet - Excel VBA

Re: How To Hyperlink to a Hidden Worksheet - Excel VBA

Re: How To Hyperlink to a Hidden Worksheet - Excel VBA

Re: How To Hyperlink to a Hidden Worksheet - Excel VBA

Re: How To Hyperlink to a Hidden Worksheet - Excel VBA

Re: How To Hyperlink to a Hidden Worksheet - Excel VBA

Re: How To Hyperlink to a Hidden Worksheet - Excel VBA

Re: How To Hyperlink to a Hidden Worksheet - Excel VBA

Re: How To Hyperlink to a Hidden Worksheet - Excel VBA

Re: How To Hyperlink to a Hidden Worksheet - Excel VBA

Re: How To Hyperlink to a Hidden Worksheet - Excel VBA

Thread Information

Users Browsing this Thread

Similar Threads

Hyperlink to hidden sheet using a cell with the =Hyperlink formula

Hyperlink to hidden worksheet, issue

Keep hidden rows hidden, in a protected, filtered worksheet

Hyperlink to a hidden sheet (in the same worksheet)?

[SOLVED] Hyperlink a cell to a hidden worksheet

[SOLVED] How do I detect hidden worksheets or hidden data on a worksheet?

[SOLVED] Hyperlink to hidden worksheet

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions