Hide Activesheet

  • Thread starter Thread starter Risky Dave
  • Start date Start date
R

Risky Dave

Hi,

A variation on the hide a worksheet theme.

I have a series of worksheets (obviously with different names) accessed from
a central Navigation page. On each of the worksheets is a "Back" button that
returns the user to the Navigation page and hides the sheet.

Currently I have a different macro assigned to each back button that does
the same thing:

<name of current sheet here>.Protect
Sheets(<name of current sheet here>).Hidden = True
Sheets("Navigation Page").Select

What I would like to do is have a single macro that works for all sheets, so
it would look something like:

ActiveSheet.Protect
ActiveSheet.Hidden = True
Sheets("Navigation Page").Select

The only problem is I can't work out the syntax for the ActiveSheet lines.

Any help would be much appreciated.

TIA

Dave
 
Try this:-

Dim MySheet As Worksheet
Set MySheet = ActiveSheet
Sheets("Navigation Page").Select
MySheet.Protect
MySheet.Visible = xlSheetHidden
Set MySheet = Nothing
Sheets("Navigation Page").Activate
 
Alan,

Perfect - my thanks.

As a matter of interest, what does

Set MySheet = Nothing

do?

Dave
 
Every time you create a variable it uses up memory. Setting the variable
back to nothing frees up that memory.
 
Alan,

Thanks.

I'm still teaching myself this VB so it's good to pick up this points along
the way.

Dave
 
Back
Top