Run-time 1004 Error when closing workbook

[ajh1]

I'm baffled by what is happening with this error. In reading a lot of posts on-line the normal issue is a failure to fully define a Range. In my case I believe I have defined everything and the attached logic works perfectly during the time I am within the spreadsheet. The problem develops when I go to close the workbook. Most typically I get the error just after clicking on "Don't Save" in the message box. The call stack shows this routine is being called due to one of a variety of combo boxes recording a change. That activation is as expected for when I'm actually selecting something in the combo box and the process is intended to clear some working space of old data. What I don't understand is why the combobox change routine is activating when exiting the workbook.

An interesting quirk of this is that if I set a break on the first line in this routine, it will break at that point during the exit process. I can then click Resume and it will execute properly without creating the error message and ultimately close the program. There does seem to be a lot of calculations going on during that exit process which doesn't make a whole lot of sense to me.

Sub InitializeCCZoneArea()
'Initialize options storage area to zero to avoid out of date entries
Dim row As Integer
Dim col As Integer

Worksheets("CC Wind").Range(Cells(123, 2), Cells(128, 40)).Value = ""
Worksheets("CC Wind").Range(Cells(131, 3), Cells(136, 40)).Value = ""
' Worksheets("CC Wind").Range(Cells(139, 2), Cells(144, 40)).Value = ""
Worksheets("CC Wind").Range(Cells(147, 2), Cells(152, 40)).Value = ""

Worksheets("CC Wind").Range(Cells(157, 3), Cells(162, 40)).Value = ""
Worksheets("CC Wind").Range(Cells(165, 3), Cells(170, 40)).Value = ""
' Worksheets("CC Wind").Range(Cells(173, 2), Cells(178, 40)).Value = ""
Worksheets("CC Wind").Range(Cells(181, 3), Cells(186, 40)).Value = ""

Worksheets("CC Wind").Range(Cells(191, 3), Cells(192, 40)).Value = ""
Worksheets("CC Wind").Range(Cells(195, 3), Cells(196, 40)).Value = ""
Worksheets("CC Wind").Range(Cells(200, 3), Cells(201, 40)).Value = ""
Worksheets("CC Wind").Range(Cells(204, 3), Cells(205, 40)).Value = ""

End Sub

Any help or suggestions would be appreciated. I've run out of ideas.

thanks.

 

[Andrzejek]

Did you try instead of:

Worksheets("CC Wind").Range(Cells(123, 2), Cells(128, 40)).Value = ""
Worksheets("CC Wind").Range(Cells(131, 3), Cells(136, 40)).Value = ""

something like:

With Worksheets("CC Wind")
    .Range("B123:AN128").Value = ""
    .Range("C131:AN136").Value = ""
End With

---- Andy

There is a great need for a sarcasm font.

 

[ajh1]

The error now points to the With Worksheets("CC Wind") line.

Any idea why this logic is even trying to execute when closing the workbook?

Al..

 

[Andrzejek]

Do you have a worksheet named [tt]CC Wind[/tt] ?
What is the logic/cide that calls [tt]InitializeCCZoneArea[/tt] ?

Just a gut felling... this Sub is being called after the Workbook is closed...


---- Andy

There is a great need for a sarcasm font.

 

[ajh1]

My sentiments exactly, but why is it being called? Happens when I try to close the workbook. I am on the "CC Wind" worksheet at the time.

Call stack:
VBAProject.Sheet1.cmbEnvelope_Change
VBAProject.Snow_WindRoutines.FillActiveCCWindZones
VBAProject.Snow_WindRoutines.InitializeCCZoneArea

Sheet1 is "CC Wind".

For normal operation, the call stack is logical and expected (and works just fine).
I don't see why this call stack would be activated when closing the Workbook. I have no specific logic coded for the close routine so it is operating off of normal Excel practices.

Al..

 

[SkipVought]

“Any idea why this logic is even trying to execute when closing the workbook?”

Not without seeing ALL the code in your workbook. Please upload.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ajh1]

Here it is. See attachment.

 

[SkipVought]

Strange, I executed [highlight #FCE94F]this[/highlight] statement

Sub InitializeCCZoneArea()
    'Initialize options storage area to zero to avoid out of date entries
    
        With Worksheets("CC Wind")
            [highlight #FCE94F].Range("B123:AN128").ClearContents[/highlight]
'...

...and immediately [highlight #FCAF3E]this[/highlight] Function was called...
[highlight #FCAF3E]displayLevelDescriptionLine1[/highlight]

BTW, I prefer the ClearContents method to er..., um..., uh..., clear the contents of cells.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

So I tried it again this AM and this time a different function fired, [highlight #FCAF3E]InterpolateCoeff[/highlight] which I understand, as that function is used on CC Wind in column AO.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Seems that FillActiveCCWindZones is firing on Closing the workbook.

So I DELETED ActiveX button [highlight #FCE94F]Fill C&C Zone Data[/highlight] (for debug use only???) and it closed just fine.

You might try to delete the button, Save, Close, Open, Add new ActiveX button or a Form Controls button.

What else does a retired guy have to do on Saturday morning: cup of coffee, some Robert Schumann and Excel. Bach would only make it slightly better.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ajh1]

Thanks for your efforts, Skip.
Personally, I'm still weekday worker (another year or so) so I try to avoid thinking on issues such as this over the weekend.

There are about 10 paths that end up calling FillActiveCCWindZones. Most of my hits have been as part of one of the combo boxes up near the top of the page. I hadn't seen the button you're referring to cause it to happen in any of my attempts to solve this. I'll give your idea a try and see if seems to consistently resolve the issue.

displayLevelDescriptionLine1 is used over on the Seismic sheet so why it has any relationship whatsoever to the FillActiveCCWindZones routine escapes me.

Again thanks and I'll keep everyone posted on how it goes.

Al..

 

[SkipVought]

The button was on the CC Wind sheet, as I recall.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ajh1]

Skip, for some reason I never had an issue with the debug button that you were able to remove and reinput and clear the problem. My issues were with either the cmbEnvelope or cmbRoofShape combo boxes. Ultimately I chose to reconfigure those into in-cell drop down lists and eliminate the combo boxes altogether. I also reconfigured a couple of the forms combo boxes, not so much because they were causing a problem but to get the sheet having a slightly more consistent look and feel. That complicated slightly a bit of the background logic, but not significantly. The process appears to be running cleanly now without blowing up on exit.

Skip and Andy, thanks again for your assistance and efforts. We may never know why Excel decided that it had to run the change routines that activated the issue upon exit. I sure don't have any better idea on that after several days of trying to work through the issue.

Al..

 

[combo]

Your problem is linked to cmbRoofShape (at least). This is a two column combo, with data linked to worksheet (list and selection). In this case VBA resets the VBA project several times on opening, when you close the workbook the control fires "Change" event with all the recalculation. None of those two happens in case of single column combo. You can easily use one column combo with VLOOKUP formula to get proper index from the selection.
Your workbook contains Worksheet_Change procedures that fire when macro changes worksheet. This is also a potential source of problems.


combo