Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save as crashes Excel - sometimes
I have this code in the ThisWorkbook object:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim Fname As Variant Fname = Sheet1.Range("F3").Value & "-" & Sheet1.Range("F4").Value Application.Dialogs(xlDialogSaveAs).Show Fname End Sub Where F3 & F4 are text values, i.e. the cells are formatted as text. The cells contain a serial number and machine name, and so the file name is concatenated as "0000-Machine Name.xls" Sometimes on the file save event Excel (v2003) crashes. Most of the time it doesn't. The code works like it is supposed to - but is there something missing, or some error trapping I can add to prevent the crash? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save as crashes Excel - sometimes
While this should have no bearing on your problem, I would declare Fname as
String variable (not a Variant one) since you know you will be assigning text to it. As for your problem... where in the process is the "crash" taking place? Does the dialog box show up beforehand? If so, do you get to press the OK button? Can you describe the "crash"... is an error message generated (if so, what is it) or does Excel just cease working altogether (and close down) or does something else happen (if so, what)? -- Rick (MVP - Excel) "mooresk257" wrote in message ... I have this code in the ThisWorkbook object: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim Fname As Variant Fname = Sheet1.Range("F3").Value & "-" & Sheet1.Range("F4").Value Application.Dialogs(xlDialogSaveAs).Show Fname End Sub Where F3 & F4 are text values, i.e. the cells are formatted as text. The cells contain a serial number and machine name, and so the file name is concatenated as "0000-Machine Name.xls" Sometimes on the file save event Excel (v2003) crashes. Most of the time it doesn't. The code works like it is supposed to - but is there something missing, or some error trapping I can add to prevent the crash? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save as crashes Excel - sometimes
Hi Rick,
I'll change the variable to String and see if that helps. Here's the sequence: 1. I click the "save" icon 2. File save dialog opens, with the filename shown correctly in the file name text box 3. I click the "save" button and then I get the "excel encountered a problem and hod to close. Would you like to recover your file" message. I send an error report while the file reopens, and I find it has saved correctly and everything is OK with the file - every time. I've got to add some more stuff so that if F3 & F4 are "" then it skips the rest of the code, but I wanted to sort this problem out first. Then again, maybe that IS the problem! Scott "Rick Rothstein" wrote: While this should have no bearing on your problem, I would declare Fname as String variable (not a Variant one) since you know you will be assigning text to it. As for your problem... where in the process is the "crash" taking place? Does the dialog box show up beforehand? If so, do you get to press the OK button? Can you describe the "crash"... is an error message generated (if so, what is it) or does Excel just cease working altogether (and close down) or does something else happen (if so, what)? -- Rick (MVP - Excel) "mooresk257" wrote in message ... I have this code in the ThisWorkbook object: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim Fname As Variant Fname = Sheet1.Range("F3").Value & "-" & Sheet1.Range("F4").Value Application.Dialogs(xlDialogSaveAs).Show Fname End Sub Where F3 & F4 are text values, i.e. the cells are formatted as text. The cells contain a serial number and machine name, and so the file name is concatenated as "0000-Machine Name.xls" Sometimes on the file save event Excel (v2003) crashes. Most of the time it doesn't. The code works like it is supposed to - but is there something missing, or some error trapping I can add to prevent the crash? Thanks! . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save as crashes Excel - sometimes
Okay, I think I see what is going on here. As soon as you hit the OK button
on the Save As dialog box, it re-executes the BeforeSave event code... however, you still have the running BeforeSave code active from when you clicked on Excel's Save icon in the first place. I don't think you want to be executing your code in the BeforeSave event. Why not add a button, then place your code in a macro and assign that macro to the button you added (and totally remove the code you have in the BeforeSave event procedure)... I believe this will eliminate the conflict you are generating. -- Rick (MVP - Excel) "mooresk257" wrote in message ... Hi Rick, I'll change the variable to String and see if that helps. Here's the sequence: 1. I click the "save" icon 2. File save dialog opens, with the filename shown correctly in the file name text box 3. I click the "save" button and then I get the "excel encountered a problem and hod to close. Would you like to recover your file" message. I send an error report while the file reopens, and I find it has saved correctly and everything is OK with the file - every time. I've got to add some more stuff so that if F3 & F4 are "" then it skips the rest of the code, but I wanted to sort this problem out first. Then again, maybe that IS the problem! Scott "Rick Rothstein" wrote: While this should have no bearing on your problem, I would declare Fname as String variable (not a Variant one) since you know you will be assigning text to it. As for your problem... where in the process is the "crash" taking place? Does the dialog box show up beforehand? If so, do you get to press the OK button? Can you describe the "crash"... is an error message generated (if so, what is it) or does Excel just cease working altogether (and close down) or does something else happen (if so, what)? -- Rick (MVP - Excel) "mooresk257" wrote in message ... I have this code in the ThisWorkbook object: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim Fname As Variant Fname = Sheet1.Range("F3").Value & "-" & Sheet1.Range("F4").Value Application.Dialogs(xlDialogSaveAs).Show Fname End Sub Where F3 & F4 are text values, i.e. the cells are formatted as text. The cells contain a serial number and machine name, and so the file name is concatenated as "0000-Machine Name.xls" Sometimes on the file save event Excel (v2003) crashes. Most of the time it doesn't. The code works like it is supposed to - but is there something missing, or some error trapping I can add to prevent the crash? Thanks! . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save as crashes Excel - sometimes
OK - I just put this together and it's doing what I want so far, which is to
do a normal file save if F3 & 4 are empty, saving with the cell content of one or the other cell that's not empty, andcencelling the save event on "cancel" in the dialog box. I'm sure there's got to be a way to clean the code up a bit though! It remains to be seen if it crashes Excel - maybe "Fname as string" will solve that problem. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim Fname As String If IsEmpty(Sheet1.Range("F3")) Then _ If IsEmpty(Sheet1.Range("F4")) Then Exit Sub If Not IsEmpty(Sheet1.Range("F3")) Then _ If IsEmpty(Sheet1.Range("F4")) Then _ Fname = Sheet1.Range("F3").Value If Not IsEmpty(Sheet1.Range("F4")) Then _ If IsEmpty(Sheet1.Range("F3")) Then _ Fname = Sheet1.Range("F4").Value If Not IsEmpty(Sheet1.Range("F3")) Then _ If Not IsEmpty(Sheet1.Range("F4")) Then _ Fname = Sheet1.Range("F3").Value & "-" & Sheet1.Range("F4").Value On Error GoTo DumpSub Application.EnableEvents = False Cancel = True Application.Dialogs(xlDialogSaveAs).Show Fname DumpSub: Application.EnableEvents = True End Sub "mooresk257" wrote: Hi Rick, I'll change the variable to String and see if that helps. Here's the sequence: 1. I click the "save" icon 2. File save dialog opens, with the filename shown correctly in the file name text box 3. I click the "save" button and then I get the "excel encountered a problem and hod to close. Would you like to recover your file" message. I send an error report while the file reopens, and I find it has saved correctly and everything is OK with the file - every time. I've got to add some more stuff so that if F3 & F4 are "" then it skips the rest of the code, but I wanted to sort this problem out first. Then again, maybe that IS the problem! Scott "Rick Rothstein" wrote: While this should have no bearing on your problem, I would declare Fname as String variable (not a Variant one) since you know you will be assigning text to it. As for your problem... where in the process is the "crash" taking place? Does the dialog box show up beforehand? If so, do you get to press the OK button? Can you describe the "crash"... is an error message generated (if so, what is it) or does Excel just cease working altogether (and close down) or does something else happen (if so, what)? -- Rick (MVP - Excel) "mooresk257" wrote in message ... I have this code in the ThisWorkbook object: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim Fname As Variant Fname = Sheet1.Range("F3").Value & "-" & Sheet1.Range("F4").Value Application.Dialogs(xlDialogSaveAs).Show Fname End Sub Where F3 & F4 are text values, i.e. the cells are formatted as text. The cells contain a serial number and machine name, and so the file name is concatenated as "0000-Machine Name.xls" Sometimes on the file save event Excel (v2003) crashes. Most of the time it doesn't. The code works like it is supposed to - but is there something missing, or some error trapping I can add to prevent the crash? Thanks! . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save as crashes Excel - sometimes
I've seen crashes on a workbook open event that was looking for named ranges
that didn't exist, IIRC. Rick, why wouldn't you put Application.EnableEvents = False Application.EnableEvents = True Around the "secondary" save event so that it doesn't run the beforesave event again? Barb Reinhardt "Rick Rothstein" wrote: Okay, I think I see what is going on here. As soon as you hit the OK button on the Save As dialog box, it re-executes the BeforeSave event code... however, you still have the running BeforeSave code active from when you clicked on Excel's Save icon in the first place. I don't think you want to be executing your code in the BeforeSave event. Why not add a button, then place your code in a macro and assign that macro to the button you added (and totally remove the code you have in the BeforeSave event procedure)... I believe this will eliminate the conflict you are generating. -- Rick (MVP - Excel) "mooresk257" wrote in message ... Hi Rick, I'll change the variable to String and see if that helps. Here's the sequence: 1. I click the "save" icon 2. File save dialog opens, with the filename shown correctly in the file name text box 3. I click the "save" button and then I get the "excel encountered a problem and hod to close. Would you like to recover your file" message. I send an error report while the file reopens, and I find it has saved correctly and everything is OK with the file - every time. I've got to add some more stuff so that if F3 & F4 are "" then it skips the rest of the code, but I wanted to sort this problem out first. Then again, maybe that IS the problem! Scott "Rick Rothstein" wrote: While this should have no bearing on your problem, I would declare Fname as String variable (not a Variant one) since you know you will be assigning text to it. As for your problem... where in the process is the "crash" taking place? Does the dialog box show up beforehand? If so, do you get to press the OK button? Can you describe the "crash"... is an error message generated (if so, what is it) or does Excel just cease working altogether (and close down) or does something else happen (if so, what)? -- Rick (MVP - Excel) "mooresk257" wrote in message ... I have this code in the ThisWorkbook object: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim Fname As Variant Fname = Sheet1.Range("F3").Value & "-" & Sheet1.Range("F4").Value Application.Dialogs(xlDialogSaveAs).Show Fname End Sub Where F3 & F4 are text values, i.e. the cells are formatted as text. The cells contain a serial number and machine name, and so the file name is concatenated as "0000-Machine Name.xls" Sometimes on the file save event Excel (v2003) crashes. Most of the time it doesn't. The code works like it is supposed to - but is there something missing, or some error trapping I can add to prevent the crash? Thanks! . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save as crashes Excel - sometimes
Rick, why wouldn't you put
Application.EnableEvents = False Application.EnableEvents = True Around the "secondary" save event so that it doesn't run the beforesave event again? Barb ================ Why? I'll tell you why.... because I'm an idiot, that's why.<g I'm not sure where my head was when I responded to mooresk257; but, for whatever reason, it just didn't occur to me. Thanks for noting it. mooresk257 ================ Give Barb's suggestion a try... I think it should fix your problem. -- Rick (MVP - Excel) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save as crashes Excel - sometimes
Barb & Rick,
That's what I think I did with this code, and I hope nobody minds the re-post: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim Fname As String ' If F3 & F4 are empty get out of this subroutine and save with original workbook name If IsEmpty(Sheet1.Range("F3")) Then _ If IsEmpty(Sheet1.Range("F4")) Then Exit Sub ' If F3 is not empty and F4 is, use F3 as file name If Not IsEmpty(Sheet1.Range("F3")) Then _ If IsEmpty(Sheet1.Range("F4")) Then _ Fname = Sheet1.Range("F3").Value ' If F4 is not empty and F3 is, use F4 as file name If Not IsEmpty(Sheet1.Range("F4")) Then _ If IsEmpty(Sheet1.Range("F3")) Then _ Fname = Sheet1.Range("F4").Value ' If F3 and F4 are not empty use both as file name separated by a dash If Not IsEmpty(Sheet1.Range("F3")) Then _ If Not IsEmpty(Sheet1.Range("F4")) Then _ Fname = Sheet1.Range("F3").Value & "-" & Sheet1.Range("F4").Value On Error GoTo DumpSub Application.EnableEvents = False Cancel = True Application.Dialogs(xlDialogSaveAs).Show Fname DumpSub: Application.EnableEvents = True End Sub It seems to be working like it should, although as I said earlier I'm sure it could be cleaned up a bit. Thanks Barb & Rick! -- Scott "Rick Rothstein" wrote: Rick, why wouldn't you put Application.EnableEvents = False Application.EnableEvents = True Around the "secondary" save event so that it doesn't run the beforesave event again? Barb ================ Why? I'll tell you why.... because I'm an idiot, that's why.<g I'm not sure where my head was when I responded to mooresk257; but, for whatever reason, it just didn't occur to me. Thanks for noting it. mooresk257 ================ Give Barb's suggestion a try... I think it should fix your problem. -- Rick (MVP - Excel) . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel crashes on save | Excel Discussion (Misc queries) | |||
Excel crashes when trying to save. | Excel Discussion (Misc queries) | |||
Excel 2000 crashes when I save | Excel Discussion (Misc queries) | |||
Excel randomly crashes during save as | Excel Discussion (Misc queries) | |||
Save As... crashes Excel | Excel Programming |