User forms and keeping array variables

[Lavina]

Hello,

I am making a user form and I'd like it to contain: a drop down list, and 4 labels + 4 text boxes.

The idea is after a choice is made in drop down list, 4 label values are changed depending on array position, and 4 text boxes can update the values of the array.

So i fill up my array and then
Call DisplayFormEditLoc(allData())

Sub DisplayFormEditLoc(allData() As Variant)
    Dim myInitFrm As DisplayFormEditLocs
    Dim strCaption As String, i As Long, posSize As Long, posWeight As Long
    Set myInitFrm = New DisplayFormEditLocs
    posSize = 1
    posWeight = 1
    With myInitFrm
        .Caption = "Editing measurements"
        .CommandButtonConfirmation.Caption = "Edit"
        .CommandButtonCancellation.Caption = "Cancel"
        For i = 1 To UBound(allData(), 1)
        If allData(i, 2) <> Empty Then .ChoiceComboBox.AddItem allData(i, 2)
        Next i
        .ChoiceComboBox.ListIndex = 0
        .Show
    End With
End Sub

After filling up my combobox i display it. Then i was hoping to call a sub in my main module that has the array as a public:

Sub ChoiceComboBox_Change()
Dim indexer As Integer
indexer = ChoiceComboBox.ListIndex
Call ChoiceComboBoxChange(indexer)
End Sub

In ChoiceComboBoxChange sub id just update the values of all labels and since the change event triggers at start, it would also load the initial values.

But when i call ChoiceComboBoxChange it has none of the public variables.

I could of course do all the changes in the sub ChoiceComboBox_Change, but i can't figure out how to get the array values to it either.

How can this be done?

 

[Lavina]

Well apperently i redimed my array inside a sub. Now it's actaully global and is reachable by class:

Call ChoiceComboBoxChange(indexer)

Sub ChoiceComboBoxChange(indexer)
DisplayFormEditLocs.Llabel.Caption = "1"
DisplayFormEditLocs.DLabel.Caption = "2"
DisplayFormEditLocs.HLabel.Caption = "3"
DisplayFormEditLocs.WLabel.Caption = "4"
End Sub

These are just default values for checking if the function works, but it's assigning values to something else than the labels in my form.
In the DisplayFormEditLoc sub i assign it the value .DLabel.Caption = "D" and then once i reach the sub ChoiceComboBoxChange i can see that
DisplayFormEditLocs.DLabel has no value assigned to it

After moving my DisplayFormEditLoc and ChoiceComboBoxChange subs into 1 module and making DisplayFormEditLocs global i was able to make it work. But that is just bad, how could i have dealt in a better way?

 

[Kyle123]

It's very hard from your post to understand exactly what code you have where and it matters are we're looking at scope issues.

Please post all the code from the module that is initializing the form, the code form the form itself and any other associated code with the relevant module names

 

[Lavina]

Module 1:

Sub DisplayFormEditLoc(allData() As Variant, weightData() As Variant)
    Dim myInitFrm As DisplayFormEditLocs
    Dim strCaption As String, i As Long, posSize As Long, posWeight As Long
    Set myInitFrm = New DisplayFormEditLocs
    posSize = 1
    posWeight = 1
    With myInitFrm
        .Caption = "Editing measurements"
        .CommandButtonConfirmation.Caption = "Edit"
        .CommandButtonCancellation.Caption = "Cancel"
        For i = 1 To UBound(allData(), 1)
        If allData(i, 2) <> Empty Then .ChoiceComboBox.AddItem allData(i, 2)
        Next i
        .ChoiceComboBox.ListIndex = 0
        .Show
    End With
End Sub

Form:

Sub ChoiceComboBox_Change()
Dim indexer As Integerindexer = ChoiceComboBox.ListIndex
Call ChoiceComboBoxChange(indexer)
End Sub

Module 2:

Sub ChoiceComboBoxChange(indexer)
    Dim I As Long
    myInitFrm.Llabel.Caption = allData(indexer, 3)
    myInitFrm.DLabel.Caption = allData(indexer, 4)
    myInitFrm.HLabel.Caption = allData(indexer, 5)
    mainIndexer = indexer
    If allLocationData(indexer, 7) = "P" Then
        For I = 0 To UBound(weightData, 1)
            If weightLocationData(I, 4) = "P" Then
                myInitFrm.WLabel.Caption = weightData(I, 2)
                maxWeightLoc = weightData(I, 2)
            End If
        Next I
    End If
    If allLocationData(indexer, 7) = "S" Then
        For I = 0 To UBound(weightLocationData)
            If weightLocationData(I, 4) = "S" Then
                myInitFrm.WLabel.Caption = weightData(I, 2)
                maxWeightLoc = weightData(I, 2)
            End If
        Next I
    End If
