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 " you have now saved your file" and to have the choice to close the
workbook?

any thing would be appreciated,

many thanks

David

--
Message posted via OfficeKB.com
http://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 = "C:\my documents\excel\" _
& .Worksheets("sheet1").Range("a1").Value _
& "_" & Format(Now, "yyyymmdd_hhmmss") & ".xls"

.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
resp = MsgBox(prompt:="Wanna close this workbook?", _
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.



"Ozzie via OfficeKB.com" wrote:

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 " you have now saved your file" and to have the choice to close the
workbook?

any thing would be appreciated,

many thanks

David

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

--

Dave Peterson

Dave, many thanks it works a treat

Dave Peterson wrote:
Maybe something like this:

Option Explicit
Sub testme()

Dim myFileName As String
Dim resp As Long

With ActiveWorkbook
myFileName = "C:\my documents\excel\" _
& .Worksheets("sheet1").Range("a1").Value _
& "_" & Format(Now, "yyyymmdd_hhmmss") & ".xls"

.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
resp = MsgBox(prompt:="Wanna close this workbook?", _
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.

What I am struggling with is being able to write a macro that performs the
following;
[quoted text clipped - 11 lines]

David


--
Message posted via http://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 "you have now saved and sent your data"

any idea's??

thanks again for all your help, cheers


Dave Peterson wrote:
Maybe something like this:

Option Explicit
Sub testme()

Dim myFileName As String
Dim resp As Long

With ActiveWorkbook
myFileName = "C:\my documents\excel\" _
& .Worksheets("sheet1").Range("a1").Value _
& "_" & Format(Now, "yyyymmdd_hhmmss") & ".xls"

.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
resp = MsgBox(prompt:="Wanna close this workbook?", _
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.

What I am struggling with is being able to write a macro that performs the
following;
[quoted text clipped - 11 lines]

David


--
Message posted via http://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 = "C:\my documents\excel\" _
& .Worksheets("sheet1").Range("a1").Value _
& "_" & Format(Now, "yyyymmdd_hhmmss") & ".xls"

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

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

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

End Sub

Sub auto_open()
Worksheets("sheet1").Buttons("button 1").Caption _
= "Click me to save and send your data"
End Sub

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

"Ozzie via OfficeKB.com" wrote:

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 "you have now saved and sent your data"

any idea's??

thanks again for all your help, cheers

Dave Peterson wrote:
Maybe something like this:

Option Explicit
Sub testme()

Dim myFileName As String
Dim resp As Long

With ActiveWorkbook
myFileName = "C:\my documents\excel\" _
& .Worksheets("sheet1").Range("a1").Value _
& "_" & Format(Now, "yyyymmdd_hhmmss") & ".xls"

.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
resp = MsgBox(prompt:="Wanna close this workbook?", _
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.

What I am struggling with is being able to write a macro that performs the
following;
[quoted text clipped - 11 lines]

David


--
Message posted via http://www.officekb.com

--

Dave Peterson