Dynamically adding CheckBoxes - Runtime error 424

Question

Hi i've copy/pasted the code from a TheEngineer-answer - I have slightly modified the code, so it collects data from an array instead of a worksheet. I keep getting RuneTime Error 424, and when I read MS help on Error 424 it says I should enable Microsoft DAO 3.5 Object Library my Excel only have 3.6. I guess newer version? But I am still getting an error. Can someone help me??

This is the code:

Option Explicit

Private Sub UserForm_Initialize()
    Dim LastColumn  As Long
    Dim i           As Long
    Dim chkBox      As MSForms.CheckBox

    Call test ' Here i run array code (The array is filled with data)

    TestArr = UniqueProvisionArray
    LastColumn = UBound(TestArr)

    For i = 0 To LastColumn
        Set chkBox = Me.Controls.Add("Forms.CheckBox.1", "CheckBox_" & i)
        chkBox.Caption = TestArr(i).Value
        chkBox.Left = 5
        chkBox.Top = 5 + ((i - 1) * 20)
    Next i
End Sub

Siddharth Rout · Accepted Answer · 2015-05-21 22:04:10Z

3

You are getting that error because of the line chkBox.Caption = TestArr(i).Value. That is an incorrect way to retrieve data from an array.

Here is a sample code to make it work.

Private Sub UserForm_Initialize()
    Dim LastColumn  As Long
    Dim i           As Long
    Dim chkBox      As MSForms.CheckBox
    Dim TestArr(1)

    TestArr(0) = 1
    TestArr(1) = 2

    LastColumn = UBound(TestArr)

    For i = 0 To LastColumn
        Set chkBox = Me.Controls.Add("Forms.CheckBox.1", "CheckBox_" & i)
        chkBox.Caption = TestArr(i)
        chkBox.Left = 5
        chkBox.Top = 5 + ((i - 1) * 20)
    Next i
End Sub

One other thing...

You may want to change chkBox.Top = 5 + ((i - 1) * 20) to chkBox.Top = 5 + (i * 20) else your first Checkbox will not be visible ;)

edited May 21, 2015 at 22:04

answered May 21, 2015 at 21:55

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

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

2 Comments

ERSTKGN Over a year ago

Thank you soooo much! This is why i love AND hate VBA - One stupid .value messes everthing up, but when it is fixed it its like an orgasm :-D

brettdj Over a year ago

Now that's a compliment!

Collectives™ on Stack Overflow

Dynamically adding CheckBoxes - Runtime error 424

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related