error on RemoveItem from Listbox

[Loski]

I am getting an error on RemoveItem. How to solve?

Also despite having 2 columns, there is only one showing up. How to display the item # in column 1, keeping the item in Column 2?


Private Sub ListBox1_Initialize()
Dim Mylist(81, 2) As String ' List of Merged Cells after Set Up (manual or automatic) is over
Dim i As Long

Mylist(0, 0) = "Line"
Mylist(0, 1) = "Column BA"

For i = 1 To 81
Mylist(i, 0) = "#" & FormatNumber(i, 0) ' number of insertion of Merged Cells
Mylist(i, 1) = "BA" & FormatNumber(i, 0) ' Where the Merged Cells addresses are located
Next i

ListBox1.ColumnCount = 2
ListBox1.List = Mylist
End Sub

'+++++++++++++++++++++++++++++++++++++++++++++++ When User clicks on one item in the list, then the value listed will do µnmerge in the Main Grid +++++++++++++++++++++++++++++++++++
Private Sub ListBox1_Click()

Range("AI49").Value = UserForm4.ListBox1.Value

Analyze_Which_to_UnMerge ' unMerge the selected item stored in AI49, it will also reduce A45 by one
Range("A45") = Range("A45") + 1

UserForm4.ListBox1.RemoveItem Range("A45").Value

Range("A45") = Range("A45") - 1
End Sub

[Tinbendr]

I've had to change my answer. I can't determine what's happening.

If your value in A45 is higher than your listcount, you'll get an error.

[jolivanes]

@Loski.
Please read the forum rules. In your case here, the rule about code tags.

[mikerickson]

ListBoxes don't have an Initialize event.

[Loski]

no I checked, A45 is the number of items stored in the Listbox. Its value is always correct. I checked. I am trying to remove the last one only, the right item gets always UnMerged OK but does not get removed from the list

[Loski]

ah ok will change that. thanks

[Loski]

oops!
will read
sorry

[Tinbendr]

ListBoxes don't have an Initialize event.

Good catch. I read right past this.

I've never seen someone track a listbox that way.

If you're only removing the last item, then

Please Login or Register  to view this content.

Or the one that's selected.

Please Login or Register  to view this content.

[mikerickson]

The next effect of this is to decrease the number of items in the box, and leave A45 unchanged.
FirstLine incriments A45. SecondLine decrements A45. Net effect = 0

Please Login or Register  to view this content.

Also you should make sure that A45 is between 0 and ListBox1.ListCount - 1

[Loski]

? I dont get it.
the reason I have to increase A45 by one before doing the RemoveItem is because A45 got decreased by one in Analyze_Which_to_UnMerge as spoecified in the text.
otherwise i would remove the wrong item
And after that RemoveItem I do have to decrease A45 by one. A45 is the qty of items in the list used in many other subs in my program.
So that code is perfectly correct.
and A45 is always between 0 and ListCount - 1 , thanks for your concern

despite all the suggestions, I still get an error runtime !!! strange

[Tinbendr]

How are you filling Listbox? If it's via Rowsource, it will error if you try to remove an item.

[Loski]

UserForm4.ListBox1.AddItem "A1"
I am developing a Sudoku solving program. it has 729 cells (9x9x9). each group of 9 cells have a name A1 to I9 (81 in total).
all that works fine for months. I give the user the capability of unmerge a group. Among those capabilities i wanted to add a 3rd way, unmerging from the list box. it does unmerge right but the list box keeps the item in.
so i tried to use RemoveItem, 1st time I try that in my life but it does do what I wish and get an error.

today when I changed the RemoveItem as follows I dont get an error anymore like I did using some suggested solutions like .ListIndex or ListCount - 1
UserForm4.ListBox1.RemoveItem UserForm4.ListBox1.MousePointer
but the whole Listbox gets empty not only the one pointed. No error but still Strange.

RowSource? not sure

[Tinbendr]

RowSource? not sure

Oh, you'd know it if you were using it. It's a property of the listbox and you can either manually enter a range, or assign one via code.

E.g.

Please Login or Register  to view this content.

Hmm. I'm out of ideas. You'll just have to put in a break, debug.print listbox.listindex to the immediate window and watch what happens.

[Loski]

by the way the Listbox1_Initialize never run. It never got running. I removed that code entirely w/o any negative effect.

may be one way to solve instead of using the contents, would be using the item # that I dont presently have in the Listbox. it only has A1 to I9 listed minus those locations merged while charging the sudoku (about 18 to 35).
how may i had the item # in the listbox? it would become an array, right?

[Loski]

may I add (not had). I broke my arm last week so I type with only one finger

[Tinbendr]

That's what Mike said in #4; there IS NO initialize event for the control Listbox. That's a event for the userform itself. (You can always double click a control and the default code will automatically be entered. Then at the top right dropdown of the window, there is a list of events applicable to that control. Unless you write your own class module to add it.)

When is the decrement suppose to occur? When you click on the listbox? Or do you select an item in the list? Or when you double click an item?

[Loski]

hello,
I did find an alternative last week. I added the storing of the items into a range. Then when an item is clicked into the listbox, that item gets removed in tha range also, then I clear the listbox.
Finally I copy the contents of the range and do additem in the listbox to regenerate it.
It works like a charm.
no need to try using RemoveItem anymore, my alternative solution does it.

thanks for your tries,
cordially
daniel