Can't delete names that are all numbers in userform listbox.

[Inti]

Hello all! In this macro I can't delete names that are all numbers via the userform. I can delete names that are mixes of letters and numbers just not all numbers (e.g 123123123). Any help on fixing this would be greatly appreciated! I am attaching the file for convenience.

The error in debug pinpoints the line that i am underlining in the following code.

Please Login or Register  to view this content.

[Tinbendr]

When you enter numbers in Excel, Excel assumes that those are, in fact, numbers. When you load a combobox with numbers, the combobox doesn't care what it is and inserts it as a string. When you assign the combobox to a variable, then use that in the match function, it is passed a string and thus can't find the number in the column.

Use

Please Login or Register  to view this content.

Or, you can enter the numbers with a tick mark in front to tell Excel that this is text.

There are a few more ways to handle this if you can't get it working.

[Inti]

Tinbendr, I added the line but now whenever I try to delete something I get an error on and it wont delete from the listbox.

Please Login or Register  to view this content.

[Tinbendr]

Did you add the Val?

Works on my copy.

[Inti]

Yes, I deleted mine and added yours. I actually tried a non numerical name first, since it didn't work I didn't try to do a all numerical name.

[Norie]

Declare k as Variant.

By the way, why are you looping if the listbox isn't multiselect?

For a single select listbox all you need to delete the selected item is the ListIndex.

[Inti]

Either I found an example that handled it that way or someone helped me with that part and did it that way. I am very new to the language so I am grasping these concepts as I go.

I added the line

Please Login or Register  to view this content.

I have the same error but the error moved to this line

Please Login or Register  to view this content.

[Norie]

Did you also use Tinbendr suggestion of Val?

If you still can't get it to work with that here's a version using ListIndex.

Please Login or Register  to view this content.

[Tinbendr]

Norie's right, you don't have to use a loop when Multi-Select is off.

Here's a version with the iteration. Just in case you ever change you mind and turn multiselect on.

Please Login or Register  to view this content.

[Inti]

GREAT! That did it :D Thank you Norie. Nice improvement!

[Inti]

I can't add to your reputation because I think you where the last person that I added to!

[Inti]

Thank you both, appreciate it! Btw, how do I select more than one choice in the listbox. I am trying but no go.

Edit

I went to properties and looked at the multiselect option. There are 3 options, 0, 1 and 2 what is difference between 1 and 2?

Edit 2

I changed multiselect to 1 and then 2 and in both instances whenever I try to delete two it only deletes the option which is lower down the list.

[Norie]

Inti

Since you repopulate the listbox after you've deleted you could miss out bit that deletes from the listbox.

[Inti]

Yes it's being repopulated to make sure that the items in the listbox are the latest ones found in the site list spreadsheet. Why is it only deleted the last item in the selection?

[Norie]

Which code are you using?

Have you tried getting rid of this?

Please Login or Register  to view this content.

[Inti]

I used yours initially but once Tinbendr suggested his script I added it. It works fine no errors but behaves as described this is the code for the delete tab

Please Login or Register  to view this content.

[Norie]

Have you tried removing this?

Please Login or Register  to view this content.

[Inti]

Do you mean a specific line or all of the instances where that line appears?

[Norie]

All the instances in that sub.

Like I said you don't need to delete the items from the listbox as you end up repopulating it from the worksheet anyway.

[Inti]

Your code works great to delete 1 selection. I am trying the multi-selection code that Tin provided and on that one I deleted the instances of that line but it is still not deleting the rows or the items on the listbox.

Please Login or Register  to view this content.