Compile Error on WB Close ONLY

[Toby Corner]

Hi all,

I have an old (97-2003) Excel workbook I'm converting to new version in Office 365. I've saved down as new file version, and VB compiles fine and all works within the workbook, but when I close the file it gives the below:

Note that cmbSeason is a COmboBox created in Developer > Design mode - looks like the following:

Running the macro whilst workbook is open is absolutely fine so getting confused as to why it only seems to cause an issue on closing the file. Any ideas?

Toby

 

[Yongle]

Welcome to MrExcel

Converting a file that old may give you many other problems in future
- too much has changed in Excel and it is still changing at a rapid rate
Consider creating a new workbook and rebuilding from the ground up (step by step) rather than converting
- converting is a compromise that you may seriously regret later


In the meantime ...
Is that code in a SHEET code window or in a Module?

 

[Toby Corner]

Welcome to MrExcel

Converting a file that old may give you many other problems in future
- too much has changed in Excel and it is still changing at a rapid rate
Consider creating a new workbook and rebuilding from the ground up (step by step) rather than converting
- converting is a compromise that you may seriously regret later


In the meantime ...
Is that code in a SHEET code window or in a Module?

The code is in a SHEET code window, not a module.

 

[Yongle]

How are you using Sheet3?
- is it the sheet's VBA codename?
- or is it a sheet variable declared in your macro?

What is the TAB name of that sheet?

Is the code in Sheet3's code window or in a different sheet?

If you do not understand my question, please post an image as below from VBA to help

 

[Toby Corner]

How are you using Sheet3?
- is it the sheet's VBA codename?
- or is it a sheet variable declared in your macro?

What is the TAB name of that sheet?

Is the code in Sheet3's code window or in a different sheet?

If you do not understand my question, please post an image as below from VBA to help
View attachment 24721

TABNAME is "Costings", screenshot to confirm. And yes the code is on Sheet3s window:

How are you using Sheet3?
- is it the sheet's VBA codename?
- or is it a sheet variable declared in your macro?

What is the TAB name of that sheet?

Is the code in Sheet3's code window or in a different sheet?

If you do not understand my question, please post an image as below from VBA to help
View attachment 24721

I have replaced Sheet3 with Sheets("Costings") in every instance and I no longer get the issue - excellent suspicions!! The Sheet3 way of referencing must be an older version thing perhaps.

Thanks so much, rebuilding would not have been a great option as it is a mammoth s/sheet.

 

[Yongle]

Glad my questions pointed you in the correct direction

The code is in the sheet code of sheet "Costings". Both of these methods should also behave correctly:

Below, VBA should interpret Me as Sheet "Costings"

Me.Cells(1, 3)= Me.cmbSeason.Value

Because the code is in the sheet code window, VBA assumes that an unreferenced object is also in that sheet. Me can be helpful to clarify things in the code but is in fact not required.

Cells(1, 3)= cmbSeason.Value

 

[Toby Corner]

Glad my questions pointed you in the correct direction

The code is in the sheet code of sheet "Costings". Both of these methods should also behave correctly:

Below, VBA should interpret Me as Sheet "Costings"

Me.Cells(1, 3)= Me.cmbSeason.Value

Because the code is in the sheet code window, VBA assumes that an unreferenced object is also in that sheet. Me can be helpful to clarify things in the code but is in fact not required.

Cells(1, 3)= cmbSeason.Value

Again, many thanks!