Created a Pop-up Macro with "Add Part" Button, Pop-up Fields no blank for additional parts! Help

[gutzy]

I'm using this macro to populate my parts database thru a pop-up dialog box along with a "Add Part" button that initiates the Pop-up Dialog box using the module below.

It works great for the first part! But when I push the "Add Part" button for the next part, the fields are still populated with the 1st parts information. Is there a reset code I have to add to the macro to clear the fields of the Pop-up after hitting OK? Thx for the help!

Private Sub cmdOK_Click()
Sheets("SHEET METAL").Select
Range("A7").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(0, 0).Value = Me.TextBox1.Value
ActiveCell.Offset(0, 1).Value = Me.TextBox2.Value
ActiveCell.Offset(0, 2).Value = Me.TextBox3.Value
ActiveCell.Offset(0, 3).Value = Me.TextBox4.Value
ActiveCell.Offset(0, 7).Value = Me.TextBox5.Value
ActiveCell.Offset(0, 8).Value = Me.TextBox6.Value
ActiveCell.Offset(0, 9).Value = Me.TextBox7.Value
ActiveCell.Offset(0, 13).Value = Me.TextBox8.Value
ActiveCell.Offset(0, 25).Value = Me.TextBox9.Value


Me.Hide
End Sub

Module for "Add Part" button

Sub showForm1()
UserForm1.Show
End Sub

 

[mikerickson]

You need to clear the textboxes. Add this loop to the end of the cmdOK_Click.

Sub cmdOK_Click()
    Dim i as Long

'...
    Me.Hide

    For i = 1 to 9
        Me.Controls("TextBox" & i).Text = vbNullString
    Next i

End Sub

 

[gutzy]

Perfect!!!! Thanks so much!
Another question if you don't mind. I need to add the input for multiple ListBox's, ListBox1-ListBox5 that I have on my spreadsheet as well. Can you give me an idea how to add those to that macro? I've been hacking my way thru this for a few days. Up until then I didn't even know what VBA was..... Thx.

 

[mikerickson]

Your code doesn't mention any ListBoxes so I'm not sure what you want to do about them or where the data is coming from.
It would be best to start a new thread on this different topic.

BUT, since you are new to VBA, I would recommend that you focus on one ListBox and try to get it to do what you want. Look at the ObjectBrowser to find the methods and properties of a ListBox and try to find out what they do. Hitting the DEBUG button a couple of hundred times is a good way to learn VBA.

 

[gutzy]

Here is the "completed" Pop-up. I have the listbox's working, so to speak. When I goto add the next part, the Pop up is now blank thanks to your recommendation, except for the Listbox's, they remain highlighted on previous selection. Also, due to editing the Pop-up, the items are out of "Tabbing" order as the data is entered. Is there a way to reset the Tabbing order so the boxes are populated in sequence? Thx.
Private Sub cmdOK_Click()
Dim i As Long
Sheets("SHEET METAL").Select
Range("A7").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(0, 0).Value = Me.TextBox1.Value
ActiveCell.Offset(0, 1).Value = Me.TextBox2.Value
ActiveCell.Offset(0, 2).Value = Me.TextBox3.Value
ActiveCell.Offset(0, 3).Value = Me.TextBox4.Value
ActiveCell.Offset(0, 4).Value = Me.ListBox1.Value
ActiveCell.Offset(0, 5).Value = Me.TextBox5.Value
ActiveCell.Offset(0, 7).Value = Me.TextBox6.Value
ActiveCell.Offset(0, 8).Value = Me.TextBox7.Value
ActiveCell.Offset(0, 9).Value = Me.TextBox8.Value
ActiveCell.Offset(0, 17).Value = Me.TextBox9.Value
ActiveCell.Offset(0, 13).Value = Me.TextBox10.Value
ActiveCell.Offset(0, 21).Value = Me.ListBox4.Value
ActiveCell.Offset(0, 23).Value = Me.ListBox5.Value
ActiveCell.Offset(0, 25).Value = Me.TextBox11.Value


Me.Hide


For i = 1 To 9
Me.Controls("TextBox" & i).Text = vbNullString
Next i

End Sub

Module

Sub showForm1()
UserForm1.Show
End Sub

 

[mikerickson]

The code I added loops through textboxes and sets their .Text to vbNullString. For ListBoxes you'd want to set the .ListIndex to -1.

 

[gutzy]

Thx