Need one Sub for all CheckBoxes.

[Learner123]

Hi,

I have run into a dead end and was hoping to receive some assistance....

The attached file produces a userform that utilizes code which dynamically adds checkboxes.

Dynamic Checkboxes to feed Cells.xlsm

When macro is initiated, the checkboxes appear and are checked off if their corresponding cells in column BA are tagged as True. This code executes the above:

Private Sub UserForm_Initialize()
Dim Rng As Integer
Dim N As Long 'N tab count
Dim tck As MSForms.CheckBox
Rng = 1

For N = 1 To ActiveWorkbook.Sheets.Count

Set tck = UserForm1.Controls.Add("Forms.CheckBox.1", Sheets(N).Name, True) 'adds a checkbox for each tab that exits
    tck.Caption = Sheets(N).Name 'names checkbox caption
    Range("BB1").offset(Rng, 0).Value = tck.Name 'stores tab name to Column BB
    tck.Value = Range("BA1").offset(Rng, 0).Value 'selects/unselects dynamic checkboxes based on Column BA value
    tck.Top = offset 'aligns checkbox
    offset = offset + 15 'aligns checkbox
Rng = Rng + 1
Next N


End Sub

In addition to having the checkboxes populate with existing column BA entries, I am trying to do the reverse. I am hoping to feed specific cells within column BA when checkboxes are clicked (Feeding corresponding cells with True or False).

I believe I am supposed to utilize a class module but not quite sure how to implement.

I am desperate for some guidance - - please help!!

Thank you in advance

[SamT]

Just one idea

Option Explicit

Dim Tcks As Collection


Private Sub UserForm_Initialize()
    Dim Rng As Integer
    Dim N As Long 'N tab count
    Dim tck As MSForms.CheckBox
    Rng = 1
     
    For N = 1 To ActiveWorkbook.Sheets.Count         
        Set tck = UserForm1.Controls.Add("Forms.CheckBox.1", Sheets(N).Name, True) 'adds a checkbox for each tab that exits
        tck.Caption = Sheets(N).Name 'names checkbox caption
        Tcks.Add tck, tck.Name

        Range("BB1").Offset(Rng, 0).Value = tck.Name 'stores tab name to Column BB
        tck.Value = Range("BA1").Offset(Rng, 0).Value 'selects/unselects dynamic checkboxes based on Column BA value
        tck.Top = Offset 'aligns checkbox
        Offset = Offset + 15 'aligns checkbox
        Rng = Rng + 1
    Next N     
End Sub

Sub X()
Dim tck As Object

For Each tck In Tcks
  Sheets(tck.Name).Range(Etc) = tck.Value
Next
End Sub

Sub Y()
Dim sht As Object

For Each sht In Sheets
  sht.Range(Etc) = Tcks(sht.Name).Value
Next
End Sub

[Learner123]

Thank you for the response SamT, but I receive an error message when running your code. It breaks after this line:

Tcks.Add tck.Name, N

[SamT]

I edited the code while you were trying it out. See the revised code

Tcks.Add tck, tckName

[Learner123]

Sill breaks down at the same spot...

Also, why would there be a need for Sub X () and Sub Y() if we never call for them??

[SamT]

I am desperate for some guidance - - please help!!

[Learner123]

Are there any other excel gurus who'd like to take a stab at developing a solution? I really feel like the answer includes adding a class module but am unsure how to do that exactly...

[SamT]

I will change the Title to reflect the issue.

[p45cal]

See attached.
active sheet updates immediately a checkbox is changed.

[Learner123]

You are awesome - thank you very much!

I was on the right path but had a difficult time executing. Thanks for the enlightenment!

[Kenneth Hobs]

I would highly recommend that you add Option Explicit as the first line in all your Modules, Userforms, etc.

It is unclear to me just what you want. It is best to state goals, not how you want code done to achieve a goal.

I am guessing that you want some sheet activex textbox controls added to each sheet checked in the userform? I guess if you add the userform checkbox controls dynamically that a class may be needed for running a Sub to take an action on check or uncheck events. But even then, a run by commandbutton would work just as well or better.

