• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Macros to open hyperlink linked to hidden sheets that has hyperlink too

Eryn.K

New Member
Hi, i have a workbook containing plenty sheets.
I would like to set the sheet2 @ Overview as my dashboard. Which, when i click link in Overview, it will unhide sheet linked and naviagate to it. Then, hide that opened sheet when i go go back to Overview.

I found a macros fulfilling the above needs perfectly.

However, i would like to get help to get a code that can achieve the below approach:
1. Let assumes the Overview dashboard sheet is A.
2. When i click link in A, it will unhide & redirect me to the corresponded hidden sheet(Assumes its B).
3. When i click on hyperlink in B, it will also unhide and redirects me to the hidden sheet C.
4. When i go back to A, the rest of the sheets will hide again.

View attachment 87109

Perhaps there were someone here can help or guide me through to achieve the above solutions.
Ps: I was completely new to the macros coding. Hence it is appreciated if you are willing to share the code to me directly or can teach me how to code it. I'm eager to learn :)

Thanks in advanced!
 
Eryn.K
Why do You would like to use ... links?
You wrote I found a macros fulfilling the above needs perfectly, but
You skipped to show that.
... Your View attachment 87109 ... is missing or so?
Could You use something like in this sample?
# those A... F are like shortcuts to sheets
# by selecting any of those - You'll see that sheet
# by selecting Overview-sheet, Your previous selected sheet will hide
 

Attachments

  • ErynK.xlsb
    23.4 KB · Views: 1
Eryn.K
Why do You would like to use ... links?
You wrote I found a macros fulfilling the above needs perfectly, but
You skipped to show that.
... Your View attachment 87109 ... is missing or so?
Could You use something like in this sample?
# those A... F are like shortcuts to sheets
# by selecting any of those - You'll see that sheet
# by selecting Overview-sheet, Your previous selected sheet will hide

Hi vletm, appreciate for the helps.

Attach with files that i have just created for your reference, the code that "I found a macros fulfilling the above needs perfectly" is in there. The previously attached picture is actually to show that macro but it missed though.

The code you share is having the same function i mentioned above.

Recently(after this thread is posted) i found out that the approach i mention above had been achieved with that macro.

The macro did point me from A to any other sheet & cell it linked, and will hide those sheets when i leave. Just that when i click on the shape(with link) at B, nothing happened. I thought the macro applied only work in sheet A (as it was all text with link). Hence this thread written in that way.

While looking for "solutions", i accidently click on the text with link in the hidden page B, the macro worked and will bring me to C.

Now only i realized the root cause here is the macro i used is not applicable to shape(with link).

Thus, i would like to correct my request - is anyone can guide me through how to create macros that can redirect me to the hidden sheet when a shape(with link) is clicked?

*****
Regarding to the queries, "Why do You would like to use ... links?"
Hmm i am actually working to create a product information type of data library for my teams.
There were org. chart created with shapes, people do added some links in there, e.g A!B2, so when we click on one of the item in the chart, it will redirect us to the corresponded sheet & cell explaining more about it.

Everything work just fine all this while, no macro involved. But then my boss request to hide all unused sheet and leave only Overview automatically to get the content looks clean and neat.

It is not necessary to use link, just that it was the most convenient way i can guess when trying to link the org. chart to others.
 

Attachments

  • TEST.xlsm
    18.8 KB · Views: 2
Last edited:
Eryn.K
Here is one sample based click on the shape feature.
With that, You can select sheets.
 

Attachments

  • ErynK.xlsb
    32.8 KB · Views: 4
Eryn.K
Here is one sample based click on the shape feature.
With that, You can select sheets.

Sorry for the late reply. I had been checked on your file and have a try. The macro work fine in your file.

I'm actually trying to understand how that work and replicate it. So with referred to what you did, i create a Module 1 with the below macro copied from your file:
Code:
Sub Click_Sheet()
    If s = Empty Then s = ActiveSheet.Name
    Sh = ActiveSheet.Shapes(Application.Caller).Name
    With Sheets(Sh)
        .Visible = -1
        .Activate
    End With
    Sheets(s).Visible = 0
    s = Sh
End Sub

Then this macro is assigned to my Shape in sheet A.
When i try run it, it shows the Application.Caller = Error 2023 as follow:
83516
I tried resolved it myself but still couldnt understand why this could happend. Some other people met with same error 2023 says "When a function is called by another VBA function, it isn't a range, and therefore the Set statement will fail. "

Was it because the newly added macro is not compatible or clash with the macro i used under ThisWorkBook hence the failure? o_O

Attached with the file with errors.
 

Attachments

  • TEST.xlsm
    19.8 KB · Views: 2
Eryn.K
Why Your code is in Module1?
Why there are missing the 1st line of my code? ... everything matters.
Why do You have used ... links ... with Your shapes?
If You so, then why do You try to use that my code?
Did You try to read - what do that my code tries to do?
#1 it'll change .Visible -value based ActiveSheet
#2 it'll change .Visible -value based previous sheet
You try to use that my code other ways.
 
Back
Top