Using a Macro to look at Hidden Sheet

A

alice

I have several sheets (sheet A) that correspond to another sheet (sheet B).
Sheet B needs to be hidden, however I am trying to record a marco that when
you click on the button it will take you to the hidden sheet.

I also need it so that when you're in the sheet (or if it's simpler, when
you leave the sheet) it remains hidden and the tab doesn't show at the bottom.

Can anybody help?!?

Thanks
alice
 
P

PCLIVE

Create a button and use the following code to unhide the sheet..

Sub HideSheetB ()
Sheets("sheet B").visible = True
End Sub


To have it hidden when you leave the sheet, right click on the sheet and
select View Code. Paste this code.

Private Sub Worksheet_Deactivate()
Sheets("sheet B").Visible = False
End Sub


HTH,
Paul
 
A

alice

I'm a little confused as to how the first part should look. Currently it
looks as below but is telling me there's an error.

Sub HideCHM()
Sheets("C H M").Visible = True
End Sub


However when you first go to paste it in it has the below there. Does this
need to stay?

Sub Button12_Click()

End Sub


Thanks
 
P

PCLIVE

Paste the one line of code between the 'Sub Button12_Click()' and 'End Sub'.
It should look like this.

Sub Button12_Click()
Sheets("C H M").Visible = True
End Sub

HTH,
Paul
 
A

alice

Eekk I'm being a pain I know!

The code for the button doesn't seem to be working. I've put in the below
and it comes up with the following:

Run time error '9':

Subscript out of range
 
D

Dave Peterson

PCLive tried to hide a sheet named "C H M". If you don't have a sheet with that
name, you'll get that error.

Try changing the "C H M" to the name of the sheet to hide.

Remember that at least one sheet has to be visible, too.
 
P

PCLIVE

In addition to Dave's message, be sure that you use the same context in your
code to match the actual sheet name. You may also want to activate the
sheet after the button is pressed.

Sub Button12_Click()
Sheets("C H M").Visible = True
Sheets("C H M").Activate
End Sub
 
A

alice

Yay it's working now that the Activate is there.

thanks
alice x

PCLIVE said:
In addition to Dave's message, be sure that you use the same context in your
code to match the actual sheet name. You may also want to activate the
sheet after the button is pressed.

Sub Button12_Click()
Sheets("C H M").Visible = True
Sheets("C H M").Activate
End Sub
Click to expand...
 
Top