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