Error 400

[Desert Piranha]

Hi all,

I have this Save As code linked to a Option Button.
Everything works as i want it to, except when there is already been a file saved with the current date.
Then i get a message box asking if i want to overwrite the file.
I DO want that.

BUT if i answer "No" or "Cancel" i get a Visual Basic "Error 400" thingy.
I DON'T want that error message.

If i put like "Application.DisplayAlerts = False" in then it also blocks the "Do i want to overwrite dialog",
which i want to keep.

Please Login or Register  to view this content.

Any direction on this would shurly be appreciated.

[Bob Phillips]

Try this

On Error Resume Next
ActiveWorkbook.SaveAs _
Filename:=ActiveWorkbook.Path & _
"myfile " & Format(Date, "mm-dd-yyyy") & ".xls"
If Err.Number = 1004 Then
MsgBox "cancelled"
End If
On Error GoTo 0


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Desert Piranha"
<[email protected]> wrote in
message news:[email protected]...
>
> Hi all,
>
> I have this Save As code linked to a Option Button.
> Everything works as i want it to, except when there is already been a
> file saved with the current date.
> Then i get a message box asking if i want to overwrite the file.
> I DO want that.
>
> BUT if i answer "No" or "Cancel" i get a Visual Basic "Error 400"
> thingy.
> I DON'T want that error message.
>
> If i put like "Application.DisplayAlerts = False" in then it also
> blocks the "Do i want to overwrite dialog",
> which i want to keep.
>
>
> Code:
> --------------------
> Private Sub SaveAsTo()
> Application.ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" &
_
> "myfile " & Format(Date, "mm-dd-yyyy") & ".xls"
> End Sub
> --------------------
> Any direction on this would shurly be appreciated.
>
>
> --
> Desert Piranha
>
>
> ------------------------------------------------------------------------
> Desert Piranha's Profile:
http://www.excelforum.com/member.php...o&userid=28934
> View this thread: http://www.excelforum.com/showthread...hreadid=499135
>

[Desert Piranha]

Hi Bob,

Thx for this. The message box is a nice touch, i like that.
Sooo how do you know that the error number is 1004 ??

Thx again
Dave

Try this

On Error Resume Next
ActiveWorkbook.SaveAs _
Filename:=ActiveWorkbook.Path & _
"myfile " & Format(Date, "mm-dd-yyyy") & ".xls"
If Err.Number = 1004 Then
MsgBox "cancelled"
End If
On Error GoTo 0


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Desert Piranha"
<[email protected]> wrote in
message news:[email protected]...
>
> Hi all,
>
> I have this Save As code linked to a Option Button.
> Everything works as i want it to, except when there is already been a
> file saved with the current date.
> Then i get a message box asking if i want to overwrite the file.
> I DO want that.
>
> BUT if i answer "No" or "Cancel" i get a Visual Basic "Error 400"
> thingy.
> I DON'T want that error message.
>
> If i put like "Application.DisplayAlerts = False" in then it also
> blocks the "Do i want to overwrite dialog",
> which i want to keep.
>
>
> Code:
> --------------------
> Private Sub SaveAsTo()
> Application.ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" &
_
> "myfile " & Format(Date, "mm-dd-yyyy") & ".xls"
> End Sub
> --------------------
> Any direction on this would shurly be appreciated.
>
>
> --
> Desert Piranha
>
>
> ------------------------------------------------------------------------
> Desert Piranha's Profile:
http://www.excelforum.com/member.php...o&userid=28934
> View this thread: http://www.excelforum.com/showthread...hreadid=499135
>

[Bob Phillips]

I tried it and found out.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Desert Piranha"
<[email protected]> wrote in
message news:[email protected]...
>
> Hi Bob,
>
> Thx for this. The message box is a nice touch, i like that.
> Sooo how do you know that the error number is 1004 ??
>
> Thx again
> Dave
> Bob Phillips Wrote:
> > Try this
> >
> > On Error Resume Next
> > ActiveWorkbook.SaveAs _
> > Filename:=ActiveWorkbook.Path & _
> > "myfile " & Format(Date, "mm-dd-yyyy") & ".xls"
> > If Err.Number = 1004 Then
> > MsgBox "cancelled"
> > End If
> > On Error GoTo 0
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Desert Piranha"
> > <[email protected]> wrote in
> > message
> > news:[email protected]...
> > >
> > > Hi all,
> > >
> > > I have this Save As code linked to a Option Button.
> > > Everything works as i want it to, except when there is already been
> > a
> > > file saved with the current date.
> > > Then i get a message box asking if i want to overwrite the file.
> > > I DO want that.
> > >
> > > BUT if i answer "No" or "Cancel" i get a Visual Basic "Error 400"
> > > thingy.
> > > I DON'T want that error message.
> > >
> > > If i put like "Application.DisplayAlerts = False" in then it also
> > > blocks the "Do i want to overwrite dialog",
> > > which i want to keep.
> > >
> > >
> > > Code:
> > > --------------------
> > > Private Sub SaveAsTo()
> > > Application.ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path &
> > "\" &
> > _
> > > "myfile " & Format(Date, "mm-dd-yyyy") & ".xls"
> > > End Sub
> > > --------------------
> > > Any direction on this would shurly be appreciated.
> > >
> > >
> > > --
> > > Desert Piranha
> > >
> > >
> > >
> > ------------------------------------------------------------------------
> > > Desert Piranha's Profile:
> > http://www.excelforum.com/member.php...o&userid=28934
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=499135
> > >
>
>
> --
> Desert Piranha
>
>
> ------------------------------------------------------------------------
> Desert Piranha's Profile:
http://www.excelforum.com/member.php...o&userid=28934
> View this thread: http://www.excelforum.com/showthread...hreadid=499135
>

