How to Create a Simple Userform with Checkboxes in VBA

Question

I am extremely new to VBA and am trying to create a spreadsheet that uses a checkbox userform to populate a table in a spreadsheet. I have been able to get the table to populate, but if a box is accidentally checked and is unchecked, the table remains populated. How do I get the table to go back to being blank after a box is unchecked and what is an efficient way to code the 33 checkboxes to populate the 33 spaces in the spreadsheet. Please see the images attached to aid in my description.

Thanks,

Userform Image

Spreadsheet Image

If you have a problem with your current code then it should be in your question. — Tim Williams
– Tim Williams, Commented Nov 30, 2017 at 4:39
Name every checkbox as 1a, 1b, 1c then 2a, 2b, 2c. Just write code to get the name of all checked boxes name to your sheet. — Harun24hr
– Harun24hr, Commented Nov 30, 2017 at 4:45

user6432984 · Accepted Answer · 2017-11-30 05:40:05Z

1

Setting the CheckBox ControlSource Property to a range address will link it to the range. If the range isn't qualified A1 the Checkbox will link to the Worksheet that is the ActiveSheet when the Userform Opens. To qualify the address add the Range's parent Worksheet's Name in single quotes followed by a exclamation mark and finally the ranges relative address 'Check List'!A1.

Initially, the Checkbox will be grayed out indicating that the linked cell is empty. When you check and uncheck it the linkedcell value will toggle between True and False.

Demo Userform Code

Private Sub UserForm_Initialize()
    Dim Left As Single, Top As Single
    Dim cell As Range, row As Range, check As MSForms.CheckBox
    Top = 25
    Left = 25
    With Worksheets("Check List")
        For Each row In .Range("A2:K4").Rows
            For Each cell In row.Cells
                Set check = Me.Controls.Add("Forms.CheckBox.1")
                With check
                    .ControlSource = "'" & cell.Parent.Name & "'!" & cell.Address(RowAbsolute:=False, ColumnAbsolute:=False)
                    .Left = Left
                    .Top = Top
                    Left = Left + 12
                End With
            Next
            Left = 25
            Top = Top + check.Height + 2
        Next
    End With
End Sub

answered Nov 30, 2017 at 5:40

user6432984

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

2 Comments

ashleedawg Over a year ago

Hey, how did you record an animated gif screenshot and even include the mouse cursor?? (I'm overtired and I thought I was seeing things!)

M. Albert Over a year ago

Thanks for this, I got most of it to work. However I have a hard time getting the demo userform code to actually work. I ended up having to reference all the ControlSources manually. When i try to run the code it gives me a compile error: User-defined type not defined for the check As MSForms.CheckBox ... Any idea how to fix this or do I have to manually enter the ControlSource for each checkbox? Thanks again.

Collectives™ on Stack Overflow

How to Create a Simple Userform with Checkboxes in VBA

1 Answer 1

Demo Userform Code

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Demo Userform Code

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related