Dynamically Add CheckBoxes - Runtime Error 424
Hi I copied / pasted the code from TheEngineer's answer. I modified the code a bit so it collects data from an array instead of a worksheet. I keep getting RuneTime Error 424
and when I read the MS help on Error 424
, it says that I should enable Microsoft DAO 3.5 Object Library
, my Excel only has 3.6
. Think new version? But I am still getting the error. Can anyone 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
source to share
You are getting this error because of the line chkBox.Caption = TestArr(i).Value
. This is the wrong way to retrieve data from an array.
Here is some 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 more thing ...
You can change chkBox.Top = 5 + ((i - 1) * 20)
to chkBox.Top = 5 + (i * 20)
otherwise your first checkbox will not be visible;)
source to share