Checkbox Control to Hide/Unhide Worksheet

[Peter]

I am tyring to create control sheet using forms control that would hide or
unhide depending if the box is checked or not.
Ideally, when the box is checked it would pass the worksheet name to my vba
code to hide/unhide the named worksheet.

Where I'm running into trouble is creating a macro that can be assigned to
the checkbox.

Not sure if I am using the best approach to doing this and would appreciate
any suggestions.

Thanks,
Peter

 

[Arvi Laanemets]

Hi

In project module, create an UDF

Public Function SheetName(SheetIndex As Integer) As String
Application.Volatile
SheetName = Sheets(SheetIndex).Name
End Function

On worksheet Control, create columns p.e. Worksheet Names (col A) and
Visiblity Status (col B)
A2=IF(ISERROR(SheetName(ROW(A1))),"",SheetName(ROW(A1)))
and copy down for as much rows you think you need
Select the range in column B, equal to one with formulas in A, and format it
with Data.Validation.List, Source=Hide,Show

Right-click onto Control worksheet's tab, select 'View code', and select in
left droptown list of code window 'Worksheet'. A Change event for worksheet
is created. Enter the code for event - something like:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Column = 2 And ActiveCell.Row > 1 Then
If ActiveCell.Offset(0, -1).Value <> "" Then
If ActiveCell.Value = "Hide" Then
Sheets(ActiveCell.Row - 1).Visible = xlSheetHidden
ElseIf ActiveCell.Value = "Show" Then
Sheets(ActiveCell.Row - 1).Visible = xlSheetVisible
End If
End If
End If
End Sub

It's not flawless - somehow the sheet is hidden/unhidden not when you change
the value in Status column, but only after you reselect the changed cell.
Sorry, but I don't have time to search for better solution at moment - maybe
you find it yourself, or somebody here helps.


Arvi Laanemets

 

[steve]

Peter,

I did this in Excel 97.

Put check boxes from the Forms Toolbar on your worksheet. Change the
caption of the checkboxs to the worksheet names.

Than use this code for each checkbox (besure to have CB1, CB2, etc)
Or better yet use a command button to fire the macro ShowHideSheet.

ShowHideSheet will look at all the checkboxes and hide/unhide the sheets.

Sub CheckBox1_Click()
ShowHideSheet
End Sub


Sub ShowHideSheet()

Dim OLEObj As OLEObject
Dim myCBX As CheckBox

For Each myCBX In ActiveSheet.CheckBoxes
If myCBX.Value = 1 Then
Sheets(myCBX.Caption).Visible = True
Else
Sheets(myCBX.Caption).Visible = False
End If
Next myCBX

End Sub