Macro to save a file with ref to a tick box

[Ozzie via OfficeKB.com]

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

 

[Dave Peterson]

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]

Dave, many thanks it works a treat

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

 

[Ozzie via 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

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

 

[Dave Peterson]

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.

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

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