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

gutzy

New Member
Joined
Sep 6, 2018
Messages
4
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
You need to clear the textboxes. Add this loop to the end of the cmdOK_Click.

Code:
Sub cmdOK_Click()
    Dim i as Long

'...
    Me.Hide

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

End Sub
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
The code I added loops through textboxes and sets their .Text to vbNullString. For ListBoxes you'd want to set the .ListIndex to -1.
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

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