Hide Activesheet

[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

 

[Alan Moseley]

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

 

[Risky Dave]

Alan,

Perfect - my thanks.

As a matter of interest, what does

Set MySheet = Nothing

do?

Dave

 

[Alan Moseley]

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

 

[Risky Dave]

Alan,

Thanks.

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

Dave