0

Basically, I have code, that looks at a "schedule" sheet for a list of team members names. It creates two sheets per member and then creates a checkbox on my "Report Builder" sheet (my main action screen). There are also some backwards checks, but that is also working fine. This list will expand or contract depending on adding or removing team members in the "schedule" tab. Warning: I am not a VBA expert, I only play around when inspired. The code below works for creating the checkboxes and tabs:

Sub ActX_Add_Multiple_CheckBox()

'Disable Screen Update
Application.ScreenUpdating = False

'Variable Declaration
Dim Rng As Range
Dim ShtRng As Range
Dim WrkSht As Worksheet
Dim RenChk As Object

Sheets("Schedule").Select
Range("c11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Report Builder").Select
Range("k1").Select
ActiveSheet.Range("K1").EntireColumn.PasteSpecial Paste:=xlPasteValues
Columns("k:k").Sort key1:=Range("k1"), order1:=xlAscending, Header:=xlNo

Range("k1").Select
Set ShtRng = Range(Selection, Selection.End(xlDown))

Set WrkSht = Application.ActiveSheet

For Each Rng In ShtRng
    Set RenChk = Sheets("Report Builder").OLEObjects.Add("Forms.CheckBox.1", Left:=Rng.Left, Top:=Rng.Top, Width:=150, Height:=Rng.Height)
    RenChk.Object.Caption = Rng
    Rng.ClearContents
    
Next


'Enable Screen Update
Application.ScreenUpdating = True

End Sub

I need this to build these click events, and i would like to have it loop through instead of me writing each click event separately, manually:

Private Sub CheckBox1_Click()
Dim cbName As String
cbName = CheckBox1.Caption
    If CheckBox1.Value = True Then
        Sheets(cbName & " TS").Visible = True
        Sheets(cbName & " TS (EDIT)").Visible = True
    Else
        Sheets(cbName & " TS").Visible = False
        Sheets(cbName & " TS (EDIT)").Visible = False
    End If
End Sub

Any suggestions would be helpful (First time poster here)

Michael_Unoriginal
  • 1
  • 1
  • unfortunately, no. I have other "always existing" checkboxes on the same sheet. One difference, while all are active x, the "always Existing" ones have names with start with cmb as i think is a default with excel. The ones i am creating "on the fly" do not start with cmb, possibly something i can do with that. – Michael_Unoriginal Sep 27 '22 at 14:05
  • That linked approach will work - you only need to check the name of the checkbox isnot like "cmb*" when populating the array with class instances. – Tim Williams Sep 27 '22 at 18:40
  • Actually, thank you very much for the quick response, i actually scrapped the checkbox idea for a listbox instead and that is actually working better for what i would like to do. I would still like to mark your answers as correct. – Michael_Unoriginal Sep 27 '22 at 19:31

0 Answers0