ListBox.Clear Function

[rameezl17]

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!

 

[Roderick_E]

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

 

[rameezl17]

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.

 

[Roderick_E]

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

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

Sweet and simple

 

[rameezl17]

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.