User forms and keeping array variables

Lavina

Board Regular
Joined
Dec 18, 2018
Messages
75
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?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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:
Upvote 0
Don't use public variables if you can avoid it, pass variables to where they are needed
 
Upvote 0
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:
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,726
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top