Evalute the string name as object where name is dynamically given during runtime in Excel VBA

2
votes

I have an UserForm where user will check the checkbox, which will be copied to the Excel sheet. When opening the UserForm, It will check the data from Excel sheet and while clicked on Apply button on UserForm, UserForm data will be placed in the Excel Sheet.

CheckBox Objects will be like - 

UserFrom Name : Config
CheckBox Names : B1M101, B1M102, B1M103 (will be supplied dyanmically)

Here is my working, (which is not working) - 

For i2 = 1 To 99
        ModuleN = "B1M10"+i2
        BeckDIE = "Config." + ModuleN
        BeckDIV = "Config." + ModuleN
        ExIntCellE = Sheets("Internal").Cells(RowStart + i2, EnableModify)
        ExIntCellV = Sheets("Internal").Cells(RowStart + i2, ValueModify)
        BeckDIE.Enabled = ExIntCellE.Value  'Getting Compile Error Here
        BeckDIV.Value = ExIntCellV.Value    'Getting Compile Error Here
Next i2

So, i am trying to copy the values from Excel Sheet "Internal" to the CheckBoxs in the UserForm. But when i am executing this code, i am getting the Compiler Error : Invalid qualifier Error during the execution - 

BeckDIE.Enabled = ExIntCellE.Value
BeckDIV.Value = ExIntCellV.Value

How can i assign the Excel Sheet Cell Value to the Dynamically created CheckBox ObjectName as string ?

I have tried to execute many forms like Application.Evaluate or Eval functions by only Executing the String Name as Object or Entire Name - 

Application.Evaluate(BeckDIE).Enabled = False
Eval(BeckDIE).Enabled = False
Application.Evaluate(BeckDIE + ".Enabled") = False
Eval(BeckDIE + ".Enabled") = False

But, No luck. It's giving error.

1
vbaexcel
What is your question? You didn't ask one. • Also (sorry but I have to say that) "is not working" is the worst error description you can use. Please always include which errors you get and in which line. If you don't get errors tell what your code actually does versus what you expect it to do. - Pᴇʜ
@Pᴇʜ, Sorry i haven't completed the question, now i have updated - Chandra Sekhar K
Is the checkbox created on a worksheet? Then you can use something like that Worksheets("Sheet1").Shapes("CheckBoxName").OLEFormat.Object.Caption = "aaa". If the CheckBox is on a userform use Controls("CheckBox1").Caption to access it by its name: eg. Controls(BeckDIE).Caption - Pᴇʜ
@Pᴇʜ No, CheckBoxs are created on the UserForm - Chandra Sekhar K
check the second part of my comment. - Pᴇʜ

1 Answers

1
votes

As suggested by @PEH, Following Code has worked for me - 

For i2 = 1 To 99
        ModuleN = "B1M10"+i2
        BeckDIE = ModuleN
        BeckDIV = ModuleN
        ExIntCellE = Sheets("Internal").Cells(RowStart + i2, EnableModify).Value
        ExIntCellV = Sheets("Internal").Cells(RowStart + i2, ValueModify).Value
        Config.Controls(BeckDIE).Enabled = ExIntCellE
        Config.Controls(BeckDIV).Value = ExIntCellV
Next i2