Semiautomated Saving of workbook
 

First, I don't think I'd put assign this code to a toolbar icon.

That icon could be used for any workbook.

Instead, I'd drop a button from the Forms toolbar (not the control toolbox
toolbar) on that sheet that gets the username and sunDT entries.

Then I'd assign this macro to that button:

Option Explicit
Sub SaveWorkbook()

Dim USRNM As String
Dim SunDT As String
Dim strFName As String

With ActiveSheet
USRNM = .Range("b5").Value
SunDT = .Range("A11").Value

If Trim(USRNM) = "" _
Or Trim(SunDT) = "" Then
MsgBox "Please fill in the username and Sunday Date" & vbLf _
& "File not saved!"
Else
strFName = "TimeCard " & SunDT & " " & USRNM
.Parent.SaveAs strFName
End If
End With

End Sub

And if that SunDt is really a date, remember that if you're using Windows, then
the filename cannot include slashes (like in 01/15/2007).

Maybe...
SunDT = format(.Range("A11").Value, "yyyy_mm_dd")

instead?????





DawnTreader wrote:

Hello

i am currently working to "dummy" proof a spreadsheet. i want my users to be
able to hit a button and save the workbook, but not have to type a name in
the dialog. i still want them to be able to save the workbook where ever they
like, but i dont want them to be able to type in the name. how?

here is my code so far:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim wb As Workbook
Set wb = ActiveWorkbook

Dim USRNM As String
USRNM = Range("b5").Value

Dim SunDT As String
SunDT = Range("A11").Value

Dim strFName As String
strFName = "TimeCard " & SunDT & " " & USRNM

' Save file
wb.SaveAs strFName

End Sub

additionally how do i connect this to a button on my toolbar?

--

Dave Peterson

Semiautomated Saving of workbook
 

I wouldn't rely on the user for the format of the date.

If you want dd-mmm-yy, then use:
SunDT = format(.Range("A11").Value, "dd-mmm-yy")

You can put the button on the worksheet and change its property so it doesn't
print.

Rightclick on the button|Format Control|Properties tab

And the code I suggested actually does the save with this line:
..Parent.SaveAs strFName

It doesn't show the dialog.

DawnTreader wrote:

Hello Dave

yeah that SunDT is a date, and i hadnt thought of that, but i have told the
users to format it in 12-dec-06 format. i also think that the cell is
formatted that way. i might see if i can make sure that it gets entered in
that format no matter what, some how.

additionally i have code that removes all toolbars and then shows my custom
toolbar so that they only have a few options with this spreadsheet. i need to
keep the spreadsheet clear of buttons and stuff as this thing will be printed
and used as hardcopy records of thier payroll. i could put the button off a
ways on the side of the document i suppose, but they have to be able to see
it without looking for it. i will work out that some how.

my understanding of your suggestion is to have a button on the sheet that
captures the data from the 2 cells and then opens the save dialog with the
file name set by the code. am i correct?

i already have a form that pops up when they open the workbook if there is
no user name entered. this form places the user name, employee number and
date in the appropriate cells on the form so i dont really need to validate
that data as you put in the code.

so just to clarify, that code goes on a button on the spreadsheet?

the more i think about it the more i like that idea. :)

Thanks for any clairification.

"Dave Peterson" wrote:

First, I don't think I'd put assign this code to a toolbar icon.

That icon could be used for any workbook.

Instead, I'd drop a button from the Forms toolbar (not the control toolbox
toolbar) on that sheet that gets the username and sunDT entries.

Then I'd assign this macro to that button:

Option Explicit
Sub SaveWorkbook()

Dim USRNM As String
Dim SunDT As String
Dim strFName As String

With ActiveSheet
USRNM = .Range("b5").Value
SunDT = .Range("A11").Value

If Trim(USRNM) = "" _
Or Trim(SunDT) = "" Then
MsgBox "Please fill in the username and Sunday Date" & vbLf _
& "File not saved!"
Else
strFName = "TimeCard " & SunDT & " " & USRNM
.Parent.SaveAs strFName
End If
End With

End Sub

