Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Redraw problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Redraw problem
Have you tried turning screenupdating off and then on again?
If that doesn't work then maybe show your code ? Tim "Greg Lovern" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Redraw problem
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 On Sep 21, 8:27 pm, "Tim Williams" <timjwilliams at gmail dot com wrote: Have you tried turning screenupdating off and then on again? If that doesn't work then maybe show your code ? Tim "Greg Lovern" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Redraw problem
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 "Greg Lovern" wrote in message ... 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 On Sep 21, 8:27 pm, "Tim Williams" <timjwilliams at gmail dot com wrote: Have you tried turning screenupdating off and then on again? If that doesn't work then maybe show your code ? Tim "Greg Lovern" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
redraw problem | Excel Programming | |||
Excel does not redraw iteself | Excel Programming | |||
Redraw Application Window | Excel Programming | |||
How can you turn off screen Redraw | Excel Programming | |||
How can you turn off screen Redraw | Excel Programming |