End Sub

I want my array allData() received in Sub DisplayFormEditLoc to get thrown over to the sub ChoiceComboBox_Change(), but since it’s initialized as an event I cant throw parameters at it, or am I wrong?

 

[Kyle123]

Why wouldn't the code in Module 2 be in the userform?

 

[Lavina]

I moved it out because i was not able to to pick up allData() once i reach the event:ChoiceComboBox_Change()

I'm not sure how public variables work but i dim them before all the subs:

Public allLocationData() As Variant
Public weightLocationData() As Variant

But whenever i move to a another module all the values are not visible, why?

 

[Kyle123]

It's because you aren't passing allData to the userform - you just need to pass it as a property (probably along with WeightData) - something like:

Form:

Private p_allData() As Variant
Private p_weightData() As Variant

Public Property Let AllData(data() As Variant)
    p_allData = data
End Property

Public Property Let WeightData(data() As Variant)
    p_weightData = data
End Property

Sub ChoiceComboBox_Change()
    ChoiceComboBoxChange Me.ChoiceComboBox.ListIndex
End Sub

Sub ChoiceComboBoxChange(indexer)
    
    Dim i As Long
    Me.Llabel.Caption = p_allData(indexer, 3)
    Me.DLabel.Caption = p_allData(indexer, 4)
    Me.HLabel.Caption = p_allData(indexer, 5)
    
    mainIndexer = indexer
    
    If allLocationData(indexer, 7) = "P" Then 'Where does this variable come from?
        For i = 0 To UBound(p_weightData, 1)
            If p_weightData(i, 4) = "P" Then
                Me.WLabel.Caption = p_weightData(i, 2)
                maxWeightLoc = p_weightData(i, 2)
            End If
        Next i
    End If
    If allLocationData(indexer, 7) = "S" Then  'Where does this variable come from?
        For i = 0 To UBound(weightLocationData) 'Where does this variable come from?
            If weightLocationData(i, 4) = "S" Then
                Me.WLabel.Caption = p_weightData(i, 2)
                maxWeightLoc = p_weightData(i, 2)
            End If
        Next i
    End If
End Sub

Module1:

Sub DisplayFormEditLoc(allData() As Variant, weightData() As Variant)
    Dim myInitFrm As DisplayFormEditLocs
    Dim strCaption As String, i As Long, posSize As Long, posWeight As Long
    Set myInitFrm = New DisplayFormEditLocs
    posSize = 1
    posWeight = 1
    With myInitFrm
        .Caption = "Editing measurements"
        .CommandButtonConfirmation.Caption = "Edit"
        .CommandButtonCancellation.Caption = "Cancel"
        .AllData = allData
        .WeightData = weightData
        For i = 1 To UBound(allData(), 1)
        If allData(i, 2) <> Empty Then .ChoiceComboBox.AddItem allData(i, 2)
        Next i
        .ChoiceComboBox.ListIndex = 0
        .Show
    End With
End Sub

 

[Kyle123]

Don't use public variables if you can avoid it, pass variables to where they are needed

 

[Lavina]

Thank you for your input, but the let statement leaves my data empty in any way i try to write it.
Since this is a setter, do i need an initial getter to pick the data up?


I tried to avoid using globals as much as possible, but i've seen people state that they use a single module to track their globals.

That does not work for me. Do they need to be included somewhere or?

Option Explicit
Public functionChoice As String
Public confirmation As Boolean
Public updatedValuesArray() As Variant

If i were to make a module that contained only that, none of these variables are seen in my initiation sub, am i doing something wrong?

 

[Kyle123]

You're going about it the wrong way, don't use the global/public variables at all.

Let's fix the issue, not look for a workaround and take this back to basics. You pass variables to userforms (or objects) using properties, so at its most simple, something like this:
Userform1

Private p_allData() As Variant

Public Property Let AllData(data As Variant)
    p_allData = data
End Property

Private Sub UserForm_Activate()
    MsgBox Join(p_allData, ", ")
End Sub

So when initializing the form, we can then pass a variable to the AllData Property:

Sub test()

    Dim uf As UserForm1
    Set uf = New UserForm1
    
    uf.AllData = Array(1, 2, 3)
    
    uf.Show


End Sub

Does that code work for you?