Hiding sheets using code
 

i have a spreadsheet with sheets named as follows

St1, St2, St3, St4, St5 etc...

I would like to enter a number into a cell on a separate sheet that will
result in
only showing the sheets up to that number and hide the rest.

For example if i enter 3, I would like St1, St2 and St3 to show and St4, St5
etc
to be hidden.

Can anyone help?

Hiding sheets using code
 

For i = 1 to Range("B2").value
Worksheets("St" & i).Visible = xlSheetHidden
Next i

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Roger on Excel" wrote in message
...
i have a spreadsheet with sheets named as follows

St1, St2, St3, St4, St5 etc...

I would like to enter a number into a cell on a separate sheet that will
result in
only showing the sheets up to that number and hide the rest.

For example if i enter 3, I would like St1, St2 and St3 to show and St4,
St5
etc
to be hidden.

Can anyone help?

Hiding sheets using code
 

Roger,

Right click your 'other' shhet tabe (Menu in my case) view code and oaste
this in

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$1" Then
If IsNumeric(Target) Then
On Error Resume Next
Application.EnableEvents = False
For i = 1 To worksheets.Count
If worksheets(i).Name < "Menu" And Val(Right(worksheets(i).Name,
1)) Target.Value Then
worksheets(i).Visible = False
Else
worksheets(i).Visible = True
End If
Next
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End Sub

Enter a number in A1 of that sheet and sheets will become hidden or visible

Mike

"Roger on Excel" wrote:

i have a spreadsheet with sheets named as follows

St1, St2, St3, St4, St5 etc...

I would like to enter a number into a cell on a separate sheet that will
result in
only showing the sheets up to that number and hide the rest.

For example if i enter 3, I would like St1, St2 and St3 to show and St4, St5
etc
to be hidden.

Can anyone help?

Hiding sheets using code
 

Excellent - Thanks very much - perfect !!!


All the best,

Roger

"Mike H" wrote:

Roger,

Right click your 'other' shhet tabe (Menu in my case) view code and oaste
this in

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$1" Then
If IsNumeric(Target) Then
On Error Resume Next
Application.EnableEvents = False
For i = 1 To worksheets.Count
If worksheets(i).Name < "Menu" And Val(Right(worksheets(i).Name,
1)) Target.Value Then
worksheets(i).Visible = False
Else
worksheets(i).Visible = True
End If
Next
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End Sub

Enter a number in A1 of that sheet and sheets will become hidden or visible

Mike

"Roger on Excel" wrote:

i have a spreadsheet with sheets named as follows

St1, St2, St3, St4, St5 etc...

I would like to enter a number into a cell on a separate sheet that will
result in
only showing the sheets up to that number and hide the rest.

For example if i enter 3, I would like St1, St2 and St3 to show and St4, St5
etc
to be hidden.

Can anyone help?

Hiding sheets using code
 

By the way I have sheets named St10, St11, St12, yet these do not disappear -
is there a modification to the code to take account of these sheets also?

"Mike H" wrote:

Roger,

Right click your 'other' shhet tabe (Menu in my case) view code and oaste
this in

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$1" Then
If IsNumeric(Target) Then
On Error Resume Next
Application.EnableEvents = False
For i = 1 To worksheets.Count
If worksheets(i).Name < "Menu" And Val(Right(worksheets(i).Name,
1)) Target.Value Then
worksheets(i).Visible = False
Else
worksheets(i).Visible = True
End If
Next
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End Sub

Enter a number in A1 of that sheet and sheets will become hidden or visible

Mike

"Roger on Excel" wrote:

i have a spreadsheet with sheets named as follows

St1, St2, St3, St4, St5 etc...

I would like to enter a number into a cell on a separate sheet that will
result in
only showing the sheets up to that number and hide the rest.

For example if i enter 3, I would like St1, St2 and St3 to show and St4, St5
etc
to be hidden.

Can anyone help?