How to use VBA to read the values of a checkbox from an Excel userform

Question

I have created a userform that contains two checkboxes. I would like to be able to do different things depending on whether each box is checked or unchecked. However, it seems like no matter what I do, it will always tell me the original value of the checkboxes (false and false). Here is the code attached to clicking CommandButton1:

Private Sub CommandButton1_Click()

ReadData

End Sub

And here ReadData:

Sub ReadData()

Dim myForm As UserForm
Set myForm = UserForms.Add("ComplaintEntryForm")

Debug.Print (myForm!CheckBox1.Name)
Debug.Print (myForm!CheckBox1.Value)
Debug.Print (myForm!CheckBox2.Name)
Debug.Print (myForm!CheckBox2.Value)

End Sub

No matter how the boxes are checked, the immediate window always shows this:

VBA.UserForms.Add("ComplaintEntryForm").Show
CheckBox1
False
CheckBox2
False

I have a screenshot of the whole operation but it won't let me upload it because I'm a new user.

barrowc · Accepted Answer · 2012-09-12 00:52:12Z

2

Try this method to load and show the form (this goes in a normal module):

Sub main()

Dim myForm As ComplaintEntryForm

Set myForm = New ComplaintEntryForm
myForm.Show
Set myForm = Nothing

End Sub

In the UserForm's own module, add the following:

Private Sub CheckBox1_Change()

readData

End Sub

Private Sub CheckBox2_Change()

readData

End Sub

Private Sub UserForm_Initialize()

Me.CheckBox1.Value = True
Me.CheckBox2.Value = False

End Sub

Private Sub readData()

Debug.Print Me.CheckBox1.Name
Debug.Print Me.CheckBox1.Value
Debug.Print Me.CheckBox2.Name
Debug.Print Me.CheckBox2.Value

End Sub

I've initialized the two checkboxes to specific values in the Initialize event. This means we are certain about the state the form will start in

answered Sep 12, 2012 at 0:52

barrowc

10.7k2 gold badges47 silver badges59 bronze badges

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

1 Comment

user1664313 Over a year ago

Hey, thank you so much for the response; that works perfectly. I was just trying to avoid having to enter too much code in the UserForm's own module since I'm creating the UserForm on the fly, and the only way I know how to add code to the UserForm is like this: With TempForm.CodeModule .InsertLines 1, "Sub CommandButton1_Click()" .InsertLines 2, "'Do stuff" .InsertLines 3, "End Sub" End With Which is a pain, especially with quotations. But it turned out I only needed like 18 lines, so that was okay. Thank you very much for your help!

Collectives™ on Stack Overflow

How to use VBA to read the values of a checkbox from an Excel userform

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related