Listbox has some items selected when userform containing it opens.

[Tom]

Sorry if this has allready been addressed. I could not find this specific
issue anywhere.

It sounds strange but multi-select listboxs inside userforms called by
double-clicking on different ranges of cells will sometimes open with items
(the same items when it does it) already selected. The real strange part is
that it seems to be linked to the width and/or height of the cells in and
arround the ranges. I.e. if I change the width of the column next to the
range that triggers the form, sometimes the pre-selection stops. But then it
may start in another userform-listbox (I have 5 set up based on different
columns being double-clicked). The listbox is loaded from dynamic named
range on another worksheet via its "RowSource" property. I've tried clearing
the list when form is initialized, AfterUpdate for Listbox, BeforeUpdate for
list box, .... I've even tried loading the listbox from an array instead of
the dynamic named range.... but still get same issue. The only way I can
clear the selections is manually clicking on them or having a control button
set up to clear the selections when pressed.

Is there some way to make sure listbox selections are cleared when form is
called?


Thanks

 

[John Keith]

I was about to post a question about this same kind of thing.
I have tried all the same kinds of ideas to keep the List box from
populating the listindex by itself too.

I noticed that the strange behavior seems to occur when the double-clicked
cell being used to launch the event just happened to be in the exact same
area as where the mouse would be positioned to select a choice from the
listbox. It seems like the 2nd click of the initial double click is also
being registered as the single click to select an item. (Tom, can you verify
if your issues arrise from the same kind of mouse to listbox item positioning
on the screen?)

I have attempted to put a short delay-loop before the double-click event
code executes the .show for the form. but it still didn't stop it from
happening.

Any thoughts on what to try next?

 

[Tom]

Now that you mention it.. it does seem to choose as the second selection (top
item selected) the item in the list that is at the location on the screen
where the double-click occured.. Consistent with your double click theory.

Not an expert, know just enough to be dangerous, but I did try some
tinkering....

1. Used BeforeRightClick to trigger form ... no pre-selected items
(consistent with "double click" as problem theory)... but then have to
contend with right click menu after exiting form.

2. Used "SelectionChange" to trigger form.... no pre-selected items!!
Works ok but minor hassels:
a) User has to move out of the cell and then back to the cell to trigger the
userform again if they need to make changes.
b) This approach complicates having macro automatically move to next column
over (I have 5 columns next to each other that the macro leads the user
through and they may or may not enter data into them). As the macro selects
the next column it fires that column's form even if the user was not planning
to enter anything in that column.
c) Speed concerns... I have to check this out for impact. Double-click is a
much "rarer" event for macro to watch than "SelectionChange"... so the macro
needs to check constantly to see if user is selecting a cell in one of the 5
columns so it know to open up an appropriate userfrom instead of napping
until a double-click.

First wish problem did not occur but if it must it would be nice to have
some way to clear the selections after listbox is loaded but before user is
presented with form...

Regards,
- Tom

 

[John Keith]

I changed my procedure to use the beforeright click and all is well.

To prevent the Right click menu from popping up when using the event, Add a
line of code:

Cancel = true

This sets the 2nd var on the event call that will prevent the original
action from taking place, I.E. no right-click menu will show up. This same
thing is available on the beforedoubleclick which prevents the cell going
into edit mode (too bad it's broken when using listboxes)

If you still need the rightclick menu sometimes, use target.row and
target.column to set up a range or ranges that will control when the rest of
the event code will run.

 

[NickHK]

Maybe you can both position the userform suitably:
http://www.cpearson.com/excel/FormPosition.htm

NickHK