AOB
Well-known Member
- Joined
- Dec 15, 2010
- Messages
- 667
- Office Version
- 365
- 2016
- 2013
- Platform
- Windows
Hi guys,
I have a workbook with a series of worksheets, each of which has an ActiveX listbox in the top-right corner.
The listboxes are dynamically populated by a single public function which is called from the Worksheet_Activate event of each of the worksheets
(The call passes the listbox object to the function which then fills the listbox with the appropriate values based on some dynamic named ranges)
The listboxes also have a _Change event within each of the parent worksheets, which fires another single public function
(Again, passing the listbox object as a parameter, which runs some code to hide/unide certain areas of the parent worksheet - nothing too crazy)
The code all works absolutely fine...
However - the weird thing is - sometimes when I click on a worksheet, the listbox appears to be disabled?
It's not greyed out, it's populated with the expected values, everything 'looks' okay - but I can't select anything in it (to fire the change event). The mouse pointer stays as the "block-plus"-type character (as if I were selecting a cell) instead of a pointer arrow (to suggest selecting from the list box) Clicking on the listbox has no effect.
If I switch to another worksheet and then switch back, it comes back - looks exactly the same but now the mouse pointer is back to an arrow and I can select/deselect at will and the change event does exactly what it should do.
Switch between worksheets and back again and it's gone again??
The behaviour is not uniform - it's not as if switching between sheets enables and disables the listbox every time - it's totally random. Sometimes I have to switch sheets 3 or 4 times before it starts to work again.
Once it works, it stays working for the duration of time that worksheet is active. Once I leave the worksheet, the randomness kicks in again.
I've checked the Enabled and Locked properties in the Immediate Window when the behaviour occurs and they are exactly what they should be (Enabled = True, Locked = False)
For curiosity's sake, I stuck a _MouseMove event on one of the listboxes and stuck a breakpoint inside it. When the listbox is working, the _MouseMove event fires as expected. When the listbox is not working, it doesn't fire. So it's as if the control is just completely dead.
I stuck in some error handlers on the functions and at no point is any error thrown. So I can't pin it down to the code. It runs perfectly fine every time, but the listbox may or may not be available for selections afterwards.
Another weird thing I noticed - if I switch to a worksheet where the listbox appears dead, and then, say, resize a column or a row, the listbox suddenly comes alive again? But other changes to the worksheet (selecting or editing a cell for example) have no effect on it.
It's really bizarre behaviour and will be quite frustrating for the end users, who would use these listboxes frequently to quickly switch between sections of the worksheet they're working on. I don't want to distribute it with the caveat of "oh, if the listboxes don't seem to work, keep switching back and forth with another worksheet until they do" or "yeah, just resize column A a little bit and it will come back" - it needs to be nice and neat, or else there's no point having it there at all.
Anybody have similar problems with embedded ActiveX controls on a worksheet and find a workaround?
Would be extremely grateful for any suggestions!! (I've already explored switching from ActiveX to Form controls and I can't go down this route - I need to be able to hook into the listboxes in the VBA for the functionality to work properly)
Thanks
AOB
I have a workbook with a series of worksheets, each of which has an ActiveX listbox in the top-right corner.
The listboxes are dynamically populated by a single public function which is called from the Worksheet_Activate event of each of the worksheets
(The call passes the listbox object to the function which then fills the listbox with the appropriate values based on some dynamic named ranges)
The listboxes also have a _Change event within each of the parent worksheets, which fires another single public function
(Again, passing the listbox object as a parameter, which runs some code to hide/unide certain areas of the parent worksheet - nothing too crazy)
The code all works absolutely fine...
However - the weird thing is - sometimes when I click on a worksheet, the listbox appears to be disabled?
It's not greyed out, it's populated with the expected values, everything 'looks' okay - but I can't select anything in it (to fire the change event). The mouse pointer stays as the "block-plus"-type character (as if I were selecting a cell) instead of a pointer arrow (to suggest selecting from the list box) Clicking on the listbox has no effect.
If I switch to another worksheet and then switch back, it comes back - looks exactly the same but now the mouse pointer is back to an arrow and I can select/deselect at will and the change event does exactly what it should do.
Switch between worksheets and back again and it's gone again??
The behaviour is not uniform - it's not as if switching between sheets enables and disables the listbox every time - it's totally random. Sometimes I have to switch sheets 3 or 4 times before it starts to work again.
Once it works, it stays working for the duration of time that worksheet is active. Once I leave the worksheet, the randomness kicks in again.
I've checked the Enabled and Locked properties in the Immediate Window when the behaviour occurs and they are exactly what they should be (Enabled = True, Locked = False)
For curiosity's sake, I stuck a _MouseMove event on one of the listboxes and stuck a breakpoint inside it. When the listbox is working, the _MouseMove event fires as expected. When the listbox is not working, it doesn't fire. So it's as if the control is just completely dead.
I stuck in some error handlers on the functions and at no point is any error thrown. So I can't pin it down to the code. It runs perfectly fine every time, but the listbox may or may not be available for selections afterwards.
Another weird thing I noticed - if I switch to a worksheet where the listbox appears dead, and then, say, resize a column or a row, the listbox suddenly comes alive again? But other changes to the worksheet (selecting or editing a cell for example) have no effect on it.
It's really bizarre behaviour and will be quite frustrating for the end users, who would use these listboxes frequently to quickly switch between sections of the worksheet they're working on. I don't want to distribute it with the caveat of "oh, if the listboxes don't seem to work, keep switching back and forth with another worksheet until they do" or "yeah, just resize column A a little bit and it will come back" - it needs to be nice and neat, or else there's no point having it there at all.
Anybody have similar problems with embedded ActiveX controls on a worksheet and find a workaround?
Would be extremely grateful for any suggestions!! (I've already explored switching from ActiveX to Form controls and I can't go down this route - I need to be able to hook into the listboxes in the VBA for the functionality to work properly)
Thanks
AOB
