×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Run-time 1004 Error when closing workbook

Run-time 1004 Error when closing workbook

Run-time 1004 Error when closing workbook

(OP)
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.

RE: Run-time 1004 Error when closing workbook

Did you try instead of:

CODE

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

something like:

CODE

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


---- Andy

There is a great need for a sarcasm font.

RE: Run-time 1004 Error when closing workbook

(OP)
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..

RE: Run-time 1004 Error when closing workbook

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

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


---- Andy

There is a great need for a sarcasm font.

RE: Run-time 1004 Error when closing workbook

(OP)
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..

RE: Run-time 1004 Error when closing workbook

“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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Run-time 1004 Error when closing workbook

Strange, I executed this statement

CODE

Sub InitializeCCZoneArea()
    'Initialize options storage area to zero to avoid out of date entries
    
        With Worksheets("CC Wind")
            .Range("B123:AN128").ClearContents
'... 
...and immediately this Function was called...
displayLevelDescriptionLine1

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Run-time 1004 Error when closing workbook

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Run-time 1004 Error when closing workbook

Seems that FillActiveCCWindZones is firing on Closing the workbook.

So I DELETED ActiveX button Fill C&C Zone Data (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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Run-time 1004 Error when closing workbook

(OP)
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..

RE: Run-time 1004 Error when closing workbook

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Run-time 1004 Error when closing workbook

(OP)
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..

RE: Run-time 1004 Error when closing workbook

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close