I have a workbook that is open to several users. Data entry is password controlled and each user has different permissions. I have a password system that unlocks various (differing) areas of the spreadsheet for each user.

I use Workbook_BeforeSave to intercept and cancel the excel save, replacing it with my own save routine which locks the user areas before the save, then saves the file, then restores the workbook to its previous condition so the user can continue working.

I use an IF statement to test whether the user used FileSave or FileSaveAs and THEN present the file dialogue box for FileSaveAs if required.

I thought my code was pretty neat and it works perfectly on my computer BUT () in testing on another computer the FileSaveAs branch shows the dialog box but then fails to save. Instead, I get a run time error:

-2147467259 (80004005)
Method Execute of FileDialog failed

Same version of excel on both machines - 2003 SP2

The code is:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

...various lines that lock the cells...

If SaveAsUI = True Then
    With Application.FileDialog(msoFileDialogSaveAs)
        If .Show Then .Execute
    End With
Else
    ThisWorkbook.Save
End If
Cancel = True

...various lines that identify the user and unlock the relevant
cells for data entry...

I assume I could get the path and filename from the dialog box and then wite code to use .SaveAs but using .Execute on the dialog box looked like a more elegant solution.

Any ideas?

Alistair

Stranger and stranger.
Same PC, same file- now the unmodified code works.
So it's a daisy - some days it works and some days it doesn't.

Any ideas?

I've heard of issues with Application.FileDialog(msoFileDialogSaveAs)
 before. Have you tried using Application.GetSaveAsFileName instead? This will involve you extracting the requested file save name and then saving this new filename and therefore less elegant but more robust.

Maccas