Listbox click error

[MicroMouse]

Hi All,
I am sure this is fundamental but I just don't understand why. I have a userform with a combobox and a listbox. I can add items to the listbox no problem but I wanted to be able to delete from the listbox. If I assign the following macro to a button then it works fine. However, when I assign it to the listbox_click event I get runtime error 1004.

Private Sub CommandButton1_Click()
Dim Item As String
Dim myRange As Range
Sheet1.Activate

Item = UserForm1.ListBox1.Value

Set myRange = Range("A1", Range("A65535").End(xlUp))

myRange.Find(Item, LookIn:=xlValues, Lookat:=xlWhole).Select
Selection.Delete Shift:=xlUp

UserForm1.ListBox1.RowSource = Range("A1", Range("A65535").End(xlUp)).Address


End Sub

Any thoughts?

 

[DanteAmor]

If you loaded the listbox with Rowsource and you delete a data from the listbox (Selection.Delete Shift: = xlUp), the ListBox1_Click event is activated again. Also when you reload the listbox1 with UserForm1.ListBox1.RowSource = Range ("A1", Range ("A65535"). End (xlUp)).Address, the ListBox1_Click event is activated again, and then the error occurs.
To avoid the error, you can declare a global variable (at the beginning of the whole code), activate the variable when the click event is running, if the variable is active then you must exit the event.
Try the following:

Dim deleting As Boolean [COLOR=#0000ff]'at the beginning of the whole code[/COLOR]
'
Private Sub ListBox1_Click()
    Dim Item As String
    Dim myRange As Range
    If deleting = True Then Exit Sub
    '
    deleting = True     '[COLOR=#0000ff]activate the variable[/COLOR]
    Sheet1.Activate
    
    Item = UserForm1.ListBox1.Value
    
    Set myRange = Range("A1", Range("A65535").End(xlUp))
    myRange.Find(Item, LookIn:=xlValues, Lookat:=xlWhole).Select
    Selection.Delete Shift:=xlUp
    UserForm1.ListBox1.RowSource = Range("A1", Range("A65535").End(xlUp)).Address
    deleting = False    '[COLOR=#0000ff]Deactivate the variable[/COLOR]
End Sub

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...19-listbox-click-event-macro-not-working.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[MicroMouse]

If you loaded the listbox with Rowsource and you delete a data from the listbox (Selection.Delete Shift: = xlUp), the ListBox1_Click event is activated again. Also when you reload the listbox1 with UserForm1.ListBox1.RowSource = Range ("A1", Range ("A65535"). End (xlUp)).Address, the ListBox1_Click event is activated again, and then the error occurs.
To avoid the error, you can declare a global variable (at the beginning of the whole code), activate the variable when the click event is running, if the variable is active then you must exit the event.
Try the following:

Dim deleting As Boolean [COLOR=#0000ff]'at the beginning of the whole code[/COLOR]
'
Private Sub ListBox1_Click()
    Dim Item As String
    Dim myRange As Range
    If deleting = True Then Exit Sub
    '
    deleting = True     '[COLOR=#0000ff]activate the variable[/COLOR]
    Sheet1.Activate
    
    Item = UserForm1.ListBox1.Value
    
    Set myRange = Range("A1", Range("A65535").End(xlUp))
    myRange.Find(Item, LookIn:=xlValues, Lookat:=xlWhole).Select
    Selection.Delete Shift:=xlUp
    UserForm1.ListBox1.RowSource = Range("A1", Range("A65535").End(xlUp)).Address
    deleting = False    '[COLOR=#0000ff]Deactivate the variable[/COLOR]
End Sub

Hi Dante,
Thank you for your fast response. I have so much to learn. Having said that, I'm struggling with declaring a global variation at the moment so I can test your response.

 

[MicroMouse]

Sorry, will remember in future.

 

[DanteAmor]

Hi Dante,
Thank you for your fast response. I have so much to learn. Having said that, I'm struggling with declaring a global variation at the moment so I can test your response.

Just put this line at the beginning of all your code, with that the declaration is made.

Dim deleting As Boolean