How make VBA run on clicking any checkbox in a userform?

Question

I have a userform with a multiple frames, all filled with multiple checkboxes. I've named the checkboxes to their corresponding Excel cells. Now I want to make VBA run on clicking any of these checkboxes on run time. I know I can do this by creating a click-sub for every individual checkbox, but there must be a cleaner way to do this.

So far I've tried to put this code in the userform_Click and userform_Mousedown events, but they don't run when I click the checkboxes. Does anyone have an idea how to do this?

Dim iControl As Control

For Each iControl In Me.Controls
    If TypeName(iControl) = "CheckBox" Then
        If iControl.Value = True And Range(iControl.Name).Value = "" Then
            Range(iControl.Name).Value = Format(Now, "dd.mm.yyyy")
        ElseIf iControl.Value = True And Range(iControl.Name).Font.Color = vbWhite Then
            Range(iControl.Name).Font.Color = vbBlack
        ElseIf iControl.Value = False And Range(iControl.Name).Value <> "" Then
            Range(iControl.Name).Font.Color = vbWhite
        End If
    End If
Next

Have you enabled Macros? Have you ensured that you workbook is NOT an XLSX file? Only XSL, XLSB and XLSM files can run macros. — Pieter Geerkens
– Pieter Geerkens, Commented Jan 19, 2016 at 13:21
Yes, all other macro's work perfectly. Also, if I click on the userform (so not on a checkbox and outside of the frames), the code like it's supposed to. — RobK
– RobK, Commented Jan 19, 2016 at 13:28

Darren Bartrup-Cook · Accepted Answer · 2016-01-19 14:01:21Z

4

As SilentRevolution said - you need an event to fire when you click the button. You're just after a single procedure to fire all check box click events.

So: Create a class module called cls_ChkBox. In the class module you'll add the Click event code:

Option Explicit

Private WithEvents chkBox As MSForms.CheckBox

Public Sub AssignClicks(ctrl As Control)
    Set chkBox = ctrl
End Sub

Private Sub chkBox_Click()
    ThisWorkbook.Worksheets("Sheet1").Range(chkBox.Name).Value = Format(Now, "dd.mm.yyyy")
End Sub

Now you just need to attach the chkBox_Click event to each check box on your form. In your user form add this code:

Option Explicit

Private colTickBoxes As Collection

Private Sub UserForm_Initialize()

    Dim ChkBoxes As cls_ChkBox
    Dim ctrl As Control

    Set colTickBoxes = New Collection

    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "CheckBox" Then
            Set ChkBoxes = New cls_ChkBox
            ChkBoxes.AssignClicks ctrl
            colTickBoxes.Add ChkBoxes
        End If
    Next ctrl

End Sub

Each check box is given its own instance of the class, which is stored in the colTickBoxes collection.

Open the form and the cell in Sheet1 will update to show the date depending on the name of the check box.

answered Jan 19, 2016 at 14:01

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

SilentRevolution Over a year ago

I didn't know that you could do this, I'm going to save this for later, I have a project this would be perfect on.

Darren Bartrup-Cook Over a year ago

I mainly use it when I need an indeterminate number of controls on a form - you can add the controls at run-time and attach an event each one.

Nathan_Sav Over a year ago

It's good to use the tag property this way also, for custom updating, combo1 updates the combo with the name of its tag for example.

Darren Bartrup-Cook Over a year ago

Yep, the Tag property can be very helpful - good for storing the old value of a control plus a million and one other uses. :)

RobK Over a year ago

This is a whole new territory for me... Thanks, it works perfectly! You guys should teach me! :)

SilentRevolution · Accepted Answer · 2016-01-19 13:52:21Z

0

You need an event to run code, if there is no event, the macro cannot start. I don't know if there is a single event that triggers for any button or checkbox that is clicked.

If the code you want to execute is the same every time except the control, you could write a private sub in the userform module which is called for each event, the private sub can take an input between the () for example.

Private Sub CheckBox1_Click()
    Call ExecuteCode(Me.CheckBox1)

End Sub

Private Sub CheckBox2_Click()
    Call ExecuteCode(Me.CheckBox2)

End Sub

Private Sub CheckBox3_Click()
    Call ExecuteCode(Me.CheckBox2)

End Sub

Private Sub ExecuteCode(IControl As Control)
    If TypeName(IControl) = "CheckBox" Then
        If IControl.Value = True And Range(IControl.Name).Value = "" Then
            Range(IControl.Name).Value = Format(Now, "dd.mm.yyyy")
        ElseIf IControl.Value = True And Range(IControl.Name).Font.Color = vbWhite Then
            Range(IControl.Name).Font.Color = vbBlack
        ElseIf IControl.Value = False And Range(IControl.Name).Value <> "" Then
            Range(IControl.Name).Font.Color = vbWhite
        End If
    End If

End Sub

edited Jan 19, 2016 at 13:52

answered Jan 19, 2016 at 13:29

SilentRevolution

1,5132 gold badges16 silver badges31 bronze badges

1 Comment

RobK Over a year ago

Thanks, but this is the solution I didn't want. I have a little over 100 checkboxes, so I'm looking for a single event that runs every time I click any of the checkboxes.

slfan · Accepted Answer · 2017-06-23 20:20:56Z

I just learned this today and thought it might help? Most of the questions, and responses for that matter, to questions regarding checkboxes, listboxes, etc. seem to not distinguish between those forms are inserted directly on the worksheet or are imbedded in a UserForm.

This may work for a checkbox - it works for a ListBox on a UserForm. If you want code to run after a selection, the code you must write has to be in module of the UserForm. I had no clue how to access. Once you have inserted a UserForm in your VBE, add the ListBoxes or whatever to the UserForm. Right click on the UserForm in the upper left project window and select "view code." Here is where you'll place the code, in my case a "change event" such that after a selection is made from the ListBox, other code is automatically run. Her for example:

Sub lstBoxDates_Change()
   Dim inputString1 As String
   inputString1 = Format(UserForm1.lstBoxDates.Value, "mm-dd-yyyy")
   Call EnterDates(inputString1)
   Unload Me
End Sub

To explain: Again this code is in the UserForm Module. I named my ListBox, lstBoxDates. In my main code that I call - EnterDates, I use the variable name = inputString1. The value or date that I have selected from the ListBox is captured from the UserForm1 by UserForm1.lstBoxDates.Value - and I format that to a date, otherwise you see just a number. This is for only one selection. Because I Dim here, no need to Dim in your main code. The sub for the main code needs to accept the variable you are passing to it:

Sub EnterDates(inputString1)

This is very generalized but maybe something will click so you can get what you are after. I hope so, for I've worked on this a full two days!!

Collectives™ on Stack Overflow

How make VBA run on clicking any checkbox in a userform?

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related