Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hiding sheets | Excel Worksheet Functions | |||
need code for hiding/unhiding sheets using checkboxes | Excel Worksheet Functions | |||
hiding zero values on all sheets & by default on new sheets | Excel Worksheet Functions | |||
Locking Sheets / Hiding Sheets | Excel Worksheet Functions | |||
Hiding Sheets | Excel Programming |