VBScript to prompt Save on Cancel click

[paperclip]

Hello everyone!

I am dabbling in some VBScripting and created a simple form. What
want to do is add an extra couple of code for the CANCEL button so tha
when its clicked it will prompt the user to save the document - whethe
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 Su
-------------------


Any help on this would be much appreciated.

Regards

 

[Bob Phillips]

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]

Private Sub cmdCancel_Click()

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

Unload Me

End Sub

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!

 

[Guest]

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.

 

[Guest]

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.

 

[Bob Phillips]

Look at GetSaveAsFilename in help.

--
HTH

Bob Phillips

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

 

[paperclip]

Thanks for the help so far guys...

As Tom pointed out Peter's code has an error in it insomuch as once
filename is chosen it brings up another error box. Is there anyway t
correct this?

Alternatively I was thinking if it was possible to code the cance
button to save the file with a pre-determined filename to the user
desktop automatically or alternatively their C: drive.

Is there anyway this can be done?

Regards

 

[Guest]

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]

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

Thanks Peter - seems to work very well!