VBA Variable within "Name"

Question

I've got several check boxes to which I am going to change the visibility and caption of based on the index of a for loop. I have an array of 1 to X. Into my form I am passing along the array total and each array element is a string.

Anyway, on my worksheet I am passing:

Sub Stripdown_Button_Click()
    LastUpdateColumn = Sheets("Update").UsedRange.Columns.Count
    Header_Array = Range(Cells(7, 1), Cells(7, LastUpdateColumn)).Value
    Header_Form.Header_Select LastUpdateColumn, Header_Array()
    Header_Form.Show
End Sub

LastUpdateColumn will be an integer, Header_Array will be an array of strings.

My form, which I am probably completely screwing up at this point is as follows...

Public Sub Header_Select(Index As Integer, Header_List() As String)

    For x = 1 To Index
        If Header_List(1) <> "" Then
            cb & Index.Visible = True
            cb & Index.Caption = Header_List(Index)
        Else
            MsgBox "Form Error. Contact Engineering", vbOKOnly
        On Error Resume Next
    End Sub

VB.NET and VBA are two completely different BASIC dialects. If you are working with MS Access, Word or Excel, you are probably using VBA. If you are working with Visual Studio, you are probably using VB.NET. — Olivier Jacot-Descombes
– Olivier Jacot-Descombes, Commented Jun 6, 2016 at 19:10

Olivier Jacot-Descombes · Accepted Answer · 2016-06-06 19:14:32Z

1

You cannot create a variable name from a string; however the forms have a Controls default property accepting a control name as index

Dim cb As CheckBox

Set cb =  Controls("cb" & Index)
cb.Visible = True
cb.Caption = Header_List(Index)

Since the property is the default property, this other syntax also works:

Set cb =  Me("cb" & Index)

answered Jun 6, 2016 at 19:14

Olivier Jacot-Descombes

114k14 gold badges149 silver badges202 bronze badges

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

2 Comments

user6140865 Over a year ago

Genius, thanks so much. I just ran into another issue. It's not letting me pass "Range(Cells(7, 1), Cells(7, LastUpdateColumn)).Value" into my array variable. It's going into "Type mismatch". Everything is set as string. All the data is definetly simple text headers.

Olivier Jacot-Descombes Over a year ago

Set a breakpoint on the line with the problem and inspect the value. google.ch/search?q=how+to+debug+vba

Collectives™ on Stack Overflow

VBA Variable within "Name"

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