software

What I am struggling with is being able to write a macro that performs the
following;

will save a workbook to a specified folder, but, adds 1) a date stamp and 2)
a piece of text from the 1st sheet of the workbook to the saved file name.

Once the macro has run and has saved the workbook I would like a message box
to say quot; you have now saved your filequot; and to have the choice to close the
workbook?

any thing would be appreciated,

many thanks

David

--
Message posted via OfficeKB.com
www.officekb.com/Uwe/Forums.a...excel/200601/1

Maybe something like this:

Option Explicit
Sub testme()

Dim myFileName As String
Dim resp As Long

With ActiveWorkbook
myFileName = quot;C:\my documents\excel\quot; _
amp; .Worksheets(quot;sheet1quot;).Range(quot;a1quot;).Value _
amp; quot;_quot; amp; Format(Now, quot;yyyymmdd_hhmmssquot;) amp; quot;.xlsquot;

.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
resp = MsgBox(prompt:=quot;Wanna close this workbook?quot;, _
Buttons:=vbYesNo)

If resp = vbYes Then
.Close savechanges:=False
End If
End With

End Sub

You may want add some validity checks for what's in that cell.
quot;Ozzie via OfficeKB.comquot; wrote:
gt;
gt; What I am struggling with is being able to write a macro that performs the
gt; following;
gt;
gt; will save a workbook to a specified folder, but, adds 1) a date stamp and 2)
gt; a piece of text from the 1st sheet of the workbook to the saved file name.
gt;
gt; Once the macro has run and has saved the workbook I would like a message box
gt; to say quot; you have now saved your filequot; and to have the choice to close the
gt; workbook?
gt;
gt; any thing would be appreciated,
gt;
gt; many thanks
gt;
gt; David
gt;
gt; --
gt; Message posted via OfficeKB.com
gt; www.officekb.com/Uwe/Forums.a...excel/200601/1

--

Dave Peterson

Dave, many thanks it works a treat

Dave Peterson wrote:
gt;Maybe something like this:
gt;
gt;Option Explicit
gt;Sub testme()
gt;
gt; Dim myFileName As String
gt; Dim resp As Long
gt;
gt; With ActiveWorkbook
gt; myFileName = quot;C:\my documents\excel\quot; _
gt; amp; .Worksheets(quot;sheet1quot;).Range(quot;a1quot;).Value _
gt; amp; quot;_quot; amp; Format(Now, quot;yyyymmdd_hhmmssquot;) amp; quot;.xlsquot;
gt;
gt; .SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
gt; resp = MsgBox(prompt:=quot;Wanna close this workbook?quot;, _
gt; Buttons:=vbYesNo)
gt;
gt; If resp = vbYes Then
gt; .Close savechanges:=False
gt; End If
gt; End With
gt;
gt;End Sub
gt;
gt;You may want add some validity checks for what's in that cell.
gt;
gt;gt; What I am struggling with is being able to write a macro that performs the
gt;gt; following;
gt;[quoted text clipped - 11 lines]
gt;gt;
gt;gt; David
gt;

--
Message posted via www.officekb.com

Dave,

Could I ask one more question, the code below works fine and have tailored it
into my vba statement, however the last thing i need to do, which I haven't
the faintest idea how, is to;

when the macro is run (in excel) from a button, I need the text on the button
to change to say quot;you have now saved and sent your dataquot;

any idea's??

thanks again for all your help, cheersDave Peterson wrote:
gt;Maybe something like this:
gt;
gt;Option Explicit
gt;Sub testme()
gt;
gt; Dim myFileName As String
gt; Dim resp As Long
gt;
gt; With ActiveWorkbook
gt; myFileName = quot;C:\my documents\excel\quot; _
gt; amp; .Worksheets(quot;sheet1quot;).Range(quot;a1quot;).Value _
gt; amp; quot;_quot; amp; Format(Now, quot;yyyymmdd_hhmmssquot;) amp; quot;.xlsquot;
gt;
gt; .SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
gt; resp = MsgBox(prompt:=quot;Wanna close this workbook?quot;, _
gt; Buttons:=vbYesNo)
gt;
gt; If resp = vbYes Then
gt; .Close savechanges:=False
gt; End If
gt; End With
gt;
gt;End Sub
gt;
gt;You may want add some validity checks for what's in that cell.
gt;
gt;gt; What I am struggling with is being able to write a macro that performs the
gt;gt; following;
gt;[quoted text clipped - 11 lines]
gt;gt;
gt;gt; David
gt;

--
Message posted via www.officekb.com

I would show the Forms toolbar and use the button from that toolbar--not a
commandbutton from the Control toolbox toolbar.

Then assign the macro to that button.

But I think you'll want to add a bit.

Option Explicit
Sub testme()

Dim myFileName As String
Dim resp As Long
Dim myBTN As Button

With ActiveWorkbook
myFileName = quot;C:\my documents\excel\quot; _
amp; .Worksheets(quot;sheet1quot;).Range(quot;a1quot;).Value _
amp; quot;_quot; amp; Format(Now, quot;yyyymmdd_hhmmssquot;) amp; quot;.xlsquot;

.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
resp = MsgBox(prompt:=quot;Wanna close this workbook?quot;, _
Buttons:=vbYesNo)

If resp = vbYes Then
.Close savechanges:=False
End If

Set myBTN = ActiveSheet.Buttons(Application.Caller)
myBTN.Caption = quot;You have now saved and sent your dataquot;
End With

End Sub

Sub auto_open()
Worksheets(quot;sheet1quot;).Buttons(quot;button 1quot;).Caption _
= quot;Click me to save and send your dataquot;
End Sub

The auto_open routine will run when the workbook opens. Change the worksheet
name and button name to what you need.

quot;Ozzie via OfficeKB.comquot; wrote:
gt;
gt; Dave,
gt;
gt; Could I ask one more question, the code below works fine and have tailored it
gt; into my vba statement, however the last thing i need to do, which I haven't
gt; the faintest idea how, is to;
gt;
gt; when the macro is run (in excel) from a button, I need the text on the button
gt; to change to say quot;you have now saved and sent your dataquot;
gt;
gt; any idea's??
gt;
gt; thanks again for all your help, cheers
gt;
gt; Dave Peterson wrote:
gt; gt;Maybe something like this:
gt; gt;
gt; gt;Option Explicit
gt; gt;Sub testme()
gt; gt;
gt; gt; Dim myFileName As String
gt; gt; Dim resp As Long
gt; gt;
gt; gt; With ActiveWorkbook
gt; gt; myFileName = quot;C:\my documents\excel\quot; _
gt; gt; amp; .Worksheets(quot;sheet1quot;).Range(quot;a1quot;).Value _
gt; gt; amp; quot;_quot; amp; Format(Now, quot;yyyymmdd_hhmmssquot;) amp; quot;.xlsquot;
gt; gt;
gt; gt; .SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
gt; gt; resp = MsgBox(prompt:=quot;Wanna close this workbook?quot;, _
gt; gt; Buttons:=vbYesNo)
gt; gt;
gt; gt; If resp = vbYes Then
gt; gt; .Close savechanges:=False
gt; gt; End If
gt; gt; End With
gt; gt;
gt; gt;End Sub
gt; gt;
gt; gt;You may want add some validity checks for what's in that cell.
gt; gt;
gt; gt;gt; What I am struggling with is being able to write a macro that performs the
gt; gt;gt; following;
gt; gt;[quoted text clipped - 11 lines]
gt; gt;gt;
gt; gt;gt; David
gt; gt;
gt;
gt; --
gt; Message posted via www.officekb.com

--

Dave Peterson