Saving a worksheet to a name from a cell

Hi,

I want to have the user enter a date and then reformat
this to a series of digits and use this as the workbook
name. So, I have the3 user enter the date and then strip
out the MM, DD and YY stuff after I enter the data and
place this into another cell. I want a macro to then fire
that does a save as operation using the value in the cell.
For example, user enter 12/06/03 into cell A1. Cel A2
reformats this as 120603MGA (the MGA are user ID
characters that also get added) and then an operation
fires to save the workbook as 120603MGA.xls

Any ideas? I recorded a macro to capture main functyions
but as a newbie, I am not quite sure how to capture the
value in cell A2 and stuff it into the filename. Can
someone point me to the object model and function calls
for the various worksheet objects.

TIA.

Mick

Mick,

Not sure where the usrer id comes from but the date can be formatted

format(range("A1"),"ddmmyy")

so if a variable userid holds the user id, the save statement would be
something aklin to

ThisWorkbook.SaveAs Filename = Format(Range("A1"), "ddmmyy") & UserId &
".xls"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mick Garrett" wrote in message
...
Hi,

I want to have the user enter a date and then reformat
this to a series of digits and use this as the workbook
name. So, I have the3 user enter the date and then strip
out the MM, DD and YY stuff after I enter the data and
place this into another cell. I want a macro to then fire
that does a save as operation using the value in the cell.
For example, user enter 12/06/03 into cell A1. Cel A2
reformats this as 120603MGA (the MGA are user ID
characters that also get added) and then an operation
fires to save the workbook as 120603MGA.xls

Any ideas? I recorded a macro to capture main functyions
but as a newbie, I am not quite sure how to capture the
value in cell A2 and stuff it into the filename. Can
someone point me to the object model and function calls
for the various worksheet objects.

TIA.

Mick

Thanks Bob,

Is there any way to have this automatically fire when you
leave the date cell, rather than having to manually run it
using a button click event?

TIA.

Mick
-----Original Message-----
Mick,

Not sure where the usrer id comes from but the date can
be formatted

format(range("A1"),"ddmmyy")

so if a variable userid holds the user id, the save
statement would be
something aklin to

ThisWorkbook.SaveAs Filename = Format(Range
("A1"), "ddmmyy") & UserId &
".xls"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mick Garrett" wrote in message
...
Hi,

I want to have the user enter a date and then reformat
this to a series of digits and use this as the workbook
name. So, I have the3 user enter the date and then
strip
out the MM, DD and YY stuff after I enter the data and
place this into another cell. I want a macro to then
fire
that does a save as operation using the value in the
cell.
For example, user enter 12/06/03 into cell A1. Cel A2
reformats this as 120603MGA (the MGA are user ID
characters that also get added) and then an operation
fires to save the workbook as 120603MGA.xls

Any ideas? I recorded a macro to capture main
functyions
but as a newbie, I am not quite sure how to capture the
value in cell A2 and stuff it into the filename. Can
someone point me to the object model and function calls
for the various worksheet objects.

TIA.

Mick


.

Hi Mick,

You could get to do this using worksheet event code. Something like this

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = True
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1")) Is Nothing Then
If IsDate(PrevCell.Value) Then
ThisWorkbook.SaveAs Filename:= _
Format(Range("A1").Value, "ddmmyy") & Range("B1").Value &
".xls"
End If
End If

ws_exit:
Application.EnableEvents = True

End Sub

This will save it when you change the cell A1 and put a valid date in it. I
have assumed that B1 holds the user id. You will need to change to suit.

As worksheet event code, it goes in the worksheet module. Righr-click on the
sheet tab, select View code fropm the menu, and paste the code in there.



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mick Garrett" wrote in message
...
Thanks Bob,

Is there any way to have this automatically fire when you
leave the date cell, rather than having to manually run it
using a button click event?

TIA.

Mick
-----Original Message-----
Mick,

Not sure where the usrer id comes from but the date can
be formatted

format(range("A1"),"ddmmyy")

so if a variable userid holds the user id, the save
statement would be
something aklin to

ThisWorkbook.SaveAs Filename = Format(Range
("A1"), "ddmmyy") & UserId &
".xls"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mick Garrett" wrote in message
...
Hi,

I want to have the user enter a date and then reformat
this to a series of digits and use this as the workbook
name. So, I have the3 user enter the date and then
strip
out the MM, DD and YY stuff after I enter the data and
place this into another cell. I want a macro to then
fire
that does a save as operation using the value in the
cell.
For example, user enter 12/06/03 into cell A1. Cel A2
reformats this as 120603MGA (the MGA are user ID
characters that also get added) and then an operation
fires to save the workbook as 120603MGA.xls

Any ideas? I recorded a macro to capture main
functyions
but as a newbie, I am not quite sure how to capture the
value in cell A2 and stuff it into the filename. Can
someone point me to the object model and function calls
for the various worksheet objects.

TIA.

Mick


.

You are a wiz - thanks.

-----Original Message-----
Hi Mick,

You could get to do this using worksheet event code.
Something like this

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = True
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1")) Is Nothing Then
If IsDate(PrevCell.Value) Then
ThisWorkbook.SaveAs Filename:= _
Format(Range("A1").Value, "ddmmyy") &
Range("B1").Value &
".xls"
End If
End If

ws_exit:
Application.EnableEvents = True

End Sub

This will save it when you change the cell A1 and put a
valid date in it. I
have assumed that B1 holds the user id. You will need to
change to suit.

As worksheet event code, it goes in the worksheet module.
Righr-click on the
sheet tab, select View code fropm the menu, and paste the
code in there.



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mick Garrett" wrote in message
...
Thanks Bob,

Is there any way to have this automatically fire when
you
leave the date cell, rather than having to manually run
it
using a button click event?

TIA.

Mick
-----Original Message-----
Mick,

Not sure where the usrer id comes from but the date
can
be formatted

format(range("A1"),"ddmmyy")

so if a variable userid holds the user id, the save
statement would be
something aklin to

ThisWorkbook.SaveAs Filename = Format(Range
("A1"), "ddmmyy") & UserId &
".xls"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

"Mick Garrett" wrote in
message
...
Hi,

I want to have the user enter a date and then
reformat
this to a series of digits and use this as the
workbook
name. So, I have the3 user enter the date and then
strip
out the MM, DD and YY stuff after I enter the data
and
place this into another cell. I want a macro to then
fire
that does a save as operation using the value in the
cell.
For example, user enter 12/06/03 into cell A1. Cel
A2
reformats this as 120603MGA (the MGA are user ID
characters that also get added) and then an operation
fires to save the workbook as 120603MGA.xls

Any ideas? I recorded a macro to capture main
functyions
but as a newbie, I am not quite sure how to capture
the
value in cell A2 and stuff it into the filename. Can
someone point me to the object model and function
calls
for the various worksheet objects.

TIA.

Mick


.



.