Redraw problem

[Greg Lovern]

I have a single combobox that I move around, repopulate, and make
visible or invisible, depending on what cell or cells the user
selects. Works fine except this problem:

If the user holds an arrow key down, so that the active cell is moved
quickly across columns, and the macro makes the combobox appear on
some of those columns and disappear on others, and then the user
releases the arrow key and lands on a column for which the combobox is
made to disappear, sometimes the combobox remains visible on the last
column for which it was made visible.

I've checked to ensure that screenupdating wasn't inadvertently left
off, and it was not. I've verified that screenupdating is turned *on*
when this happens.

If I scroll the sheet a page or two away, so that the combobox
disappears, then scroll back, I no longer see the combobox, indicating
that Excel did a redraw making the combobox invisible.

Or, if I just enter anything in the active cell, the combobox
disappears, also indicating that Excel did a redraw making the
combobox invisible.

Any suggestions? Would it be a bad idea to try to use the Win32 API
function RedrawWindow on Excel, and if so, what parameters? I haven't
used that one before and if there is a working sample out there that
make Excel redraw, I'd sure rather start with that.


(BTW, in case you're wondering why we're not using Excel's validation
dropdowns instead: we were, but the users hated them because you can't
type in the value if you know it and enter it a hundred times a day;
instead you must always use the mouse. That's extremely frustrating
for users who do a lot of data entry. Using the combobox control
solves that problem, and also provides auto-complete, which those
users also want. To avoid having millions of comboboxes, I move a
single one around and repopulate it etc. as necessary.)


Thanks,

Greg

 

[Tim Williams]

Have you tried turning screenupdating off and then on again?

If that doesn't work then maybe show your code ?

Tim

 

[Greg Lovern]

Cycling screenupdating does make the problem I described go away.

However, various other strange things happen. Moving the
screenupdating cycling to a function called by a timer was a small
improvement, but still does weird things -- unexpected cells become
selected, the selection appears to sometimes reverse and move in the
opposite direction without my changing the keyboard key I was holding
down, the dropdown list from a previous column stays visible, etc.

For now, I'm just going to live with the original problem, as it's
easily worked around by the users, and doesn't seem as bad as the
alternative. Also, tens of thousands of users have been living with it
for several months, and no one has complained.


As for posting the code -- it would be a lot of code, and the client
might not be excited about that much of it being posted.


Thanks,

Greg

 

[Tim Williams]

I had the same problem and partially resolved it by using a static variable
as a "flag" within the event handler: I know that there shouldn't be
"overlapping" instances of the code running at any one time, but rapidly
cycling through cells did seem to cause odd behaviour and the flag seemed to
help.

'---------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Static bInProcess As Boolean

If bInProcess Then
Exit Sub
Else
bInProcess = True
End If

'**********
'do stuff here
'**********

bInProcess = False

End Sub
'----------------------------------------------------------------

Have to be careful not to ever leave the sub without switching back the flag
though...

Tim