ListBox.Clear Function

rameezl17

Board Regular
Joined
Mar 6, 2018
Messages
105
Hi Everyone,

I have UserForm1 Which Has 3 TextBoxes & 2 ListBoxes. (TextBox1, 2, 3) & (ListBox1,2)

I also Have An "Exit" Button (CommandButton3), a "Clear All" Button (CommandButton2), and "Submit" Button (CommandButton1)

So The User Populates The TextBoxes And Then They Select a Drop Down From The ListBoxes. When They Click Submit, The Data That Was Typed & Selected Populate Into "Sheet 1".

Now My Problem Is When A User Clicks The Clear All Button It Clears The UserForm Like It Should, But When They Click "Clear All & Then Click "Submit", Everything Is Cleared In The "Sheet 1" Like It Should...EXCEPT For the ListBox Selections. Does Anybody Know Why?


My Code For The Clear All Button Is:

Private Sub Command Button2_Click()
Unload Me
UserForm1.Show
End Sub

Thank You Very Much For Your Help!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Hi there. I don't think we have enough information to help, but on the userform, yo your clear the listboxes? The code you provided isn't showing us how you are clearing sheet1. Thanks
 
Upvote 0
Hi Roderick,

So when the user clicks "Clear All" the textboxes are cleared and listboxes selection is cleared on the UserForm. When they click submit right after clicking "Clear All', "Sheet 1" should be blank.

The problem is that if someone submits data one time, and then realizes they made a mistake and goes back to the userform to "Clear All" and "Submit", technically everything from "Sheet 1" should be empty...however the listbox selections from the first time they clicked "Submit", is still appearing in "Sheet 1". Even though they clicked "Clear All" which cleared out the userform and clicked "Submit" which should make "Sheet 1" blank.

I hope this makes sense please let me know if it does not.
 
Last edited:
Upvote 0
Thanks but we still need to understand how you clearing sheet1. Why not just find the last populated row and clear everything there? Then it doesn't matter if the userform still has data in the textboxes or listboxes.

Something like
Code:
lastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
sheet1.rows(lastrow)=""

Sweet and simple
 
Upvote 0
Sheet 1 Is Clearing Because When The User Clicks "Clear All" An Then "Submit" The User Is Submitting An Empty UserForm. Thank You For That Formula But I Can't use Last Row Because I have More Than 1 Row. I only Mentioned 1 Row For This Example To Make It Easier.
 
Upvote 0

Forum statistics

Threads
1,224,271
Messages
6,177,617
Members
452,786
Latest member
k3calloway

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