VBA errors when doing "File -- Save As"
 

Very strange problem. I have a workbook with quite a bit of code in
it, controls, a graph, some text boxes that are hidden or shown
depending on results. The final product is a deliverable to a client,
so it needs to work. It's a little messy in the code area, and I do
unprotect and protect the main sheet, but it does work.

All the code works fine, and I can save the file under the same name
without incident (File -- Save). But when I do File -- Save As and
select a different filename, I get many VBA error messages. I have to
repeatedly hit the End button before the code stops executing. Most of
the errors are centered on the text boxes (saying the objects are
found or something like that), and one chokes on a simple statement
for selecting a named range. Once I do that, I have a completely
functioning copy of the original, under the new filename.

I've done some troubleshooting - commenting out the text box
references still leaves the named range error. I did get it to stop by
commenting out all of the worksheet unprotect and protect statements.
Now when I save the file, it still runs one of the macros several
times, which I assume is being fired by the Worksheet.Change
subroutine.

At one time I did have the following code, which was the first thing I
commented out:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If SaveAsUI = False Then
Response = MsgBox("Do you really want to overwrite this file?"
+ _
Chr(10) + Chr(10) + "[Yes] - Overwrite file with same name
(Save)" + _
Chr(10) + "[No] - Do not overwrite this file" + Chr(10) +
Chr(10) + _
"Use File--Save As to save under a different name", 4,
"Overwrite file?")
If Response = 6 Then
Cancel = False
Else
Cancel = True
End If
End If
End Sub

I'm baffled - what does Excel do in terms of VBA when you "save as" a
new file?

Thanks,
Scott

VBA errors when doing "File -- Save As"
 

This is obviously a guess, but

If you have controls linked to cells, closing a workbook causes a
calculation which can cause change events related to the controls to
execute. I generally fix this by removing these links, then using the
event code to write and fetch values.

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
Very strange problem. I have a workbook with quite a bit of code in
it, controls, a graph, some text boxes that are hidden or shown
depending on results. The final product is a deliverable to a client,
so it needs to work. It's a little messy in the code area, and I do
unprotect and protect the main sheet, but it does work.

All the code works fine, and I can save the file under the same name
without incident (File -- Save). But when I do File -- Save As and
select a different filename, I get many VBA error messages. I have to
repeatedly hit the End button before the code stops executing. Most of
the errors are centered on the text boxes (saying the objects are
found or something like that), and one chokes on a simple statement
for selecting a named range. Once I do that, I have a completely
functioning copy of the original, under the new filename.

I've done some troubleshooting - commenting out the text box
references still leaves the named range error. I did get it to stop by
commenting out all of the worksheet unprotect and protect statements.
Now when I save the file, it still runs one of the macros several
times, which I assume is being fired by the Worksheet.Change
subroutine.

At one time I did have the following code, which was the first thing I
commented out:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If SaveAsUI = False Then
Response = MsgBox("Do you really want to overwrite this file?"
+ _
Chr(10) + Chr(10) + "[Yes] - Overwrite file with same name
(Save)" + _
Chr(10) + "[No] - Do not overwrite this file" + Chr(10) +
Chr(10) + _
"Use File--Save As to save under a different name", 4,
"Overwrite file?")
If Response = 6 Then
Cancel = False
Else
Cancel = True
End If
End If
End Sub

I'm baffled - what does Excel do in terms of VBA when you "save as" a
new file?

Thanks,
Scott