And if that SunDt is really a date, remember that if you're using Windows, then
the filename cannot include slashes (like in 01/15/2007).

Maybe...
SunDT = format(.Range("A11").Value, "yyyy_mm_dd")

instead?????





DawnTreader wrote:

Hello

i am currently working to "dummy" proof a spreadsheet. i want my users to be
able to hit a button and save the workbook, but not have to type a name in
the dialog. i still want them to be able to save the workbook where ever they
like, but i dont want them to be able to type in the name. how?

here is my code so far:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim wb As Workbook
Set wb = ActiveWorkbook

Dim USRNM As String
USRNM = Range("b5").Value

Dim SunDT As String
SunDT = Range("A11").Value

Dim strFName As String
strFName = "TimeCard " & SunDT & " " & USRNM

' Save file
wb.SaveAs strFName

End Sub

additionally how do i connect this to a button on my toolbar?

--

Dave Peterson


--

Dave Peterson

Semiautomated Saving of workbook
 

Hello again

is there a way to show the dialog with that code? i want them to be able to
save it anywhere they want, but to not change the name. and thanks for the
tip on the formatting the date, that will be cool. :)

know that all help is greatly appreciated. :)

"Dave Peterson" wrote:

I wouldn't rely on the user for the format of the date.

If you want dd-mmm-yy, then use:
SunDT = format(.Range("A11").Value, "dd-mmm-yy")

You can put the button on the worksheet and change its property so it doesn't
print.

Rightclick on the button|Format Control|Properties tab

And the code I suggested actually does the save with this line:
..Parent.SaveAs strFName

It doesn't show the dialog.

DawnTreader wrote:

Hello Dave

yeah that SunDT is a date, and i hadnt thought of that, but i have told the
users to format it in 12-dec-06 format. i also think that the cell is
formatted that way. i might see if i can make sure that it gets entered in
that format no matter what, some how.

additionally i have code that removes all toolbars and then shows my custom
toolbar so that they only have a few options with this spreadsheet. i need to
keep the spreadsheet clear of buttons and stuff as this thing will be printed
and used as hardcopy records of thier payroll. i could put the button off a
ways on the side of the document i suppose, but they have to be able to see
it without looking for it. i will work out that some how.

my understanding of your suggestion is to have a button on the sheet that
captures the data from the 2 cells and then opens the save dialog with the
file name set by the code. am i correct?

i already have a form that pops up when they open the workbook if there is
no user name entered. this form places the user name, employee number and
date in the appropriate cells on the form so i dont really need to validate
that data as you put in the code.

so just to clarify, that code goes on a button on the spreadsheet?

the more i think about it the more i like that idea. :)

Thanks for any clairification.

"Dave Peterson" wrote:

First, I don't think I'd put assign this code to a toolbar icon.

That icon could be used for any workbook.

Instead, I'd drop a button from the Forms toolbar (not the control toolbox
toolbar) on that sheet that gets the username and sunDT entries.

Then I'd assign this macro to that button:

Option Explicit
Sub SaveWorkbook()

Dim USRNM As String
Dim SunDT As String
Dim strFName As String

With ActiveSheet
USRNM = .Range("b5").Value
SunDT = .Range("A11").Value

If Trim(USRNM) = "" _
Or Trim(SunDT) = "" Then
MsgBox "Please fill in the username and Sunday Date" & vbLf _
& "File not saved!"
Else
strFName = "TimeCard " & SunDT & " " & USRNM
.Parent.SaveAs strFName
End If
End With

End Sub

And if that SunDt is really a date, remember that if you're using Windows, then
the filename cannot include slashes (like in 01/15/2007).

Maybe...
SunDT = format(.Range("A11").Value, "yyyy_mm_dd")

instead?????





DawnTreader wrote:

Hello

i am currently working to "dummy" proof a spreadsheet. i want my users to be
able to hit a button and save the workbook, but not have to type a name in
the dialog. i still want them to be able to save the workbook where ever they
like, but i dont want them to be able to type in the name. how?

here is my code so far:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim wb As Workbook
Set wb = ActiveWorkbook

