Reference an array declared in a different userform

Question

I'm not an experienced VBA programmer but I've been trying to create an Excel Spreadsheet that is able to manage a basketball team.

In it I've got a primary userform where I have declared an array, 'selectedPlayers'. This primary userform has a for loop that starts up the secondary userform 'i' times.

I have not been able to access the primary userform's 'i' and 'selectedPlayers' from the secondary one. I've been able to find a workaround the 'i' by creating a non-visible textbox in the first userform, that I'm able to reference from the second one.

I've tried declaring both of them as public, but yet I'm not able to call upon it from the second userform.

part of the code for the first userform:

i = 0
Do While Not i = Int(txtNumberPlayers)
    frmGameDataSecondary.Show
    i = i + 1
Loop

second userform:

Private Sub cmdDone_Click()
    frmGameData.selectedPlayers(frmGameData.i) = lbxPlayer.Value
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    With Me.lbxPlayer

    For Each LR In LO.ListRows
        exitSequence = False

        For k = 1 To Int(frmGameData.txtNumberPlayers)
            If frmGameData.selectedPlayers(k) = blablabla.Value Then
                exitSequence = True
            End If
        Next k

        If !exitSequence Then
            .AddItem blablabla.Value
        End If

    Next LR

    End With
End Sub

Community · Accepted Answer · 2017-05-23 12:31:32Z

1

The main problem is that array contents are cleared after the sub is finished.

I was also messing around with this idea and there is a really good thread I started with tons of great information from various awesome people

Calling an Array Upon User Form Terminate/Close VBA

edited May 23, 2017 at 12:31

CommunityBot

11 silver badge

answered Mar 30, 2016 at 12:22

Doug Coats

7,2279 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Comintern · Accepted Answer · 2016-03-30 12:45:47Z

Forms in VBA are Objects, and can be treated like any other Class module. This means that you can add properties to them. If you need to pass information back from a form, all you need to do is grab a reference to it, then Hide it instead of Unload it. Treat it like a dialog and let the calling code handle it's create and destruction (I'm assuming from your code that it is modal).

Something like this:

In the first UserForm:

For i = 0 To 1
    Dim second As frmGameDataSecondary
    Set second = New frmGameDataSecondary
    second.Show
    'Execution suspends until the second form is dismissed.
    selectedPlayers(i) = second.Player
    Unload second
Next i

In the second UserForm:

Private mPlayer As String

'This is where your returned information goes.
Public Property Get Player() As String
    Player = mPlayer
End Property

Private Sub cmdDone_Click()
    mPlayer = lbxPlayer.Value
    'Control passes back to the caller, but the object still exists.
    Me.Hide
End Sub

gembird · Accepted Answer · 2016-03-30 20:50:56Z

You can declare properties inside of parent form which will manipulate the array from outside. The child form needs to have a reference to parent so it can call this properties. HTH

Parent form

Option Explicit

' I have not been able to access the primary userform's
' 'i' and 'selectedPlayers' from the secondary one
Private selectedPlayers As Variant

Public Function GetMyArrayValue(index) As Variant
    GetMyArrayValue = selectedPlayers(index)
End Function

Public Sub SetMyArrayValue(index, newValue)
    selectedPlayers(index) = newValue
End Sub

Private Sub UserForm_Click()
    Dim i

    i = 0
    Do While Not i = Int(txtNumberPlayers)
        With New secondaryUserForm
            Set .ParentForm = Me
            .SetIndex = i
            .Show
        End With
        i = i + 1
    Loop
End Sub

Private Sub UserForm_Initialize()
    selectedPlayers = Array("A", "B", "C")
End Sub

Child form

Option Explicit

Private m_parent As primaryUserForm
Private m_index As Integer

Public Property Let SetIndex(ByVal vNewValue As Integer)
    m_index = vNewValue
End Property

Public Property Set ParentForm(ByVal vNewValue As UserForm)
    Set m_parent = vNewValue
End Property

Private Sub cmdDone_Click()
    ' frmGameData.selectedPlayers(frmGameData.i) = lbxPlayer.Value
    m_parent.SetMyArrayValue m_index, "lbxPlayer.Value"
    Unload Me
End Sub

Collectives™ on Stack Overflow

Reference an array declared in a different userform

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related