Workbook_BeforeSave() 1004 Error

[joel.brewster]

All,

I know that this has been asked a number of times in other posts, but I
have yet to put it together in the context that I need. I am using
Excel97 on XP. I would like to do a SaveAs any time the user attempts
any sort of Save. My plan was to use the Workbook_BeforeSave event to
prompt the user to enter another filename each time they try to save
and process accordingly. My code is listed below. Any help would be
appreciated.

TIA,

Joel Brewster

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

On Error GoTo ErrHandler

Call SaveAsDBF
Cancel = True

ErrHandler:
If Err.Number <> 0 Then
Cancel = True
End If

End Sub

Public Sub SaveAsDBF()

Dim strFileName As String

On Error GoTo ErrHandler

strFileName = Application.GetSaveAsFilename("*.dbf", "dBase
Files (*.dbf),*.dbf", 7, "Save DBF")
If UCase(strFileName) <> "FALSE" Then
Application.EnableEvents = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=strFileName,
FileFormat:=xlDBF4
Application.DisplayAlerts = True
Application.EnableEvents = True
End If

ErrHandler:
If Err.Number <> 0 Then
MsgBox "Error in SaveAsDBF: " & Err.Number & " - " &
Err.Description
Err.Clear
Application.DisplayAlerts = True
Application.EnableEvents = True
End If

End Sub

 

[Bob Phillips]

This doesn't prompt for a new filename, but gets the user to browse for a
filename (which they can then input).

What doesn't happen that you want? Did you put the code in the Thisworkbook
code module.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[joel.brewster]

Thanks for your response Bob. The code is located in the ThisWorkbook
module in Sub Workbook_BeforeSave and does prompt for the new filename
and does not prompt for an overwrite. However, whenever I compile it
and run without debugging an error is raised indicating a 1004 - Method
SaveAs of object '_Workbook' failed. Everything that I've read
indicates that the above code should work but it does not for me. Any
ideas would be appreciated.

Thanks, Joel

 

[joel.brewster]

I forgot to mention that my workbook is password protected. I read
somewhere that the protection feature might cause strange behavior so I
disable it and everything works well. It would be nice if I could
protect the code from modification though if anyone has ideas.

TIA

Joel

 

[Bob Phillips]

Could you not turn protection off in the code, do your stuff, then
re-protect?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[joel.brewster]

Goof idea but I already tried that and it still received the error.