Editing listbox items in Dblclick event freezes Excel unless mouse moves over listbox

**muneebmansoor** · 02-28-2013, 10:57 AM

Hi Everyone,

I'm stumped by a very weird Excel issue. Consider this sample scenario:

I have a simple listbox with 3 items in it. I double click on any of the three items, a message box pops up and I click OK. Now, Excel practically freezes (I can't click on any other control neither can I click the red cross to close the user form, can't use VBA either) UNLESS I move mouse pointer over the same listbox.

Attached is this sample scenario.

Could someone please help me out in figuring out the issue?

Thank you!

I asked the same question at http://www.ozgrid.com/forum/showthre...610#post651610

listboxissue.xlsm

**Norie** · 02-28-2013, 11:07 AM

What is this code meant to do?

    With lbValidation
        strRowSource = .RowSource
        .RowSource = vbNullString
        .RowSource = strRowSource
    End With

Without it everything works fine.

Perhaps the problem is because you are using the RowSource property which is a kind of 2 way thing between the worksheet and listbox.

Why not populate the listbox like this?

   lbValidation.List = Range(sRowsource).Value

**muneebmansoor** · 02-28-2013, 11:20 AM

Like I said this is a sample scenario. In the actual case, when a user double clicks on a value in Listbox, an input box pops in which user enters a value. This value is then written to a sheet and listbox is updated automatically since it has a static rowsource (I do not change the rowsource in this case).

I also noticed something that as long as listbox.listindex stays at 0 (if the first value is double-clicked), it works fine. But I also tried to force the listindex to 0 after writing the value to the sheet but once it goes to -1, it freezes everything.

**Norie** · 02-28-2013, 11:26 AM

Using RowSource is not a good idea, and this is the sort of thing that can happen when you do.

If you populate as I suggest it's straightforward to write a line (maybe two) of code to put the user entered value on the sheet and update the listbox.

**muneebmansoor** · 02-28-2013, 12:38 PM

Will this also work when I have multiple columns in the listbox?

**Norie** · 02-28-2013, 12:40 PM

Yes, though obviously there could be a few more lines of code.

**muneebmansoor** · 02-28-2013, 12:43 PM

Range("refLookups!$A$1:$C$5").Value - can this return 3 columns in the listbox?

**Norie** · 02-28-2013, 01:28 PM

Yes.

Set rng = Sheets("refLookups").Range($A$1:$C$5")

With lbValidation
     .ColumnCount = rng.Columns.Count
     .List = rngValues
End With

**muneebmansoor** · 02-28-2013, 01:44 PM

Thanks for your reply. Now I have another issue: I have some of these columns formatted in % and currency formats. Range.Value is not picking up formats. How do I display formatting in the listbox?

**muneebmansoor** · 02-28-2013, 02:21 PM

Using listbox.list does not solve the problem either! It still hangs up after the value is changed using input box.

Editing listbox items in Dblclick event freezes Excel unless mouse moves over listbox

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Editing listbox items in Dblclick event freezes Excel unless mouse moves over listbox

Re: Editing listbox items in Dblclick event freezes Excel unless mouse moves over listbox

Re: Editing listbox items in Dblclick event freezes Excel unless mouse moves over listbox

Re: Editing listbox items in Dblclick event freezes Excel unless mouse moves over listbox

Re: Editing listbox items in Dblclick event freezes Excel unless mouse moves over listbox

Re: Editing listbox items in Dblclick event freezes Excel unless mouse moves over listbox

Re: Editing listbox items in Dblclick event freezes Excel unless mouse moves over listbox

Re: Editing listbox items in Dblclick event freezes Excel unless mouse moves over listbox

Re: Editing listbox items in Dblclick event freezes Excel unless mouse moves over listbox

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions