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-

	COLUMN Q	COLUMN R
Checkbox ()	Control	Formula	Sheet()		Formulas contained in Column R cells
Checkbox 1	FALSE	TRUE	Sheet4	ROW 38	'=IF(OR(Q40=TRUE,Q41=TRUE),"TRUE","FALSE")
Checkbox 2	FALSE	TRUE	Sheet5	ROW 39	'=IF(OR(Q38=TRUE,Q40=TRUE,Q41=TRUE,Q42=TRUE),"TRUE","FALSE")
Checkbox 3	TRUE	TRUE	Sheet6	ROW 40	'=IF(OR(Q40=TRUE,Q41=TRUE),"TRUE","FALSE")
Checkbox 4	FALSE	TRUE	Sheet7	ROW 41	'=IF(OR(Q40=TRUE,Q41=TRUE),"TRUE","FALSE")
Checkbox 5	FALSE	TRUE	Sheet8	ROW 42	'=IF(OR(Q41=TRUE,Q42=TRUE,Q40=TRUE),"TRUE","FALSE")
		FALSE	Sheet9 Sheet15 Sheet16 Sheet17	ROW 43	'=IF(OR(Q42=TRUE,Q41=TRUE),"TRUE","FALSE")
		FALSE	Sheet10	ROW 44	'=IF(OR(Q41=TRUE,Q42=TRUE,Q38=TRUE),"TRUE","FALSE")
		FALSE	Sheet11	ROW 45	'=IF(OR(Q41=TRUE,Q42=TRUE),"TRUE","FALSE")
		FALSE	Sheet12	ROW 46	'=IF(OR(Q41=TRUE,Q42=TRUE,Q38=TRUE),"TRUE","FALSE")
		FALSE	Sheet13 Sheet19	ROW 47	'=IF(Q41=TRUE,"TRUE","FALSE")
		FALSE	Sheet14	ROW 48	'=IF(OR(Q41=TRUE,Q42=TRUE,Q38=TRUE),"TRUE","FALSE")
		FALSE	Sheet18	ROW 49	'=IF(OR(R45="TRUE",R46="TRUE",R48="TRUE"),"TRUE","FALSE")

<tbody>
</tbody>

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}?