Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting "Save As" adds "Copy of" to file name- MS Excel 2007 | Excel Discussion (Misc queries) | |||
Unable to open a 82 KB XLSM file due to "Too many different cellformats" & "Converter failed to open the file." errors. | Excel Discussion (Misc queries) | |||
Unable to open a 82 KB XLSM file due to "Too many different cellformats" & "Converter failed to open the file." errors. | Setting up and Configuration of Excel | |||
Problem- Recording macros for "file save" and "File open" | Excel Programming | |||
how to change default file name in "save as" or "save" | Excel Programming |