Prev: Making Plug-In Macros Visible for Custom QAT Access
Next: How to load specific range of data from html into excel?
From: Brian on 30 Jan 2010 23:47
I have a user form with several Check Boxes that are for controlling
worksheets in a workbook.

Each check box is for hiding or unhiding the different worksheets in a
workbook from the user form2.

Workbook name = Installer Forms
Worksheet name = Sheet 1
Checkbox names = Office_Package_Preparations_101

I want when the Box is checked for the worksheet to be shown and when the
box is not checked for the worksheet to be hidden.






From: OssieMac on 31 Jan 2010 01:16
Hi Brian,

Important consideration is that you cannot hide all sheets so must test for
at least 2 visible sheets before hiding a sheet. If only one sheet visible
then unchecking the last checkbox is ignored and it is re-checked. All of
your CheckBox code should be like the following.

You could if you wish place the main code in a standard module and call it
from the CheckBox click events and pass the worksheet name to the code.

Private Sub ChkSht1_Click()

Dim ws As Worksheet
Dim i As Long

'Edit ChkSht1 to your CheckBox Name
If Me.ChkSht1 = True Then
'Edit "Sheet1" to your sheet name
Sheets("Sheet1").Visible = True
Else
'Cannot hide all sheets so at least
'2 must be visible before hiding one.
For Each ws In Worksheets
If ws.Visible Then
i = i + 1
If i > 1 Then Exit For
End If
Next ws

If i > 1 Then
Sheets("Sheet1").Visible = False
Else
'Set back to True (Visible)
Me.ChkSht1 = True
End If
End If
End Sub

--
Regards,

OssieMac


 | 
Pages: 1
Prev: Making Plug-In Macros Visible for Custom QAT Access
Next: How to load specific range of data from html into excel?