User forms and keeping array variables

Lavina

Board Regular
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())
Code:
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:

Code:
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

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

Call ChoiceComboBoxChange(indexer)

Code:
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?
 
Last edited:

Kyle123

Well-known Member
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

Board Regular
Module 1:
Code:
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:
Code:
Sub ChoiceComboBox_Change()
Dim indexer As Integerindexer = ChoiceComboBox.ListIndex
Call ChoiceComboBoxChange(indexer)
End Sub
Module 2:
Code:
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?
 
Last edited:

Kyle123

Well-known Member
Why wouldn't the code in Module 2 be in the userform?
 

Lavina

Board Regular
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?
 
Last edited:

Kyle123

Well-known Member
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:
Code:
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:
Code:
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
 
Last edited:

Lavina

Board Regular
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?

Code:
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?
 
Last edited:

Kyle123

Well-known Member
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
Code:
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:
Code:
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?
 
Last edited:

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top