I have a "Save As" macro. When activated a box appears asking for a file name.
If the the file name already exists a prompt asks if the existing file
should be over -
written. If I answer YES there is no problem. If I answer NO a VBS prompt
asks if I
want to END or DEBUG. Ideally if the answer is NO I would want to return to
the sheet the macro was in. Mike.

Hi Mike

Post the macro please.

Best wishes Harald

"Mike" skrev i melding
...
I have a "Save As" macro. When activated a box appears asking for a file
name.
If the the file name already exists a prompt asks if the existing file
should be over -
written. If I answer YES there is no problem. If I answer NO a VBS prompt
asks if I
want to END or DEBUG. Ideally if the answer is NO I would want to return
to
the sheet the macro was in. Mike.

There is a problem with your VBA code (the macro).

if you open up your visual basic editor, and look at the module
containing the code (or click debug when you get this message, you want
to insert the following code (but adapt it accordingly)

If(whatYouTyped="no") Then
MsgBox "File not overwritten."
Exit Sub
End If

My macro is as follows:-
'Sub SaveMe()
myFilename = InputBox("Enter filename") & ".xls"
myDir = "C:\documents and settings\mike\my documents"
ActiveWorkbook.SaveAs Filename:= _
myDir & "\" & myFilename, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

"Ninja Stevie" wrote:

There is a problem with your VBA code (the macro).

if you open up your visual basic editor, and look at the module
containing the code (or click debug when you get this message, you want
to insert the following code (but adapt it accordingly)

If(whatYouTyped="no") Then
MsgBox "File not overwritten."
Exit Sub
End If

Hope this helps.
'Sub SaveMe()
myFilename = InputBox("Enter filename") & ".xls"
myDir = "C:\documents and settings\mike\my documents"
ActiveWorkbook.SaveAs Filename:= _
myDir & "\" & myFilename, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

"Harald Staff" wrote:

Hi Mike

Post the macro please.

Best wishes Harald

"Mike" skrev i melding
...
I have a "Save As" macro. When activated a box appears asking for a file
name.
If the the file name already exists a prompt asks if the existing file
should be over -
written. If I answer YES there is no problem. If I answer NO a VBS prompt
asks if I
want to END or DEBUG. Ideally if the answer is NO I would want to return
to
the sheet the macro was in. Mike.

Sure it crashes. Your code insists on "Save" and Windows says "No save". Try

If Dir(myDir & "\" & myFilename) = "" Then
ActiveWorkbook.SaveAs Filename:= _
myDir & "\" & myFilename, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Else
MsgBox "File already exists.", vbInformation, "No can do"
End If

HTH. Best wishes Harald

"Mike" skrev i melding
...
Hope this helps.
'Sub SaveMe()
myFilename = InputBox("Enter filename") & ".xls"
myDir = "C:\documents and settings\mike\my documents"
ActiveWorkbook.SaveAs Filename:= _
myDir & "\" & myFilename, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

"Harald Staff" wrote:

Hi Mike

Post the macro please.

Best wishes Harald

"Mike" skrev i melding
...
I have a "Save As" macro. When activated a box appears asking for a
file
name.
If the the file name already exists a prompt asks if the existing file
should be over -
written. If I answer YES there is no problem. If I answer NO a VBS
prompt
asks if I
want to END or DEBUG. Ideally if the answer is NO I would want to
return
to
the sheet the macro was in. Mike.

Hi Harald,
I'm a bit thick...
Where do I put the code you suggest ? Or do I replace all my code with your
version ? I copied/pasted my code from one of your previous helps I think.
Mike

"Harald Staff" wrote:

Sure it crashes. Your code insists on "Save" and Windows says "No save". Try

If Dir(myDir & "\" & myFilename) = "" Then
ActiveWorkbook.SaveAs Filename:= _
myDir & "\" & myFilename, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Else
MsgBox "File already exists.", vbInformation, "No can do"
End If

HTH. Best wishes Harald

"Mike" skrev i melding
...
Hope this helps.
'Sub SaveMe()
myFilename = InputBox("Enter filename") & ".xls"
myDir = "C:\documents and settings\mike\my documents"
ActiveWorkbook.SaveAs Filename:= _
myDir & "\" & myFilename, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

"Harald Staff" wrote:

Hi Mike

Post the macro please.

Best wishes Harald

"Mike" skrev i melding
...
I have a "Save As" macro. When activated a box appears asking for a
file
name.
If the the file name already exists a prompt asks if the existing file
should be over -
written. If I answer YES there is no problem. If I answer NO a VBS
prompt
asks if I
want to END or DEBUG. Ideally if the answer is NO I would want to
return
to
the sheet the macro was in. Mike.

Hi Mike

It is to replace

ActiveWorkbook.SaveAs Filename:= _
myDir & "\" & myFilename, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

HTH. Best wishes Harald

"Mike" skrev i melding
...
Hi Harald,
I'm a bit thick...
Where do I put the code you suggest ? Or do I replace all my code with
your
version ? I copied/pasted my code from one of your previous helps I think.
Mike

"Harald Staff" wrote:

Sure it crashes. Your code insists on "Save" and Windows says "No save".
Try

If Dir(myDir & "\" & myFilename) = "" Then
ActiveWorkbook.SaveAs Filename:= _
myDir & "\" & myFilename, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Else
MsgBox "File already exists.", vbInformation, "No can do"
End If

HTH. Best wishes Harald

"Mike" skrev i melding
...
Hope this helps.
'Sub SaveMe()
myFilename = InputBox("Enter filename") & ".xls"
myDir = "C:\documents and settings\mike\my documents"
ActiveWorkbook.SaveAs Filename:= _
myDir & "\" & myFilename, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

"Harald Staff" wrote:

Hi Mike

Post the macro please.

Best wishes Harald

"Mike" skrev i melding
...
I have a "Save As" macro. When activated a box appears asking for
a
file
name.
If the the file name already exists a prompt asks if the existing
file
should be over -
written. If I answer YES there is no problem. If I answer NO a VBS
prompt
asks if I
want to END or DEBUG. Ideally if the answer is NO I would want to
return
to
the sheet the macro was in. Mike.

Harald i thanks a million !!
Could you possibly tell me what the code (and where to put it) is to stop
the excel
exit button offering a save option ? I don't want the user to be able to
save the workbook they are working in. Thanks.
Mike

"Mike" wrote:

Hi Harald,
I'm a bit thick...
Where do I put the code you suggest ? Or do I replace all my code with your
version ? I copied/pasted my code from one of your previous helps I think.
Mike

"Harald Staff" wrote:

Sure it crashes. Your code insists on "Save" and Windows says "No save". Try

If Dir(myDir & "\" & myFilename) = "" Then
ActiveWorkbook.SaveAs Filename:= _
myDir & "\" & myFilename, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Else
MsgBox "File already exists.", vbInformation, "No can do"
End If

HTH. Best wishes Harald

"Mike" skrev i melding
...
Hope this helps.
'Sub SaveMe()
myFilename = InputBox("Enter filename") & ".xls"
myDir = "C:\documents and settings\mike\my documents"
ActiveWorkbook.SaveAs Filename:= _
myDir & "\" & myFilename, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

"Harald Staff" wrote:

Hi Mike

Post the macro please.

Best wishes Harald

"Mike" skrev i melding
...
I have a "Save As" macro. When activated a box appears asking for a
file
name.
If the the file name already exists a prompt asks if the existing file
should be over -
written. If I answer YES there is no problem. If I answer NO a VBS
prompt
asks if I
want to END or DEBUG. Ideally if the answer is NO I would want to
return
to
the sheet the macro was in. Mike.