Program Userform CheckBox that is Programmatically Added in Excel VB

Question

This is the code I have to programmatically add checkbox in a userform in Excel:

Private Sub UserForm_Activate()

    Me.Height = btm + 40

    Me.Width = wth + 10

End Sub

Private Sub UserForm_Initialize()

    btm = 0

    wth = 0

    For Each s In Array("Elementary", "Elementary8")

        t = 10

        w = wth

        For i = 7 To 11

        r = ActiveCell.Column

        c = Worksheets(s).Range("A" & i)

        a = Worksheets("Elementary").Cells(i, r)

        If a = "" And c <> "" Then

            With UserForm2.Controls.Add("Forms.CheckBox.1", "myControl" & i, True)

                    .Top = t

                    .Left = 10 + w

                    t = t + 20

                    .Caption = Worksheets(s).Range("A" & i)

                    If btm < .Top + .Height Then btm = .Top + .Height

                    If wth < .Left + .Width Then wth = .Left + .Width

                End With

            End If

        Next i

    Next s

End Sub

This is the code for checkbox that were created manually in an excel userform:

Private Sub Yes_Click()

ActiveCell.Resize(5).Value = UserForm1.TextBoxName.Value

Unload Me

End Sub

I would like the programmatically created CheckBox to perform a funciton similar to the manually created CheckBox.  The programmatically created CheckBox are created and named according to dates in Column A of the spreadsheet.  I would like to program them to perform the following function:

When Clicked

Lookup date in column A to match the CheckBox date name, copy and paste the value from UserForm TextBoxName into the cell that is in the row of the matching date and in the column of the active cell.

Answer 1

I used the following code in the class module: <br><br>Private WithEvents myControl As MSForms.CheckBox <br><br>Public Property Set Ctrl(NewVal As MSForms.CheckBox) <br><br>Set myControl = NewVal <br><br>End Property <br><br>Private Sub myControl_Click() <br><br>ActiveCell = "Christina" <br><br>'I used ActiveCell as I am unsure what I should enter to perform the function to match the value of the CheckBox caption to the date in Column A, then enter a value 3 columns over.' <br><br>End Sub <br><br>I used the following code in the standard module: <br><br>Sub ShowForm() <br><br>Dim frm As UserForm2 <br><br>Dim cbx As MSForms.CheckBox <br><br>Dim ocbx As CCheckBox <br><br>Set mCln = New Collection <br><br>Set frm = New UserForm2 <br><br>For Each cbx In frm.Controls <br><br>If TypeName(cbx) = "CheckBox" Then <br><br>ActiveCell.Value = "Christina" <br><br>'I used ActiveCell as I am unsure what I should enter to perform the function to match the value of the CheckBox caption to the date in Column A, then enter a value 3 columns over.' <br><br>Set ocbx = New CheckBox <br><br>Set ocbx.Ctrl = ctl <br><br>mCln.Add ocbx <br><br>End If <br><br>Next cbx <br><br>frm.Show <br><br>End Sub <br><br>The checkbox still performs no functions when clicked. Any advice on how to correct this?

Answer 2

Computerkitten,

John Walkenbach documents how to do it at his site:

http://spreadsheetpage.com/index.php/site/tip/handle_multiple_userform_buttons_with_one_subroutine/

that is for commandbuttons.  But the technique would be the same for you.

--

Regards,

Tom Ogilvy