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

Virginia632

New Member
Joined
Sep 12, 2023
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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.

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

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Upvote 0
I don't like global variables, because I lose track of what is updating them.
 
Upvote 0
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;

VBA Code:
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
 
Upvote 1
Solution
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.
 
Upvote 0

Forum statistics

Threads
1,223,404
Messages
6,171,922
Members
452,433
Latest member
Woodchuck76

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