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.Enabled = False

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.Enabled = False" line in my vba code.

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.
jobprojnAsked:
Who is Participating?
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.

zorvek (Kevin Jones)ConsultantCommented:
I have seen this kind of problem before and solved it by deselecting the ActiveX control. This is done simply by selecting any range of cells as soon as you enter the event handler.

Kevin
0
jobprojnAuthor Commented:
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").Select
                SupplierNameTextBox.Enabled = False

End Sub

Thanks.
0
zorvek (Kevin Jones)ConsultantCommented:
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
0
Cloud Class® Course: Microsoft Exchange Server

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.

jobprojnAuthor Commented:
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.
0
zorvek (Kevin Jones)ConsultantCommented:
Is the control tied to a cell that might be impacted by the refresh?

Kevin
0
zorvek (Kevin Jones)ConsultantCommented:
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.Enabled = False
    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.Enabled = False
    End If

Kevin
0

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
jobprojnAuthor Commented:
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.
0
jobprojnAuthor Commented:
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
0
zorvek (Kevin Jones)ConsultantCommented:
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
0
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.