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

--
papercli
-----------------------------------------------------------------------
paperclip's Profile: http://www.excelforum.com/member.php...fo&userid=3221
View this thread: http://www.excelforum.com/showthread.php?threadid=56673

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" wrote
in message ...

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

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

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" wrote
in message ...

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

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

--
papercli
-----------------------------------------------------------------------
paperclip's Profile: http://www.excelforum.com/member.php...fo&userid=3221
View this thread: http://www.excelforum.com/showthread.php?threadid=56673

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!

Peter Perception Wrote:
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




--
paperclip
------------------------------------------------------------------------
paperclip's Profile: http://www.excelforum.com/member.php...o&userid=32219
View this thread: http://www.excelforum.com/showthread...hreadid=566739