Clearing Form After Entering Data

Topic

New Reply

Hi Experts-

I continue to learn VBA. I am stuck on a problem I haven’t found a solution for.

I am using a form to enter data. I need to have the form cleared after clicking OK.

The code below in red is where I am having problems. All the data in textboxes clears fine.

But the data in the combo box isn’t clearing.

Any help you can provide is appreciated.

Private Sub btnOK_Click()

Dim ws As Worksheet
Set ws = Worksheets(“Assets”)

‘If there is nothing in column A then this code will over write anything else that is on the same row.
Dim newRow As Long
newRow = Application.WorksheetFunction.CountA(ws.Range(“A:A”)) + 1

ws.Cells(newRow, 1).Value = Me.txtName.Value
ws.Cells(newRow, 2).Value = Me.txtDateOpened.Value
ws.Cells(newRow, 3).Value = Me.cbAccountType.Value
ws.Cells(newRow, 4).Value = Me.txtAccountNumber.Value
ws.Cells(newRow, 5).Value = Me.txtAmount.Value
ws.Cells(newRow, 6).Value = Me.txtInterestRate.Value
ws.Cells(newRow, 7).Value = Me.txtWeissRating.Value
ws.Cells(newRow, 8).Value = Me.txtDateClosed.Value
ws.Cells(newRow, 9).Value = Me.txtNotes.Value

Dim ctl
For Each ctl In Me.Controls
If TypeOf ctl Is MSForms.TextBox Then
ctl.Text = “”
If TypeOf ctl Is MSForms.ComboBox Then
ctl.Text = “”


End If
End If
Next
End Sub

Private Sub UserForm_Initialize()
With cbAccountType
.AddItem “Savings”
.AddItem “IRA Savings”
.AddItem “Roth Savings”
.AddItem “PM”

End With
End Sub

Reply | Quote

Replies

Hi

..to clear comboboxes use:
If TypeOf ctl Is MSForms.ComboBox Then
ctl.Clear

zeddy

Reply | Quote

Zeddy-

For some reason, the code you suggested doesn’t clear the combo box.

I’ll provide a file.

Reply | Quote

Hi

..try this.
It was easier to freeze the display, unload the Form, then re-load it.

zeddy

Reply | Quote

Hi Newbie,

Another newbie here, but it looks like your If statement testing for the combobox type control is nested inside the If statement testing for the textbox control. You need to End If the first before starting the second test.

Hope this helps,

Jonathan

Reply | Quote

sib183-

Your help hit pay dirt. Thank you!

Reply | Quote

Hi Jonathan

..credit to you for spotting that!
Welcome to the Lounge!

..you deserves your first Thanks!
..that makes you 100% thanks in ALL your posts!

zeddy

Reply | Quote

Zeddy-

That is simple enough. Thank you.

One concern, I entered several rows of data using the form. Nothing showed up in the sheet when I clicked OK. I continued to enter data. When I clicked cancel the data showed up on the sheet. For some reason, the data starts on row 5. This was happening prior to your work. Then I got an “Out of Memory” error.

Reply | Quote

Hi Excelnewbie

..count how many entries you have in column [A]
..I make it four, so the next entry will be on row 5 as per your code line
newRow = Application.WorksheetFunction.CountA(ws.Range(“A:A “)) + 1

..so, move your stuff out of column A!

zeddy

Reply | Quote

Zeddy,

If you have time, I would like to add the code you provided as an alternative. Have you got it working now?

Application.ScreenUpdating = False
Unload Me
UserForm1.Show
End Sub

Reply | Quote

Hi

..does this one work

Reply | Quote

Zeddy,

Yes, it works. You said one time that, “simple is elegant”.

I agree.

Reply | Quote

FYI….

The statement:

cbAccountType.Clear

would also clear the combobox

Maud

Reply | Quote

Thank Maud-You are very kind.

Reply | Quote

Thanks Excel. You were so close in your original code. Aside the aforementioned nested end if error, your original code could have been modified to work with adding just one line of code. Note changes in Blue:

Code:

Private Sub btnOK_Click()
Dim ws As Worksheet
Set ws = Worksheets(“Assets”)
‘If there is nothing in column A then this code will over write anything else that is on the same row.
Dim newRow As Long
newRow = Application.WorksheetFunction.CountA(ws.Range(“A:A “)) + 1
ws.Cells(newRow, 1).Value = Me.txtName.Value
ws.Cells(newRow, 2).Value = Me.txtDateOpened.Value
ws.Cells(newRow, 3).Value = Me.cbAccountType.Value
ws.Cells(newRow, 4).Value = Me.txtAccountNumber.Value
ws.Cells(newRow, 5).Value = Me.txtAmount.Value
ws.Cells(newRow, 6).Value = Me.txtInterestRate.Value
ws.Cells(newRow, 7).Value = Me.txtWeissRating.Value
ws.Cells(newRow, 8).Value = Me.txtDateClosed.Value
ws.Cells(newRow, 9).Value = Me.txtNotes.Value
Dim ctl
For Each ctl In Me.Controls
    If TypeOf ctl Is MSForms.TextBox Then
        ctl.Text = “”
    [COLOR=”#0000FF”]End If[/COLOR]
    If TypeOf ctl Is MSForms.ComboBox Then
        ctl.Text = “”
    [COLOR=”#0000FF”]End If[/COLOR]
Next
End Sub

Code:

Private Sub UserForm_Initialize()
With cbAccountType
[COLOR=”#0000FF”].AddItem “”[/COLOR]
.AddItem “Savings”
.AddItem “IRA Savings”
.AddItem “Roth Savings”
.AddItem “PM”

End With
End Sub

HTH,
Maud

Reply | Quote

Many thanks! I’m learning, thanks to you and the other pro’s here.

Reply | Quote