Error trap only works once

[Zarch]

Hello

I have some code to save a workbook for the first time using the SaveAs
dialog.

I need to trap if the same filename is currently being used by aother Excel
session -to prevent VBA from getting upset.

Essentially the code is:

Dim FD As FileDialog ' Variable for dialog object

On Error GoTo ErrorHandler

'Create file dialog object
Set FD = Application.FileDialog(MsoFileDialogType.msoFileDialogSaveAs)

'Set the default file name (& path)
FD.InitialFileName = Application.DefaultFilePath & "\Planning Spreadsheet"

SaveLoop: 'Point to restart save routine if error occurred
'Display the save as dialog and save file if cancel not selected
If FD.Show <> 0 Then FD.Execute
On Error GoTo 0
Exit Sub

ErrorHandler:
' Traps file save error e.g. if file currently in use by another Excel session
MsgBox "Please change the file name", vbExclamation, "File Currently In
Use"
On Error GoTo ErrorHandler
GoTo SaveLoop ' Return to Save As dialog
End Sub

The error trap works fine the first time round, however if I try to
overwrite the file a second time I get a run time error 2147467259 (80004005)
Cannot Access Planning Spreadsheet.xls

I have also tried Err.Clear.

How can I reset the error handler? If I hide and reshow the SaveAs dialog,
would this clear it? If so, how do I hide the built in dialog as it doesn't
seem to have a Hide method?

Regards and thanks,

Z

 

[Jim Rech]

ErrorHandler:
Every error handler must have a Resume unless you are directly exiting the
sub.


--
Jim
| Hello
|
| I have some code to save a workbook for the first time using the SaveAs
| dialog.
|
| I need to trap if the same filename is currently being used by aother
Excel
| session -to prevent VBA from getting upset.
|
| Essentially the code is:
|
| Dim FD As FileDialog ' Variable for dialog object
|
| On Error GoTo ErrorHandler
|
| 'Create file dialog object
| Set FD = Application.FileDialog(MsoFileDialogType.msoFileDialogSaveAs)
|
| 'Set the default file name (& path)
| FD.InitialFileName = Application.DefaultFilePath & "\Planning
Spreadsheet"
|
| SaveLoop: 'Point to restart save routine if error occurred
| 'Display the save as dialog and save file if cancel not selected
| If FD.Show <> 0 Then FD.Execute
| On Error GoTo 0
| Exit Sub
|
| ErrorHandler:
| ' Traps file save error e.g. if file currently in use by another Excel
session
| MsgBox "Please change the file name", vbExclamation, "File Currently In
| Use"
| On Error GoTo ErrorHandler
| GoTo SaveLoop ' Return to Save As dialog
| End Sub
|
| The error trap works fine the first time round, however if I try to
| overwrite the file a second time I get a run time error 2147467259
(80004005)
| Cannot Access Planning Spreadsheet.xls
|
| I have also tried Err.Clear.
|
| How can I reset the error handler? If I hide and reshow the SaveAs
dialog,
| would this clear it? If so, how do I hide the built in dialog as it
doesn't
| seem to have a Hide method?
|
| Regards and thanks,
|
| Z

 

[Zarch]

That's the reason! - and the solution.

Many thanks Jim.

Regards,

Z