When assigning values to a UserForm from an array how do I switch between box types..."Caption&-CodePudding

eg A "Label" the value is stored in "Caption" whilst a Textboxes uses "Value" Please...

...but elegantly

I know I could use and "if then" statement ... but I see it getting messy pretty quick.

Dim propValue(2, 2) As String
    propValue(0, 1) = "SomeInfo"
    propValue(1, 1) = "lblReference"
    propValue(2, 1) = "Caption"
    propValue(0, 2) = "MoreInfo"
    propValue(1, 2) = "tboxReference"
    propValue(2, 2) = "Value"

Dim element As Integer
For element = 1 To 2
   UserForm1.Controls(propValue(1, element)).propValue(2, element) = propValue(0, element)
Next element

Many thanks.

CodePudding user response：

As I tried saying in my comment, it is not possible to use a string (extracted from an array or not) as an Object Property. The next code store a string defining the control type and use it in the following way:

Sub testModifFormControlsProperties()
   Dim propValue(2, 2) As String
    propValue(0, 1) = "SomeInfo": propValue(1, 1) = "lblReference"
    propValue(2, 1) = "Label"    'store the control type as String
    
    propValue(0, 2) = "MoreInfo": propValue(1, 2) = "tboxReference"
    propValue(2, 2) = "TextBox" 'store the control type as String
    
    Dim element As Integer, ctrl As MSForms.Control
    For element = 1 To 2
        Set ctrl = UserForm1.Controls(propValue(1, element)) 'to make a compact code
        Select Case propValue(2, element)
            Case "TextBox": ctrl.Text = propValue(0, element)
            Case "Label": ctrl.Caption = propValue(0, element)
        End Select
    Next element
    UserForm1.Show
End Sub

CodePudding user response：

For the additional price of a very simple data object, the following is an approach that avoids using a 2-d Array. You could use a Collection or an Array with this implementation.

Option Explicit

Private Sub InitTextBoxesAndLabels()
        
    Dim propValue(1) As Variant
    Set propValue(0) = CreateTextBoxOrLabel("Label1", "MoreInfo", True)
    Set propValue(1) = CreateTextBoxOrLabel("TextBox1", "SomeInfo", False)

    Dim dElement As TextBoxOrLabel
    
    Dim element As Long
    For element = 0 To 1
        Set dElement = propValue(element)
        If dElement.IsLabel Then
            UserForm1.Controls(dElement.ControlName).Caption = dElement.Content
        Else
            UserForm1.Controls(dElement.ControlName).Value = dElement.Content
        End If
    Next
    
End Sub

Private Function CreateTextBoxOrLabel(ByVal pControlName As String, _
    pContent As String, pIsLabel As Boolean) As TextBoxOrLabel
    
    Set CreateTextBoxOrLabel = New TextBoxOrLabel
    
    With CreateTextBoxOrLabel
        .ControlName = pControlName
        .Content = pContent
        .IsLabel = pIsLabel
    End With

End Function

TextBoxOrLabel data object

Option Explicit

Public IsLabel As Boolean
Public Content As String
Public ControlName As String