If you have just a few sheets, it would be best to just code the checkbox options in to begin with for the userform. You can always iterate by sheet index and hide the unneeded sheets. I don't really see a need for checkboxes for each worksheet. It would be easier to maintain to just use a control like a listbox and use Selection or a checkbox option to make the list. Again, a commandbutton would be best to take action on selections or not.

For sheet activex controls, here is an example to add one to show the concept. Note how it sets a linked cell to hold the true/false values for check or not. In production, one would have to get a list of all checkbox controls and check their topleft cell locations and maybe names to make sure that added or deleted ones do not overlap.

Sub MakeOneActiveXTextBoxAndLink()  
  Dim c As Range, s As OLEObject
  With Worksheets("NY")
    Set c = .Range("BA2")
    Set s = .OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=True, _
      DisplayAsIcon:=False, Left:=c.Left, Top:=c.Top, Width:= _
      c.Width, Height:=c.Height)
   s.LinkedCell = c.Address(external:=True)
  End With
End Sub

There are better ways to handle the sheet checkbox issues.
1. Data Validation with True,False list.
2. A sheet selection event, to move a checkbox control there.
3. A sheet selection event to toggle the true/false values.

If you need the userform checkbox class, here is an example. There are many out there. Userform code:

'http://www.mrexcel.com/forum/excel-questions/272752-checkbox-controlsource-visual-basic-applications.htmlDim CheckBoxes() As New Class1


Private Sub UserForm_Initialize()
Dim CCount As Long, c As Control
'place here the code that creates your checkboxes, or elsewhere,
'but surelly before the following procedure
CCount = 0
For Each c In Me.Controls
    If TypeOf c Is MSForms.CheckBox Then
        CCount = CCount + 1
        ReDim Preserve CheckBoxes(1 To CCount)
        Set CheckBoxes(CCount).CheckGroup = c
    End If
Next c


End Sub

Module:

Option Explicit

Public WithEvents CheckGroup As MSForms.CheckBox


Private Sub CheckGroup_Change()
  MsgBox CheckGroup.Value
End Sub

[p45cal]

It would be easier to maintain to just use a control like a listbox and use Selection or a checkbox option to make the list.

Absolutely! Or to stress the point with infixation: Abso-effin-lutely.

[Kenneth Hobs]

Here is an example for doing checkboxes in a listbox. Set the commented options manually in the listbox control or uncomment those two property settings.

Private Sub UserForm_Initialize()  
  Dim a, i&
  ReDim a(1 To Worksheets.Count)
  For i = 1 To Worksheets.Count
    a(i) = Worksheets(i).Name
  Next i
  'ListBox1.ListStyle = fmListStyleOption
  'ListBox1.MultiSelect = fmMultiSelectExtended
  ListBox1.List = a
End Sub


Private Sub CommandButton1_Click()
  ActOnSelectedListboxItems
  Unload Me
End Sub


Private Sub ActOnSelectedListboxItems()
  Dim a, i%, j%
  
  On Error Resume Next
  If Not ListBox1.Selected(ListBox1.ListIndex) = True Then Exit Sub
  'or
  'If ListBox1.Selected(0) = False And ListBox1.ListIndex = 0 Then Exit Sub
  
  ReDim a(1 To 1)
  With ListBox1
    For i = 0 To .ListCount - 1
      If .Selected(i) = True Then
        j = j + 1
        ReDim Preserve a(1 To j)
        a(j) = .List(i, 0)
      End If
    Next i
  End With


  MsgBox Join(a, vbLf)
End Sub

[mikerickson]

Rather than adding checkboxes at run-time, it would be easier to use a multi-select ListBox with .ListStyle = fmListStyleOption.

With ListBox1
    .MultiSelect = fmMultiSelectMulti
    .ListStyle = fmListStyleOption
End With

For N = 1 To ActiveWorkbook.Sheets.Count 
    ListBox1.AddItem Sheets(N).Name
       
    ListBox1.Selected(ListBox1.ListCount - 1) = Range("BA1").offset(Rng, 0).Value 
        
    Rng = Rng + 1 
Next N