Excel VBA ActiveX Change Event Error
Hello. Have a very interesting problem where when I invoke the _Change event on an ActiveX control. The change event works fine up until the point where I refresh any external data range within my Excel spreadsheet.
Below is my _Change event that is setup for an ActiveX combo box control called RunByComboBox.
Private Sub RunByComboBox_Change()
SupplierNameTextBox.Enable
End Sub
This code works perfectly fine up until the point where I refresh an external data range with my spreadsheet. Whenever I refresh a data range (any data range) I receive the following vba error message:
"Runtime error '1004'. Unable to set the Enabled property of the OLEobject class".
When I attempt to debut it takes me to the "SupplierNameTextBox.Enabl
Does anyone know why I'm getting this error message on data refresh? The data I'm refreshing is in no way related to the combo box or text box in question. Also, for note this error message only occurs when I use the _Change or _Click events. All other events are OK. Unfortunately, I need the _Change event to work.
Any insight is greatly appreciated.
Below is my _Change event that is setup for an ActiveX combo box control called RunByComboBox.
Private Sub RunByComboBox_Change()
SupplierNameTextBox.Enable
End Sub
This code works perfectly fine up until the point where I refresh an external data range with my spreadsheet. Whenever I refresh a data range (any data range) I receive the following vba error message:
"Runtime error '1004'. Unable to set the Enabled property of the OLEobject class".
When I attempt to debut it takes me to the "SupplierNameTextBox.Enabl
Does anyone know why I'm getting this error message on data refresh? The data I'm refreshing is in no way related to the combo box or text box in question. Also, for note this error message only occurs when I use the _Change or _Click events. All other events are OK. Unfortunately, I need the _Change event to work.
Any insight is greatly appreciated.
ASKER
Kevin, I tried adding the .Select line below and it didn't work. Is this what you had in mind?
Private Sub RunByComboBox_Change()
ActiveSheet.Range("B9").Se
SupplierNameTextBox.Enable
End Sub
Thanks.
Private Sub RunByComboBox_Change()
ActiveSheet.Range("B9").Se
SupplierNameTextBox.Enable
End Sub
Thanks.
Yes. But now that I read your question more carefully, I see that you are not actually invoking that code with a control event.
Replace the select statement with a Stop. Let's see if the code is actually being executed when you do the refresh. Are you setting the value of the control in another part of the program that is doing the refresh?
Kevin
Replace the select statement with a Stop. Let's see if the code is actually being executed when you do the refresh. Are you setting the value of the control in another part of the program that is doing the refresh?
Kevin
ASKER
Well I put the Stop in and the _Change event is actually firing on refresh. The refresh I'm doing in Excel is being done manually and I can't see any correlation between the data refresh and the control. I have no clue why the _Change or the _Click events fire for that control when the data is refreshed. It's driving me crazy.
Is the control tied to a cell that might be impacted by the refresh?
Kevin
Kevin
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You're on to something. The ListFillRange for the contol resides in the same worksheet as the data that is being refreshed. Although the control's ListFillRange and data being refreshed are un-releated when I move the ListFillRange to another worksheet the problem goes away.
My guess is when the data being refreshed is refreshing something's happening to the ListFillRange that's then firing the _Change or _Click events. The ListFillRange never actually changes, but somethings going on behind the scenes that I'm not seeing.
My guess is when the data being refreshed is refreshing something's happening to the ListFillRange that's then firing the _Change or _Click events. The ListFillRange never actually changes, but somethings going on behind the scenes that I'm not seeing.
ASKER
Kevin, you're a rockstar. Spent so much time on this. You got it immediately and helped me see a different angle. Thanks so much.
- Blayne
- Blayne
Then there you go!
What we do know is that ActiveX controls are notoriously finicky and don't work well on an Excel worksheet. I always use Forms controls for this reason even though they are more limited in scope and function. I'm not surprised that the ActiveX text box is complaining when you are trying to manipulate it's properties while something else is going on.
Given that, I suggest doing whatever you can to avoid that Change event from being triggered (move the source range to another worksheet) or make the Change event handler more resilient to the bad ways of ActiveX controls.
Kevin
What we do know is that ActiveX controls are notoriously finicky and don't work well on an Excel worksheet. I always use Forms controls for this reason even though they are more limited in scope and function. I'm not surprised that the ActiveX text box is complaining when you are trying to manipulate it's properties while something else is going on.
Given that, I suggest doing whatever you can to avoid that Change event from being triggered (move the source range to another worksheet) or make the Change event handler more resilient to the bad ways of ActiveX controls.
Kevin
Kevin