RunTime 91 error

[julhs]

I am stumped as to why the 1st of the 2 Change Events below in one Workbook works without problems. But the 2nd one, in another Workbook causes a RunTime 91 error when I close the Workbook?

Private Sub ComboBox1_Change()
Dim wt As Worksheet
Set wt = ThisWorkbook.ActiveSheet
ActiveSheet.[e4].Select
End Sub

Private Sub ComboBox1_Change()
Dim wt As Worksheet
Set wt = ThisWorkbook.ActiveSheet
ActiveSheet.[i7].Select
End Sub

 

[julhs]

My apologies, my problem is with some of VDA terminologies. The code for the change event DOES reside in Worksheet module ( or as I think of it, “IN / ON” the sheet)

EMBED("Forms.ComboBox.1","") is just an ActiveX form.

Yes it is exactly the same. I copied and pasted the whole ComboBox and did the same for the “Change event” code and then changed the cell ref# to "I7"

I have tried your alternative now, giving Runtime 1004 error

I’m beginning to think the Workbook itself is corrupted?
So my only thought is to copy out the contents of the Worksheet to a new book??

 

[Joe4]

I’m beginning to think the Workbook itself is corrupted?
So my only thought is to copy out the contents of the Worksheet to a new book??

That is a possibility. I believe that ActiveX components can be a little flaky, and could be a security risk.
Is there some reason you have to use ActiveX Controls, instead of Excel's built-in Form Controls? Except for a calendar, that is what I always use.

 

[julhs]

Hi Joe4

I use ActiveX Combox’s all the time; have in region of 40 split over numerous Workbooks without a single issue until now, that is what’s so strange!

I feel they have more scope in themselves, they are stand alone (no need for additional Macros to achieve what I want), have more range of formatting and you can “Enter” values into the ActiveX Combox’s window if you wish.

Security is not an issue as the Workbooks are not shared.

I have experimented with clearing out the ENTIRE problem sheet of EVERYTHING except the “ActiveX Combo’s” and the issue remains!

While I could change the “ActiveX Combo’s to “Form Combo” and use a Macro to do the equivalent of the “Change event”. But that is not solving the problem as to why/how this is happening in ONLY this Workbook??

 

[julhs]

Well I’ve at least now ascertained that; all my “ActiveX Combo’s with a “Change event” assigned to them, that work WITHOUT a Runtime Error are ALL set to “BoundColumn 1 & ColumnCount 1” in their properties.

Whereas the Runtime Error issue is where they are set to “BoundColumn 2 & ColumnCount 2” because I wanted the Combo to display 2 columns of the range list and place the data from the 2nd in another cell

Need to rethink things!!

 

[julhs]

FINALLY CRACKED IT !!!!, it was simply a matter of changing “Private Sub ComboBox1_Change()” to “Private Sub ComboBox1_Click()”. NO MORE RunTime 91 error when closing the Workbook, so simple in the end!!

 

[Joe4]

Thanks for posting back to the solution.
Glad you were able to figure it out.
Not using the ActiveX controls, I did not know that.