Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

hyperlink to hidden worksheet (assign to object)

0

Hi,

i want to create hyperlink that link to hidden worksheet. i already find the vba code for that but its only for word (assign to word) not for the object like button that i created(not userform button). i try to assign that code to object before but its doesnt work.

i need your help to make it.

**code for main worksheet

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    LinkTo = Target.SubAddress

    WhereBang = InStr(1, LinkTo, "!")

    If WhereBang > 0 Then

        Mysheet = Left(LinkTo, WhereBang - 1)

        Worksheets(Mysheet).Visible = True

        Worksheets(Mysheet).Select

        MyAddr = Mid(LinkTo, WhereBang + 1)

        Worksheets(Mysheet).Range(MyAddr).Select

    End If   

End Sub

**code for hidden worksheet

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    Worksheets("main menu").Select

    Target.Parent.Worksheet.Visible = False

End Sub

Answer
Discuss

Discussion

Please put CODE tags around your code. To do that, edit your post, select your code and click the CODE button.
don (rep: 1989) Sep 22, '18 at 2:17 am
Add to Discussion

Answers

0

It's not clear what you want to achieve. The macros you posted are designed to respond to a click on a hyperlink. However, I doubt that they would actually work.

On the other hand, you say that you want to link the macro to a button which isn't a userform button. I presume it might be a shape and you wish to use the shape's Assign macro function to assign a macro. The code below is designed for that purpose. It will work on visible and hidden sheets.

Sub MySheet_Click()
    ' 21 Sep 2018
    
    Dim Ws As Worksheet
    
    On Error Resume Next
    Set Ws = Worksheets(Application.Caller)
    Ws.Visible = xlSheetVisible
    Ws.Activate
End Sub

The design requires that you have a shape on your worksheet and that shape's name is the same as the worksheet to be activated by its action. This enables you to have many buttons on a worksheet which activate different worksheets, all using the same code. The trick is to name the button.

To create a suitable button select Insert > Shapes and select the shape you want.
Right-click the shape and change the name in the Name Bar (above cell A1).
Paste the code in a standard code module. Don't use any of the existing code sheets. Instead, insert a new module whose default name is like Module1. You can change the name to what you want.
Once correctly installed the macro will appear in the list of the dialog box that opens when you press the Assign Macro button on the shape's right-click menu.

Edit 24 Sep 2018  ====================================

I have attached a workbook in which the functionality of the above code is demonstrated.

Discuss

Discussion

thanks alot..already try the code given but then still not working.
its keep telling that undefined variable "Sub MySheet_Click()"
karlabe Sep 23, '18 at 9:33 pm
MySheet_Click isn't a variable. It's a macro's name. If VBA thinks it is a variable you must have installed it in a way different from installing a procedure. Please check your installation against that in the workbook which I have now attached to my answer.
Variatus (rep: 4889) Sep 24, '18 at 5:10 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login