UserForm_Initialize not being recognized in Commands

Denny57

Board Regular
Joined
Nov 23, 2015
Messages
235
Office Version
  1. 365
Platform
  1. Windows
I have been using Call UserForm_Initialize for as long as I have been using VBA, both when initializing forms and to reset a User Form once details have been added to the Worksheet.

Recently, this function has not been working in a number of new files and forms and I am at a loss as to why this might be. I have several options to reset the form, however, I often use UserForm_Initialize when I apply a lot of initial data to a worksheet where the information in one or more controls is always the same (e.g. TextBox1.Value - "Monday")

Here is the entire code for a recent User Form with a "Call UserForm_Initialize" included to reset the User Form after using the Add Transaction Command Button.

VBA Code:
Private Sub UserForm_Initialize()

    Worksheets("Spending Account").Activate
    DTPicker1.SetFocus
    
End Sub

Private Sub cmdAddRecord_Click()
'Used to add new transation records to the database

    Dim r As Long, sCredit As String, sDebit As String
    
    sDebit = Me.txtTransactionAmountDebit
    sCredit = Me.txtTransactionAmountCredit
    
    With Sheets("Spending Account")
        r = 1 + .Cells(.Rows.Count, "A").End(xlUp).Row
        .Cells(r, "A").Value = DTPicker1
        .Cells(r, "B").Value = cboVendorDetails
        .Cells(r, "C").Value = cboTransactionType
        .Cells(r, "F").Value = cboTransactionStatus
        
        ' credit or debit
        If Len(sDebit) > 0 Then
            If Len(sCredit) > 0 Then
                MsgBox "Warning - Both Credit and Debit", vbExclamation
            Else
                .Cells(r, "D").Value = CDbl(sDebit)
            End If
        ElseIf Len(sCredit) > 0 Then
           .Cells(r, "E").Value = CDbl(sCredit)
        End If
          
        If r > 21 Then
            Application.Goto Reference:=.Cells(r - 20, "A"), Scroll:=True
        End If
    End With

    'Unload Me
    'frmRegularTransactions.Show
    
    Call UserForm_Initialize
    
End Sub
Private Sub txtTransactionAmountDebit_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    txtTransactionAmountDebit.Value = Format(txtTransactionAmountDebit.Value, "Currency")
End Sub

Private Sub txtTransactionAmountCredit_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txtTransactionAmountCredit.Value = Format(txtTransactionAmountCredit.Value, "Currency")
End Sub

Private Sub cmdClearForm_Click()
 
    Dim ctl
        For Each ctl In Me.Controls
            If TypeOf ctl Is MSForms.TextBox Then
                ctl.Text = ""
            End If
            If TypeOf ctl Is MSForms.ComboBox Then
                ctl.Text = ""
            End If
    Next ctl
End Sub

Private Sub cmdCloseForm_Click()

Unload Me

End Sub

After the details are uploaded the form does not reset using Call UserForm_Initialize in the Add command,

I would like to understand why if I use the

Unload Me
frmRegularTransaction.Show

or the code in the cmdClearForm sub that both these reset the User Form but Call UserForm_Initialize does not.

Thanks in advance
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Forum statistics

Threads
1,223,883
Messages
6,175,168
Members
452,615
Latest member
bogeys2birdies

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