VBA to Hide/Unhide Worksheets using Checkboxes (FormControl or ActiveX) and IF statement

[michele_said]

I have been trying to create a code that would hide/unhide certain worksheets based on the value formulated in Column R resulting from the Checkbox control value in Column Q indicated by TRUE or FALSE. See below-


[TABLE="class: grid, width: 900"]
<tbody>[TR]
[TD]
[/TD]
[TD]COLUMN Q[/TD]
[TD]COLUMN R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]Checkbox ()[/TD]
[TD="align: center"]Control[/TD]
[TD="align: center"]Formula[/TD]
[TD="align: center"]Sheet()[/TD]
[TD][/TD]
[TD="align: center"]Formulas contained in Column R cells[/TD]
[/TR]
[TR]
[TD]Checkbox 1[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]Sheet4[/TD]
[TD]ROW 38[/TD]
[TD]'=IF(OR(Q40=TRUE,Q41=TRUE),"TRUE","FALSE")[/TD]
[/TR]
[TR]
[TD]Checkbox 2[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]Sheet5[/TD]
[TD]ROW 39[/TD]
[TD]'=IF(OR(Q38=TRUE,Q40=TRUE,Q41=TRUE,Q42=TRUE),"TRUE","FALSE")[/TD]
[/TR]
[TR]
[TD]Checkbox 3[/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[TD]Sheet6[/TD]
[TD]ROW 40[/TD]
[TD]'=IF(OR(Q40=TRUE,Q41=TRUE),"TRUE","FALSE")[/TD]
[/TR]
[TR]
[TD]Checkbox 4[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]Sheet7[/TD]
[TD]ROW 41[/TD]
[TD]'=IF(OR(Q40=TRUE,Q41=TRUE),"TRUE","FALSE")[/TD]
[/TR]
[TR]
[TD]Checkbox 5[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]Sheet8[/TD]
[TD]ROW 42[/TD]
[TD]'=IF(OR(Q41=TRUE,Q42=TRUE,Q40=TRUE),"TRUE","FALSE")[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]FALSE[/TD]
[TD]Sheet9
Sheet15
Sheet16
Sheet17[/TD]
[TD]ROW 43[/TD]
[TD]'=IF(OR(Q42=TRUE,Q41=TRUE),"TRUE","FALSE")[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]FALSE[/TD]
[TD]Sheet10[/TD]
[TD]ROW 44[/TD]
[TD]'=IF(OR(Q41=TRUE,Q42=TRUE,Q38=TRUE),"TRUE","FALSE")[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]FALSE[/TD]
[TD]Sheet11[/TD]
[TD]ROW 45[/TD]
[TD]'=IF(OR(Q41=TRUE,Q42=TRUE),"TRUE","FALSE")[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]FALSE[/TD]
[TD]Sheet12[/TD]
[TD]ROW 46[/TD]
[TD]'=IF(OR(Q41=TRUE,Q42=TRUE,Q38=TRUE),"TRUE","FALSE")[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]FALSE[/TD]
[TD]Sheet13
Sheet19[/TD]
[TD]ROW 47[/TD]
[TD]'=IF(Q41=TRUE,"TRUE","FALSE")[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]FALSE[/TD]
[TD]Sheet14[/TD]
[TD]ROW 48[/TD]
[TD]'=IF(OR(Q41=TRUE,Q42=TRUE,Q38=TRUE),"TRUE","FALSE")[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]FALSE[/TD]
[TD]Sheet18[/TD]
[TD]ROW 49[/TD]
[TD]'=IF(OR(R45="TRUE",R46="TRUE",R48="TRUE"),"TRUE","FALSE")[/TD]
[/TR]
</tbody>[/TABLE]

I have tried both Form Control checkboxes, that result with the macro doing nothing, and ActiveX that results in an debugging error, ambiguous name error, or other. I'm starting to believe this may just not be possible.

PLEASE HELP ME

 

[Yongle]

Clicking a checkbox is easy and familiar to the user but here is a simple bit of code that is much simpler than using checkboxes
- test in a new workbook

After adding the code below, simply click on any cell in column Q (row 2 onwards) to click on or off
- use IF(Q2="P",value if TRUE,value if FALSE) in any formula as your test

Paste code into sheet module
(right click on sheet tab \ select View Code \ paste into code window \ {ALT}{F11} takes you go back to Excel)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row > 1 And Target.CountLarge = 1 And Target.Column = 17 Then
        Target.Font.Name = "Wingdings 2"
        If Target = "[COLOR=#ff0000][B]P[/B][/COLOR]" Then Target.ClearContents Else Target = "[COLOR=#ff0000][B]P[/B][/COLOR]"
        Target.Offset(, 1).Activate
    End If
End Sub

alternative letters you may prefer O, P, Q, R

 

[michele_said]

Thank you for you help Yongle! However, I am trying to hide the corresponding worksheets based on the "Target". [Example using your code above - if Q2="P" then it would hide Sheet5, etc.]

 

[Yongle]

To hide a sheet the code is

Sheets("Sheet5").Visible = False

1. do you want code that hides (unhide special) sheets listed in column S based on value in column R?
2. are the sheets listed in Column S separated with {ALT}{Enter}?