Passing a Static-type variable to the Initialize() procedure from another userform procedure

[Virginia632]

I was getting an "Out of Memory" error after having my userform open too long so I unload the form every time the user clicks btnEnterResetNext. Now my problem is that I have to use a global variable to reopen the form in the right place. Is there a way of passing a variable that maintains its value when passed to the Initialize procedure? I tried using the .tag property but it keeps resetting to "" when Initialize is called.

The form populates a combobox with the start dates of all the pay periods
When the user clicks btnEnterResetNext the form enters the data onto the worksheet, resets the userform, and then should unload the form and reopen it and set the ListIndex to the one after the one previously selected. Right now I increment PayPeriodIndex (a global variable) which works but I want to get away from global variables.

Private Sub btnEnterResetNext_Click()
    
   'ensure a pay period has been selected first
    If cmbDayInPay.ListCount Then
        'enter the data
        EnterData
        
        'Reset the form
        ResetFormValues
           
        'If the last day isn't selected, select the next day
        If (cmbDayInPay.ListIndex) < (cmbDayInPay.ListCount - 1) Then
            cmbDayInPay.ListIndex = cmbDayInPay.ListIndex + 1
        
        'Else tell the user they've finished with that pay period, and moving on to the next one
        ElseIf cmbPayPeriod.ListIndex < cmbPayPeriod.ListCount - 1 Then
            MsgBox "You have finished with this paystub.  Selecting the next one."
            PayPeriodIndex = cmbPayPeriod.ListIndex + 1
            Unload Me
            frmPayStubEntry.Show
            
        'If the last Pay Period is selected send a message to the user
        Else
            MsgBox "That was the last paystub for the year"
            Unload Me
        End If
    Else
        MsgBox "Select a Pay Period first."
    End If
End Sub

Private Sub UserForm_Initialize()
    Dim strDate As String
  
    'Populate the Pay Periods with dates
    Dim i As Integer
    For i = 1 To Sheets.Count - 2
        'get the string to add to the combo box
        strDate = Format(Sheets(i).Range("Pay_Period").Value, "dd-mmm-yyyy")
        'add it to the combo box
        cmbPayPeriod.AddItem strDate
    Next i
    
    cmbPayPeriod.ListIndex = PayPeriodIndex
    
End Sub

 

[Virginia632]

I'm using Excel 2019 on Windows

 

[Jaafar Tribak]

Right now I increment PayPeriodIndex (a global variable) which works but I want to get away from global variables.

Any particular reason why you don't want to use a global variable ?

BTW, you could store the value in many other places such as in a worksheet cell, in an excel name, in the registry, in a window property etc

 

[Virginia632]

I don't like global variables, because I lose track of what is updating them.

 

[Jaafar Tribak]

Like I said, there are many places where you can store the value of PayPeriodIndex

If you want to keep things tidy and inside the userform module, you could define PayPeriodIndex as Private Property as follows;

Option Explicit

Private Property Get PayPeriodIndex() As String
    With CommandBars.FindControl
        PayPeriodIndex = IIf(Len(.Tag) = 0&, -1&, .Tag)
    End With
End Property

Private Property Let PayPeriodIndex(ByVal vNewValue As String)
    CommandBars.FindControl.Tag = vNewValue
End Property

Private Sub btnEnterResetNext_Click()
   
   'ensure a pay period has been selected first
    If cmbDayInPay.ListCount Then
        'enter the data
        EnterData
       
        'Reset the form
        ResetFormValues
         
        'If the last day isn't selected, select the next day
        If (cmbDayInPay.ListIndex) < (cmbDayInPay.ListCount - 1) Then
            cmbDayInPay.ListIndex = cmbDayInPay.ListIndex + 1
       
        'Else tell the user they've finished with that pay period, and moving on to the next one
        ElseIf cmbPayPeriod.ListIndex < cmbPayPeriod.ListCount - 1 Then
            MsgBox "You have finished with this paystub.  Selecting the next one."
            PayPeriodIndex = cmbPayPeriod.ListIndex + 1
            Unload Me
            frmPayStubEntry.Show
           
        'If the last Pay Period is selected send a message to the user
        Else
            MsgBox "That was the last paystub for the year"
            Unload Me
        End If
    Else
        MsgBox "Select a Pay Period first."
    End If
End Sub

Private Sub UserForm_Initialize()
    Dim strDate As String
 
    'Populate the Pay Periods with dates
    Dim i As Integer
    For i = 1 To Sheets.Count - 2
        'get the string to add to the combo box
        strDate = Format(Sheets(i).Range("Pay_Period").Value, "dd-mmm-yyyy")
        'add it to the combo box
        cmbPayPeriod.AddItem strDate
    Next i
   
    cmbPayPeriod.ListIndex = PayPeriodIndex
   
End Sub

 

[Virginia632]

I'll give that a try tonight. Thanks.

 

[Virginia632]

That worked perfectly. Thank you so much. I don't really understand the code though. I'm pretty new at this and completely self-taught.