VBScript to prompt Save on Cancel click

Private Sub cmdCancel_Click()

If MsgBox ("Save document?,vbYesNo) =vbYes Then
ActiveWorkbooks.Save
End If

Unload Me

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"paperclip" <[email protected]> wrote
in message news:[email protected]...
>
> Hello everyone!
>
> I am dabbling in some VBScripting and created a simple form. What I
> want to do is add an extra couple of code for the CANCEL button so that
> when its clicked it will prompt the user to save the document - whether
> they have changed it or not (cos I figure its easier that way!).
>
> This is all the code i have for the cancel button:
>
> Code:
> --------------------
> Private Sub cmdCancel_Click()
>
> Unload Me
>
> End Sub
> --------------------
>
>
> Any help on this would be much appreciated.
>
> Regards.
>
>
> --
> paperclip
> ------------------------------------------------------------------------
> paperclip's Profile:
http://www.excelforum.com/member.php...o&userid=32219
> View this thread: http://www.excelforum.com/showthread...hreadid=566739
>

First remark
Your code and Bob's are doing exactly the same
Still, I like Bob's more, while the unload action is always to be completed,
so there is no reason to nest it between your if - end if clause.
Apart from that, here some code in order to give your file the costumer's
wanted loaction:

Private Sub cmdCancel_Click()
Dim str_FullPath As String
On Error Resume Next
Do
Err.Clear
str_FullPath = Application.GetSaveAsFilename
If (str_FullPath) Then
ActiveWorkbook.SaveAs str_FullPath
End If
If Err.Number <> 0 Then
MsgBox "File has not been saved. Try again", vbExclamation,
"Error Message'"
End If
Loop Until Err.Number = 0

Unload Me
End Sub

The user only has to click the cancel button if he doesn't want anything to
be saved?
As you can see, I put some erre error handling, because if some (network's
or name's) failure it isalways possible that the system didn't succeed at
saving the file.

"paperclip" wrote:

>
> Bob Phillips Wrote:
> > Private Sub cmdCancel_Click()
> >
> > If MsgBox ("Save document?,vbYesNo) =vbYes Then
> > ActiveWorkbooks.Save
> > End If
> >
> > Unload Me
> >
> > End Sub
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
>
> Hey Bob!
>
> Thanks that solved half of my problem, althouth there some major typos
> in your post that I had to figure out on my own to get it to work -
> this is what I changed it to:
>
>
> Code:
> --------------------
> Private Sub cmdCancel_Click()
>
> If MsgBox("Save document?", vbYesNo) = vbYes Then
>
> ActiveWorkbook.Save
>
> Unload Me
>
> Else
>
> Unload Me
>
> End If
>
> End Sub
> --------------------
>
>
> That works fine, but how can I get a dialog box to make them choose an
> alternate save location aside from just saving where ever the file
> already is. I want the user to be able to choose a location that would
> be more convient for them.
>
> Any further ideas on this would be great!
>
>
> --
> paperclip
> ------------------------------------------------------------------------
> paperclip's Profile: http://www.excelforum.com/member.php...o&userid=32219
> View this thread: http://www.excelforum.com/showthread...hreadid=566739
>
>

Peter: Just a heads up.
since you dimmed str_Fullpath as String,

if (str_FullPath) then

will produce an error when a filename is actually selected.

---------------

From reading the original question, I would go with Bob's interpretation
that the existing file needs to be saved.



--
Regards,
Tom Ogilvy




"Peter Perception" wrote:

> First remark
> Your code and Bob's are doing exactly the same
> Still, I like Bob's more, while the unload action is always to be completed,
> so there is no reason to nest it between your if - end if clause.
> Apart from that, here some code in order to give your file the costumer's
> wanted loaction:
>
> Private Sub cmdCancel_Click()
> Dim str_FullPath As String
> On Error Resume Next
> Do
> Err.Clear
> str_FullPath = Application.GetSaveAsFilename
> If (str_FullPath) Then
> ActiveWorkbook.SaveAs str_FullPath
> End If
> If Err.Number <> 0 Then
> MsgBox "File has not been saved. Try again", vbExclamation,
> "Error Message'"
> End If
> Loop Until Err.Number = 0
>
> Unload Me
> End Sub
>
> The user only has to click the cancel button if he doesn't want anything to
> be saved?
> As you can see, I put some erre error handling, because if some (network's
> or name's) failure it isalways possible that the system didn't succeed at
> saving the file.
>
> "paperclip" wrote:
>
> >
> > Bob Phillips Wrote:
> > > Private Sub cmdCancel_Click()
> > >
> > > If MsgBox ("Save document?,vbYesNo) =vbYes Then
> > > ActiveWorkbooks.Save
> > > End If
> > >
> > > Unload Me
> > >
> > > End Sub
> > >
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> >
> > Hey Bob!
> >
> > Thanks that solved half of my problem, althouth there some major typos
> > in your post that I had to figure out on my own to get it to work -
> > this is what I changed it to:
> >
> >
> > Code:
> > --------------------
> > Private Sub cmdCancel_Click()
> >
> > If MsgBox("Save document?", vbYesNo) = vbYes Then
> >
> > ActiveWorkbook.Save
> >
> > Unload Me
> >
> > Else
> >
> > Unload Me
> >
> > End If
> >
> > End Sub
> > --------------------
> >
> >
> > That works fine, but how can I get a dialog box to make them choose an
> > alternate save location aside from just saving where ever the file
> > already is. I want the user to be able to choose a location that would
> > be more convient for them.
> >
> > Any further ideas on this would be great!
> >
> >
> > --
> > paperclip
> > ------------------------------------------------------------------------
> > paperclip's Profile: http://www.excelforum.com/member.php...o&userid=32219
> > View this thread: http://www.excelforum.com/showthread...hreadid=566739
> >
> >

Look at GetSaveAsFilename in help.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"paperclip" <[email protected]> wrote
in message news:[email protected]...
>
> Bob Phillips Wrote:
> > Private Sub cmdCancel_Click()
> >
> > If MsgBox ("Save document?,vbYesNo) =vbYes Then
> > ActiveWorkbooks.Save
> > End If
> >
> > Unload Me
> >
> > End Sub
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
>
> Hey Bob!
>
> Thanks that solved half of my problem, althouth there some major typos
> in your post that I had to figure out on my own to get it to work -
> this is what I changed it to:
>
>
> Code:
> --------------------
> Private Sub cmdCancel_Click()
>
> If MsgBox("Save document?", vbYesNo) = vbYes Then
>
> ActiveWorkbook.Save
>
> Unload Me
>
> Else
>
> Unload Me
>
> End If
>
> End Sub
> --------------------
>
>
> That works fine, but how can I get a dialog box to make them choose an
> alternate save location aside from just saving where ever the file
> already is. I want the user to be able to choose a location that would
> be more convient for them.
>
> Any further ideas on this would be great!
>
>
> --
> paperclip
> ------------------------------------------------------------------------
> paperclip's Profile:
http://www.excelforum.com/member.php...o&userid=32219
> View this thread: http://www.excelforum.com/showthread...hreadid=566739
>

So Paperclip, here is the new and ,I hope, right, code.
As one allready told, the Help fonction helps, but i does not tell us how te
declare our variable.
The var_FullPath must be a variant instead and cannot be a string.
The string value output depends on the Excel's language version.
So clicking on the cancel button results in my system in a string 'onwaar'
which is Dutch for 'false'. Changing the string into a variant, makes the
whole thing independent from the language version.
If one clicks the cancel button, the variant var_FullPath becomes a boolean.
.. If one does otherwise, i.e. if one fills in the file-name textbox, the
output is a string. The textbox does not accept empty strings or string
containing spaces only, so no code has to be written for that.
Let's finish the whole thing with a Beatles' quote: and in the end the love
you take is equal to the love you make. Cheers!
Sub cmdCancel_Click()
Dim var_FullPath As Variant
On Error Resume Next
Do
Err.Clear
var_FullPath = Application.GetSaveAsFilename(initialfilename:="", _
fileFilter:="Excel Workbook (*.xls), *.xls")

If var_FullPath <> False Then
ActiveWorkbook.SaveAs var_FullPath
If Err.Number <> 0 Then
MsgBox "File has not been saved. Try again" & Chr(13) &
Err.Description _
, vbExclamation, Err.Number & "Error Message'"
End If
End If
Loop Until Err.Number = 0
Unload Me
End Sub

"paperclip" wrote:

>
> Thanks for the help so far guys...
>
> As Tom pointed out Peter's code has an error in it insomuch as once a
> filename is chosen it brings up another error box. Is there anyway to
> correct this?
>
> Alternatively I was thinking if it was possible to code the cancel
> button to save the file with a pre-determined filename to the users
> desktop automatically or alternatively their C: drive.
>
> Is there anyway this can be done?
>
> Regards.
>
>
> --
> paperclip
> ------------------------------------------------------------------------
> paperclip's Profile: http://www.excelforum.com/member.php...o&userid=32219
> View this thread: http://www.excelforum.com/showthread...hreadid=566739
>
>

Wow educational and entertaining (dare I say edutainment!) - I learn about Excel, VBScript & the Beatles!

Thanks Peter - seems to work very well!

Originally Posted by Peter Perception

So Paperclip, here is the new and ,I hope, right, code.
As one allready told, the Help fonction helps, but i does not tell us how te
declare our variable.
The var_FullPath must be a variant instead and cannot be a string.
The string value output depends on the Excel's language version.
So clicking on the cancel button results in my system in a string 'onwaar'
which is Dutch for 'false'. Changing the string into a variant, makes the
whole thing independent from the language version.
If one clicks the cancel button, the variant var_FullPath becomes a boolean.
.. If one does otherwise, i.e. if one fills in the file-name textbox, the
output is a string. The textbox does not accept empty strings or string
containing spaces only, so no code has to be written for that.
Let's finish the whole thing with a Beatles' quote: and in the end the love
you take is equal to the love you make. Cheers!
Sub cmdCancel_Click()
Dim var_FullPath As Variant
On Error Resume Next
Do
Err.Clear
var_FullPath = Application.GetSaveAsFilename(initialfilename:="", _
fileFilter:="Excel Workbook (*.xls), *.xls")

If var_FullPath <> False Then
ActiveWorkbook.SaveAs var_FullPath
If Err.Number <> 0 Then
MsgBox "File has not been saved. Try again" & Chr(13) &
Err.Description _
, vbExclamation, Err.Number & "Error Message'"
End If
End If
Loop Until Err.Number = 0
Unload Me
End Sub

"paperclip" wrote:

>
> Thanks for the help so far guys...
>
> As Tom pointed out Peter's code has an error in it insomuch as once a
> filename is chosen it brings up another error box. Is there anyway to
> correct this?
>
> Alternatively I was thinking if it was possible to code the cancel
> button to save the file with a pre-determined filename to the users
> desktop automatically or alternatively their C: drive.
>
> Is there anyway this can be done?
>
> Regards.
>
>
> --
> paperclip
> ------------------------------------------------------------------------
> paperclip's Profile: http://www.excelforum.com/member.php...o&userid=32219
> View this thread: http://www.excelforum.com/showthread...hreadid=566739
>
>