Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    155
    Thanks
    70
    Thanked 0 Times in 0 Posts

    Clearing Form After Entering Data

    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

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

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


    zeddy

  3. #3
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    155
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Zeddy-

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

    I'll provide a file.
    Last edited by Excelnewbie; 2015-06-30 at 18:38.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

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

    zeddy
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Jun 2015
    Posts
    2
    Thanks
    0
    Thanked 2 Times in 1 Post
    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

  6. The Following 2 Users Say Thank You to sib183 For This Useful Post:

    Excelnewbie (2015-06-30),zeddy (2015-06-30)

  7. #6
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    155
    Thanks
    70
    Thanked 0 Times in 0 Posts
    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.
    Last edited by Excelnewbie; 2015-06-30 at 15:12.

  8. #7
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    155
    Thanks
    70
    Thanked 0 Times in 0 Posts
    sib183-

    Your help hit pay dirt. Thank you!

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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

  10. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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
    Last edited by zeddy; 2015-06-30 at 15:43.

  11. #10
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    155
    Thanks
    70
    Thanked 0 Times in 0 Posts
    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

  12. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    ..does this one work
    Attached Files Attached Files

  13. The Following User Says Thank You to zeddy For This Useful Post:

    Excelnewbie (2015-06-30)

  14. #12
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    155
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Zeddy,

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

    I agree.

  15. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    FYI....

    The statement:

    cbAccountType.Clear

    would also clear the combobox

    Maud

  16. #14
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    155
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Thank Maud-You are very kind.

  17. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    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 = ""
        End If
        If TypeOf ctl Is MSForms.ComboBox Then
            ctl.Text = ""
        End If
    Next
    End Sub
    Code:
    Private Sub UserForm_Initialize()
    With cbAccountType
    .AddItem ""
    .AddItem "Savings"
    .AddItem "IRA Savings"
    .AddItem "Roth Savings"
    .AddItem "PM"
    
    End With
    End Sub
    HTH,
    Maud

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •