vba help - worksheet checkboxes addition deletion

[Mallesh23]

Hi Team,

I need one help. I have Below code which


1) Read all sheets and print sheet names in Column D.
2) and add all checkboxes to column E.
3) and after clicking checkboxes it hides that sheet OR Unhides that sheet.


Changes Required.
---------------------------
While opening a workbook if checkbox is already checked in column E.
it should avoid unchecking it or deleting it.


Working 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
                ws.Visible = False
                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

Thanks
mg

 

[Fluff]

Cross posted without links, yet again.
You MUST post links to all other sites where you have asked this question & in future do it without being told.