[Desert Piranha]

Hi Bob,
Oh ok, i see. If you run the code from the VBE it then shows the Error number.
Cool.
Thx Bob for your help.
Dave

I tried it and found out.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Desert Piranha"
<[email protected]> wrote in
message news:[email protected]...
>
> Hi Bob,
>
> Thx for this. The message box is a nice touch, i like that.
> Sooo how do you know that the error number is 1004 ??
>
> Thx again
> Dave
> Bob Phillips Wrote:
> > Try this
> >
> > On Error Resume Next
> > ActiveWorkbook.SaveAs _
> > Filename:=ActiveWorkbook.Path & _
> > "myfile " & Format(Date, "mm-dd-yyyy") & ".xls"
> > If Err.Number = 1004 Then
> > MsgBox "cancelled"
> > End If
> > On Error GoTo 0
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Desert Piranha"
> > <[email protected]> wrote in
> > message
> > news:[email protected]...
> > >
> > > Hi all,
> > >
> > > I have this Save As code linked to a Option Button.
> > > Everything works as i want it to, except when there is already been
> > a
> > > file saved with the current date.
> > > Then i get a message box asking if i want to overwrite the file.
> > > I DO want that.
> > >
> > > BUT if i answer "No" or "Cancel" i get a Visual Basic "Error 400"
> > > thingy.
> > > I DON'T want that error message.
> > >
> > > If i put like "Application.DisplayAlerts = False" in then it also
> > > blocks the "Do i want to overwrite dialog",
> > > which i want to keep.
> > >
> > >
> > > Code:
> > > --------------------
> > > Private Sub SaveAsTo()
> > > Application.ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path &
> > "\" &
> > _
> > > "myfile " & Format(Date, "mm-dd-yyyy") & ".xls"
> > > End Sub
> > > --------------------
> > > Any direction on this would shurly be appreciated.
> > >
> > >
> > > --
> > > Desert Piranha
> > >
> > >
> > >
> > ------------------------------------------------------------------------
> > > Desert Piranha's Profile:
> > http://www.excelforum.com/member.php...o&userid=28934
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=499135
> > >
>
>
> --
> Desert Piranha
>
>
> ------------------------------------------------------------------------
> Desert Piranha's Profile:
http://www.excelforum.com/member.php...o&userid=28934
> View this thread: http://www.excelforum.com/showthread...hreadid=499135
>

[Bob Phillips]

My pleasure Dave.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Desert Piranha"
<[email protected]> wrote in
message news:[email protected]...
>
> Hi Bob,
> Oh ok, i see. If you run the code from the VBE it then shows the Error
> number.
> Cool.
> Thx Bob for your help.
> Dave
> Bob Phillips Wrote:
> > I tried it and found out.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Desert Piranha"
> > <[email protected]> wrote in
> > message
> > news:[email protected]...
> > >
> > > Hi Bob,
> > >
> > > Thx for this. The message box is a nice touch, i like that.
> > > Sooo how do you know that the error number is 1004 ??
> > >
> > > Thx again
> > > Dave
> > > Bob Phillips Wrote:
> > > > Try this
> > > >
> > > > On Error Resume Next
> > > > ActiveWorkbook.SaveAs _
> > > > Filename:=ActiveWorkbook.Path & _
> > > > "myfile " & Format(Date, "mm-dd-yyyy") & ".xls"
> > > > If Err.Number = 1004 Then
> > > > MsgBox "cancelled"
> > > > End If
> > > > On Error GoTo 0
> > > >
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove nothere from email address if mailing direct)
> > > >
> > > > "Desert Piranha"
> > > > <[email protected]> wrote
> > in
> > > > message
> > > >
> > news:[email protected]...
> > > > >
> > > > > Hi all,
> > > > >
> > > > > I have this Save As code linked to a Option Button.
> > > > > Everything works as i want it to, except when there is already
> > been
> > > > a
> > > > > file saved with the current date.
> > > > > Then i get a message box asking if i want to overwrite the file.
> > > > > I DO want that.
> > > > >
> > > > > BUT if i answer "No" or "Cancel" i get a Visual Basic "Error
> > 400"
> > > > > thingy.
> > > > > I DON'T want that error message.
> > > > >
> > > > > If i put like "Application.DisplayAlerts = False" in then it
> > also
> > > > > blocks the "Do i want to overwrite dialog",
> > > > > which i want to keep.
> > > > >
> > > > >
> > > > > Code:
> > > > > --------------------
> > > > > Private Sub SaveAsTo()
> > > > > Application.ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path
> > &
> > > > "\" &
> > > > _
> > > > > "myfile " & Format(Date, "mm-dd-yyyy") & ".xls"
> > > > > End Sub
> > > > > --------------------
> > > > > Any direction on this would shurly be appreciated.
> > > > >
> > > > >
> > > > > --
> > > > > Desert Piranha
> > > > >
> > > > >
> > > > >
> > > >
> > ------------------------------------------------------------------------
> > > > > Desert Piranha's Profile:
> > > > http://www.excelforum.com/member.php...o&userid=28934
> > > > > View this thread:
> > > > http://www.excelforum.com/showthread...hreadid=499135
> > > > >
> > >
> > >
> > > --
> > > Desert Piranha
> > >
> > >
> > >
> > ------------------------------------------------------------------------
> > > Desert Piranha's Profile:
> > http://www.excelforum.com/member.php...o&userid=28934
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=499135
> > >
>
>
> --
> Desert Piranha
>
>
> ------------------------------------------------------------------------
> Desert Piranha's Profile:
http://www.excelforum.com/member.php...o&userid=28934
> View this thread: http://www.excelforum.com/showthread...hreadid=499135
>