VBA - Executing a row as a command in Excel

Question

For the last 30 minutes, I have been trying to execute a line in VBA as a command. The command is like this, and it works fine:

activesheet.chb_g_ba1.visible = true

What I'm trying to do is set chb_g_ba1 as a variable, because there is also chb_g_ba2, chb_g_ba3, etc.

What I have tried so far are things like:

dim l_counter as long: l_counter = 1
Evaluate (CStr("me.chb_g_ba" & l_counter & ".visible = true"))

Or even Eval

Eval (CStr("me.chb_g_ba" & l_counter & ".visible = true"))

Eval is a function in MS Access VBA, but obviously it is not in Excel. To a large extent, my question is very similar to this one: VBA, how to run a line as a line of code , but this is for Excel, not Access.

So any ideas are welcome! :)

Answer 1

Pearson Software Consulting

, , .

: Microsoft Visual Basic 5.3

Sub test()
    Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
    VBComp.Name = "NewModule"
    Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("NewModule").CodeModule
    Dim l_counter As Long
    l_counter = 1
    With VBCodeMod
        LineNum = .CountOfLines + 1
        .InsertLines LineNum, _
        "Sub MyNewProcedure()" & Chr(13) & "UserForm1.chb_g_ba" & l_counter & ".Visible = True" & Chr(13) & "End Sub"
    End With
    'run the new module
    Application.Run "MyNewProcedure"
    UserForm1.Show
    'Delete the created module
    ThisWorkbook.VBProject.VBComponents.Remove VBComp
End Sub