Hide or display worksheets based on form

S

Shawnn

Worksheet 1 displays a list of questions. Based on the answer of those
questions (via checkbox), I would like to display different worksheets. For
example the worksheet one is always displayed. If you select yes to question
#1, then worksheet 2 appears. If you select yes to question #2 then
worksheet 3 also appears. Can this be done?
 
S

Simon Lloyd

Yes of course it can be done, using code similar to below, it goes in
the sheet1 code module:

Code:
--------------------
Private Sub CheckBox1_Click()
Dim Sh As Worksheet
For Each Sh In Sheets
If Sh.Name = "Sheet1" Or Sh.Name = "Sheet2" Then
Else
Sh.Visible = xlSheetHidden
End If
End Sub
Private Sub CheckBox2_Click()
Dim Sh As Worksheet
For Each Sh In Sheets
If Sh.Name = "Sheet1" Or Sh.Name = "Sheet3" Then
Else
Sh.Visible = xlSheetHidden
End If
End Sub
Private Sub CheckBox3_Click()
Dim Sh As Worksheet
For Each Sh In Sheets
If Sh.Name = "Sheet1" Or Sh.Name = "Sheet4" Then
Else
Sh.Visible = xlSheetHidden
End If
End Sub
--------------------
Shawnn;164128 said:
Worksheet 1 displays a list of questions. Based on the answer of those
questions (via checkbox), I would like to display different worksheets.
For
example the worksheet one is always displayed. If you select yes to
question
#1, then worksheet 2 appears. If you select yes to question #2 then
worksheet 3 also appears. Can this be done?
Click to expand...


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
 
S

Shane Devenshire

Hi,

Assuming you have a separate checkbox for each sheet the code for each would
look something like this

Private Sub CheckBox1_Click()
If CheckBox1 = True Then
Sheet2.Visible = xlSheetVisible
Else
Sheet2.Visible = xlSheetHidden
End If
End Sub
 
S

Shane Devenshire

Hi again,

I realize I should add that in the notation

Sheet2.Visible = xlSheetVisible

Sheet2 is the code windows name for the sheet if you use the spreadsheet
name you must write the above line as

Sheets("Sheet2").Visible = xlSheetVisible
 
Top