VBA UserForm - Making ActiveX Control Visible based on cell reference.

Question

Hello everyone,

I need to be able to make a particular ActiveX Control visible using the name of the control which is located in a cell reference.

I have a range of cells I need to cycle through to see if they have something. If they do, using offset, we'll take the name of something (which is the control name) and then display it. Check out my code below, it might be clearer.

For Each Cell In Range("B5:B22")

    If Not Cell.Value = "" Then

        Dim AUTO_List As String

        Dim AUTO_Discharge As String

        Dim AUTO_Load As String

        AUTO_List = Cell.Offset(0, -1).Value

        AUTO_Discharge = Cell.Offset(0, 1).Value

        AUTO_Load = Cell.Offset(0, 3).Value

        Me.AUTO_List.Visible = True

        Me.AUTO_Discharge.Visible = True

        Me.AUTO_Load.Visible = True

    End If

Next Cell

This code will cycle through all the cells in B5:B22. If the cell contains something it will run the "control visible" section. The control name, the exact name, is located on an offset. I then want to take the value of the offset cell to then make the control visible ("Me.AUTO_List.Visible = True" would be "Me.D1_PB_A_R1_List.Visible = True").

Any ideas? It must be possible but I've obviously got the syntax wrong.

Thanks,

Daniel

***Post moved by the moderator to the appropriate forum category.***

Answer 1

Try:

    Dim c As Control

    For Each Cell In Range("B5:B22")

        'First, reset all the controls to not visible

        'Use logic like this if there are specific controls to hide

        'For example, all the labels whose names start with lbl

        For Each c In Me.Controls

            If c.Name Like "lbl*" Then c.Visible = False

        Next c

        'Use logic like this to hide all controls

'        For Each c In Me.Controls

'            c.Visible = False

'        Next c

        If Cell.Value <> "" Then

            Me.Controls(Cell.Offset(0, -1).Value).Visible = True

            Me.Controls(Cell.Offset(0, 1).Value).Visible = True

            Me.Controls(Cell.Offset(0, 3).Value).Visible = True

        End If

    Next Cell

Answer 2

That's perfect. Thank you Bernie!