Unhiding spreadhseets upon meeting a condition

[K. Georgiadis]

Gentlemen,

this is a follow up to a post of mine that was ultimately solved by Ken Puls on 12/4/04. Suddenly, however, the code stopped working, possibly because I changed something, somewhere accidentally

As a recap, my goal was to make certain worksheets appear if an option button was clicked and to hide them if the option button was cleared. Based on Ken Puls recommendation, I created an option button from the Control Toolbox and currently have the following code assigned to it:

Private Sub CheckBox1_Change()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, sh4 As Worksheet
Set sh1 = Worksheets("Book Value Based")
Set sh2 = Worksheets("Asset and Multiple Based")
Set sh3 = Worksheets("Synergies")
Set sh4 = Worksheets("Target Company's View")
If CheckBox1.Value = True Then
   sh1.Visible = xlSheetVisible
   sh2.Visible = xlSheetVisible
   sh3.Visible = xlSheetVisible
   sh4.Visible = xlSheetVisible
   MsgBox "Please be certain to complete the following worksheets:" & vbNewLine _
   & vbTab & sh1.Name & vbNewLine _
   & vbTab & sh2.Name & vbNewLine _
   & vbTab & sh3.Name & vbNewLine _
   & vbTab & sh4.Name, vbOKOnly + vbExclamation, "Thank you!"
Else
   sh1.Visible = xlSheetHidden
   sh2.Visible = xlSheetHidden
   sh3.Visible = xlSheetHidden
   sh4.Visible = xlSheetHidden
End If
End Sub

Is there something wrong with the syntax? When I click on the option button, it remains selected but nothing happens an, in fact, I cannot even clear it (the option button is NOT in the design view). Each time I click on it, the following is added after the above listed code:

Private Sub OptionButton1_Click() 
End Sub

I don't know if this code addition is deleterious or if it has nothing to do with my problem.

Your help is greatly appreciated.

KG

[Ken Puls]

Hi KG,

What's weirdest to me is that it was working, as that code is specifically coded for a checkbox, not an optionbutton... The problem with the (single) optionbutton is that there is no way to unselect it once selected, which would be a good reason to go with the Checkbox. (With that you can)

If you'd really like optionbuttons, we'd have to set up a second so that your criteria could be evaluated and then clear the other option button by code.

Does that make sense?

[K. Georgiadis]

Ken, you pointed out right away what I must have changed accidentally. As soon as I replaced the option button with a check box, everything works fine again. Thanks!

PS: what type of syntax would I use if I wanted to hide, say, Rows 3-4 in Sheet1, if the user checked yet another checkbox?

[Ken Puls]

Hi there,

Assuming that the other CheckBox is called CheckBox2, and the Sheet's name is Sheet1:

If Checkbox2.value = True then
   Worksheets("Sheet1").Rows("3:4").EntireRow.Hidden = True
Else
   Worksheets("Sheet1").Rows("3:4").EntireRow.Hidden = False
End If

Hope that helps!

[K. Georgiadis]

Hi ken,

as I indicated in my previous reply, this code works once I reinstated a checkbox in lieu of an option button. I'm only curious why the "Thank you!" message does not appear when clicking "OK." This is not something that I need, I'm just curious why it doesn't happen (and I suspect that I may have screwed up another part of the code )

[K. Georgiadis]

Ken, ignore my previous note (I am having a rocky day!). I thought that "Thank you" was meant to be a response to clicking "OK," but I just noticed that it is just a caption on the message box. Thank you for your help and your patience. I'll mark all of the above as SOLVED