Dim USRNM As String
USRNM = Range("b5").Value

Dim SunDT As String
SunDT = Range("A11").Value

Dim strFName As String
strFName = "TimeCard " & SunDT & " " & USRNM

' Save file
wb.SaveAs strFName

End Sub

additionally how do i connect this to a button on my toolbar?

--

Dave Peterson


--

Dave Peterson

Semiautomated Saving of workbook
 

If you want them to choose a folder and you choose the filename, you could use
different code.

Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm
(look for BrowseForFolder)

John Walkenbach has one at:
http://j-walk.com/ss/excel/tips/tip29.htm

DawnTreader wrote:

Hello again

is there a way to show the dialog with that code? i want them to be able to
save it anywhere they want, but to not change the name. and thanks for the
tip on the formatting the date, that will be cool. :)

know that all help is greatly appreciated. :)

"Dave Peterson" wrote:

I wouldn't rely on the user for the format of the date.

If you want dd-mmm-yy, then use:
SunDT = format(.Range("A11").Value, "dd-mmm-yy")

You can put the button on the worksheet and change its property so it doesn't
print.

Rightclick on the button|Format Control|Properties tab

And the code I suggested actually does the save with this line:
..Parent.SaveAs strFName

It doesn't show the dialog.

DawnTreader wrote:

Hello Dave

yeah that SunDT is a date, and i hadnt thought of that, but i have told the
users to format it in 12-dec-06 format. i also think that the cell is
formatted that way. i might see if i can make sure that it gets entered in
that format no matter what, some how.

additionally i have code that removes all toolbars and then shows my custom
toolbar so that they only have a few options with this spreadsheet. i need to
keep the spreadsheet clear of buttons and stuff as this thing will be printed
and used as hardcopy records of thier payroll. i could put the button off a
ways on the side of the document i suppose, but they have to be able to see
it without looking for it. i will work out that some how.

my understanding of your suggestion is to have a button on the sheet that
captures the data from the 2 cells and then opens the save dialog with the
file name set by the code. am i correct?

i already have a form that pops up when they open the workbook if there is
no user name entered. this form places the user name, employee number and
date in the appropriate cells on the form so i dont really need to validate
that data as you put in the code.

so just to clarify, that code goes on a button on the spreadsheet?

the more i think about it the more i like that idea. :)

Thanks for any clairification.

"Dave Peterson" wrote:

First, I don't think I'd put assign this code to a toolbar icon.

That icon could be used for any workbook.

Instead, I'd drop a button from the Forms toolbar (not the control toolbox
toolbar) on that sheet that gets the username and sunDT entries.

Then I'd assign this macro to that button:

Option Explicit
Sub SaveWorkbook()

Dim USRNM As String
Dim SunDT As String
Dim strFName As String

With ActiveSheet
USRNM = .Range("b5").Value
SunDT = .Range("A11").Value

If Trim(USRNM) = "" _
Or Trim(SunDT) = "" Then
MsgBox "Please fill in the username and Sunday Date" & vbLf _
& "File not saved!"
Else
strFName = "TimeCard " & SunDT & " " & USRNM
.Parent.SaveAs strFName
End If
End With

End Sub

And if that SunDt is really a date, remember that if you're using Windows, then
the filename cannot include slashes (like in 01/15/2007).

Maybe...
SunDT = format(.Range("A11").Value, "yyyy_mm_dd")

instead?????





DawnTreader wrote:

Hello

i am currently working to "dummy" proof a spreadsheet. i want my users to be
able to hit a button and save the workbook, but not have to type a name in
the dialog. i still want them to be able to save the workbook where ever they
like, but i dont want them to be able to type in the name. how?

here is my code so far:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim wb As Workbook
Set wb = ActiveWorkbook

Dim USRNM As String
USRNM = Range("b5").Value

Dim SunDT As String
SunDT = Range("A11").Value

Dim strFName As String
strFName = "TimeCard " & SunDT & " " & USRNM

' Save file
wb.SaveAs strFName

End Sub

additionally how do i connect this to a button on my toolbar?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson