Saving a worksheet to a name from a cell

[Mick Garrett]

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

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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)

 

[gumly]

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)




.