Unhiding spreadsheets upon meeting a condition

[K. Georgiadis]

Hi folks,

I have designed a financial evaluation template that can be used for assessing new products as well as company acquisitions. Because the majority of the analyses will be for the simpler product evaluations, I plan to keep 4 of the worksheets hidden because (a) they are only needed for acquisitions and (b) their mere presence might confuse the user.

Here is what I'm hoping to be able to do:

The first worksheet in the workbook is designed as a "Control Panel" where, among others, the user will be asked to use a checkbox to indicate whether the evaluation pertains to the acquisition of a company. If the user checks the box (a YES answer), I want the hidden worksheets to appear and a pop up message, with an "OK" button, to appear with something like this:

"Please be certain to complete (names of 4 unhidden worksheets)."

The pop up message should disappear as soon as the user clicks OK.

I'm hoping that my objects can be accomplished with IF-THEN statements, or some other technique. Could someone help me with the VBA code for the above steps?

Thanks!

[Ken Puls]

Hi there,


One method as follows:

On your "Control Panel" sheet, create a checkbox from the "Control Toolbox" menu. (Don't use the one from the forms menu here.)

Once created, right click it and choose "view code". Replace this:

Private Sub CheckBox1_Click()
End Sub

with this:


Private Sub CheckBox1_Change()
Dim sh1 As Worksheet, _
sh2 As Worksheet, _
sh3 As Worksheet, _
sh4 As Worksheet
Set sh1 = Worksheets("Sheet2")
Set sh2 = Worksheets("Sheet3")
Set sh3 = Worksheets("Sheet4")
Set sh4 = Worksheets("Sheet5")
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!"
End If
End Sub

You'll want to change the names of the "Sheet1" etc... to the right sheets, but this should work for what you need. If you want to hide the sheets when the box is not checked, you'd need to add an "Else" statment to it with the following for each sh...

shX.visible = xlsheethidden

where X is 1 through 4.

Hope that helps!

[K. Georgiadis]

Hi there,


One method as follows:

On your "Control Panel" sheet, create a checkbox from the "Control Toolbox" menu. (Don't use the one from the forms menu here.)

Once created, right click it and choose "view code". Replace this:

Private Sub CheckBox1_Click()
End Sub

with this:


Private Sub CheckBox1_Change()
Dim sh1 As Worksheet, _
sh2 As Worksheet, _
sh3 As Worksheet, _
sh4 As Worksheet
Set sh1 = Worksheets("Sheet2")
Set sh2 = Worksheets("Sheet3")
Set sh3 = Worksheets("Sheet4")
Set sh4 = Worksheets("Sheet5")
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!"
End If
End Sub

You'll want to change the names of the "Sheet1" etc... to the right sheets, but this should work for what you need. If you want to hide the sheets when the box is not checked, you'd need to add an "Else" statment to it with the following for each sh...

shX.visible = xlsheethidden

where X is 1 through 4.

Hope that helps!
I am about to try this but first a dumb question: I know that a checkbox drawn from the Control Toolbox works in a UserForm but does it work in a regular worksheet? My so-called "Control Panel" is a plain ole worksheet

[Ken Puls]

Hi there,

Yep! In the plain ol Excel interface, just make sure you create a checkbox from the "Control Toolbox" menu. It'll drop an ActiveX control on your sheet, exactly the same as a userform.

Give it a shot, and let me know how you make out.

Cheers,

[K. Georgiadis]

I tried that but I cannot check the box, even when I turn on worksheet protection; when I point to the control, I get Excel's "crosshairs" which only allow me to resize or move the control. What basic step am I missing?

[Ken Puls]

HI there,

Betting that the Design Mode is still turned on. (It's the Tri Square, Ruler & Pencil icon in the Control Toolbox). Click it to un-depress it (is that a real word?), and then try to click the checkbox.

HTH,

[Zack Barresse]

Sounds like you're in Design Mode buddy. Make sure and toggle this button ..

[K. Georgiadis]

I knew that I was missing something very basic. Terrific and works! Mark this as SOLVED. Thanks for your help!

[Zack Barresse]

Done. :)


Btw, to quote Ken's sig line, "Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved! :thumb " You can do this to your own threads. :yes

[K. Georgiadis]

will do!