RunTime 91 error

julhs

Active Member
Joined
Dec 3, 2018
Messages
404
Office Version
  1. 2010
Platform
  1. Windows
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
You cannot have two VBA procedures in the same module with the same name. Otherwise, how would it know which one to run?
You need to combine them both under a single procedure, if you want them both to apply to the same event.
 
Upvote 0
Im not sure I follow?

They are both change events on the Worksheet, not in module. But are in different Workbooks that are not open at the same time
 
Upvote 0
Sorry, I totally misread your question.

The second one returns an error, not when you click on the Combo Box, but when you close the Workbook?
How do you know that it is that code that is causing the error?
If you hit "Debug", which line of code does it highlight?

Do you have other VBA code in the workbook, especially some "Before Close" event procedure code?
If so, please post it.
 
Upvote 0
Erro not returned when Combo box value is changed, only when I close the Workbook.
Runtime error box appears on closing, and the error is on “ActiveSheet.[i7].Select.
No other code on the Worksheet or Workbook for that matter other than a couple more "Combox Change events"
 
Upvote 0
Hmmm...
That does not seem to make any sense, but closing the workbook should not trigger that code to run, unless you had something in a "BeforeClose" event that selected that combo box.

If you open that workbook, and Compile the code, does it give you any errors then?
If the worksheet that this code is found on open/active when you go to close the workbook?
Is there anything special about cell I7? Is it hidden, locked, or part of a merge field?
 
Upvote 0
I’ll try re-compile the problem code.

But it is just the non-problem one copied & pasted in with a change of cell # for the different active sheet.
Compile error dialog box only appears after I click to “Save it or Not”, so when the book is effectively closing.
Yes it’s open and the active sheet (Workbook only has 3 Sheets).
Nothing special about I7, yes locked but only when Book protected, not part of merged cells either
 
Upvote 0
What format are you trying to save the file in?
Is it one of the Macro/VBA enabled formats?

Also note that if this code is already in a Worksheet module, you shouldn't need all those sheet references (as by default, it only applies to that sheet).
Try this instead:
VBA Code:
Private Sub ComboBox1_Change()
    Range("I7").Select
End Sub
Also, verify that the name of your ComboBox on this sheet is actually "ComboBox1".
 
Upvote 0
No error while re-compiling, but end result the same.
Saved as xlsm.
ComboBox is labelled “ComboBox1” in its properties, it’s an EMBED("Forms.ComboBox.1","")

I take your point about putting it in a “Module” but there is only 3 Sheets in the Book, only one has Combobox’s on it and one is even blank.
 
Upvote 0
I take your point about putting it in a “Module” but there is only 3 Sheets in the Book, only one has Combobox’s on it and one is even blank.
I never said to put it in a different module - you want it in the Worksheet module where the Combo box resides.

Did you try my updated version of the code?
I have never used embeded combo boxes. I am not sure that "Forms.ComboBox.1" is the same as "ComboBox1".
In your workbook where it is working, is that also an embedded ComboBox named EXACTLY the same way?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top