vba help - hide/unhide sheets as per worksheet checkbox click

[Mallesh23]

Hi Team,

Need your help, I have 4 checkboxes in column E.
if user select whichever checkboxes corrensponding sheets needs to get hide or unhide.
check = unhide
uncheck = hide

please suggest how to achieve this task. suggest vba code or event. thanks in advance !

Tasks.xlsm
	A	B	C	D	E
1				Sheetnames	Hide/Unhide
2				Sheet1
3				sheet2
4				sheet3
5				sheet4
6
Master

attached image of excel.

I have added checkboxes via below code.

Sub Addcheckboxes()
 
'Declare variables and data types
Dim cell, LRow As Single
Dim chkbx As CheckBox
Dim CLeft, CTop, CHeight, CWidth As Double
 
'Don't refresh or update screen while processing macro, this will make the macro quicker.
Application.ScreenUpdating = False
 
 
'Iterate through 2 to last non empty cell
For cell = 2 To 5
    
    'Check if cell in column A is not equal to nothing
    If Cells(cell, "A").Value = "" Then
 
        'Save cell dimensions and coordinates of corresponding cell in column E to variables
        CLeft = Cells(cell, "E").Left
        CTop = Cells(cell, "E").Top
        CHeight = Cells(cell, "E").Height
        CWidth = Cells(cell, "E").Width
 
        'Create checkbox based on dimension and coordinates data from variables
        ActiveSheet.CheckBoxes.Add(CLeft, CTop, CWidth, CHeight).Select
        With Selection
            .Caption = ""
            .Value = xlOff
            .Display3DShading = False
        End With
    End If
Next cell
 
'Turn on screen refresh
Application.ScreenUpdating = True
 
End Sub

Thanks
mg

 

[John_w]

Try this code.

Public Sub Add_Checkboxes()

    Dim MasterWs As Worksheet, ws As Worksheet
    Dim cb As CheckBox
    Dim r As Long

    Application.ScreenUpdating = False
    
    With ActiveWorkbook
    
        Set MasterWs = .Worksheets("Master")

        Delete_Checkboxes MasterWs

        r = 1
        For Each ws In .Worksheets
            If Not ws Is MasterWs Then
                ws.Visible = xlSheetVisible
                r = r + 1
                With MasterWs
                    .Cells(r, "D").Value = ws.Name
                    Set cb = .CheckBoxes.Add(.Cells(r, "E").Left, .Cells(r, "E").Top, .Cells(r, "E").Width, .Cells(r, "E").Height)
                End With
                With cb
                    .Caption = ""
                    .Value = xlOn
                    .Display3DShading = False
                    .Name = "CB_" & r
                    .OnAction = "CheckBox_Click"
                End With
            End If
        Next
        
    End With

    Application.ScreenUpdating = True

End Sub


Public Sub CheckBox_Click()

    Dim cb As CheckBox
    
    'Application.Caller is name of Checkbox that was clicked
    
    With ActiveSheet
        Set cb = .CheckBoxes(Application.Caller)
        Worksheets(.Cells(cb.TopLeftCell.Row, "D").Value).Visible = (cb.Value = xlOn)
    End With
    
End Sub


Private Sub Delete_Checkboxes(ws As Worksheet)
    With ws.CheckBoxes
        While .count > 0
            .Item(1).Delete
        Wend
    End With
End Sub

 

[Mallesh23]

Hi John_w.

Thanks for your help, it worked. liked the code. ?


Thanks
mg