The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.
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.
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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
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.
I suspect it is. If either the linked cell or list fill range change then the Change event will be triggered.
One option is to ignore errors when that line of code is executed:
On Error Resume Next
SupplierNameTextBox.Enable
On Error GoTo 0
Another is to create a macro that does the refresh and, as the macro starts, sets a global switch that allows the Change event handler to alter it's behavior:
Public Sub Refresh()
gbRefreshInProgress = True
' do the refresh
gbRefreshInProgress = False
End Sub
And then, in the Change event handler:
If Not gbRefreshInProgress Then
SupplierNameTextBox.Enable
End If
Kevin
One option is to ignore errors when that line of code is executed:
On Error Resume Next
SupplierNameTextBox.Enable
On Error GoTo 0
Another is to create a macro that does the refresh and, as the macro starts, sets a global switch that allows the Change event handler to alter it's behavior:
Public Sub Refresh()
gbRefreshInProgress = True
' do the refresh
gbRefreshInProgress = False
End Sub
And then, in the Change event handler:
If Not gbRefreshInProgress Then
SupplierNameTextBox.Enable
End If
Kevin
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
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.
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
Kevin