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 Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Risky Dave" wrote:

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,

Perfect - my thanks.

As a matter of interest, what does

Set MySheet = Nothing

do?

Dave

"Alan Moseley" wrote:

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 Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Risky Dave" wrote:

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

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

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Risky Dave" wrote:

Alan,

Perfect - my thanks.

As a matter of interest, what does

Set MySheet = Nothing

do?

Dave

"Alan Moseley" wrote:

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 Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Risky Dave" wrote:

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,

Thanks.

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

Dave

"Alan Moseley" wrote:

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

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Risky Dave" wrote:

Alan,

Perfect - my thanks.

As a matter of interest, what does

Set MySheet = Nothing

do?

Dave

"Alan Moseley" wrote:

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 Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Risky Dave" wrote:

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