help with macro

[Ron99]

Hello....

I have two hyperlink created in sheet1, first hyperlink says (sheet2) second hyperlink says (sheet3), when I click on sheet2 hyperlink it takes me to sheet2 the same with hyperlink sheet3.

macro that I need help on is, after clicking sheet2 hyperlink I am on sheet2, when I click sheet1, sheet2 should be hidden. basically when I click on sheet1 whichever sheet after sheet1 should be hidden, but when I click on hyperlink the sheet should be shown.

Regards,
Ron..

 

[Ron99]

Please help!!

 

[Trevor G]

what you can do is in the workbook events for on open is hide your sheets less sheet1 then when you click your hyperlink add the code to make the sheet visible. Then when you close the book in the before close event make sure the sheets are hidden again. This code goes in the workbook events, so use Alt + F11 then double click on the right where it says thisworkbook, change the first drop down to Workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets(2).Visible = False
Sheets(3).Visible = False
End Sub
Private Sub Workbook_Open()
Sheets(2).Visible = False
Sheets(3).Visible = False
End Sub

On sheet 1 place this code in the worksheet event, so right click the tab and then select view code then change the first drop down to worksheet then the second to Hyperlink

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Sheets(2).Visible = True
Sheets(2)Select

End Sub

 

[Ron99]

Hey thanx Trevor!

I did exactly the same, I am getting an error in this code

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Sheets(2).Visible = True
Sheets(2)Select

End Sub

I am using excel 2007

 

[Trevor G]

In the VBA screen are the sheets named as 2 or are they named as something else. So do they show the names as Sheet2(Sheet2)?

 

[Ron99]

It is named as Sheet1,Sheet2 and Sheet3

 

[Trevor G]

Do you have any protections on the sheets? other than that without seeing the workbook I can't help.

 

[Ron99]

I do not have any protection on the sheet,

when I click on hyperlink it takes me to the sheet, which works fine,

but when I click on sheet1, other sheets should hide, which isn't happening

Regards,
Ron...

 

[Trevor G]

In sheet1 view the code by right clicking the sheet tab then change the drop down at the top to worksheet then on the right select activate, the code you need is below.

Private Sub Worksheet_Activate()
Sheets(2).Visible = False
Sheets(3).Visible = False
End Sub

 

[Ron99]

HI...we are almost there, sheet works just fine when I click on sheet1, it hides the other sheets, but now when I am clicking the hyperlink, sheet2 or sheet3 it is not opening.