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.
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
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