Workbook_Open Event questions

[Stuart]

When a workbook opens, I need to do the following:

1) Insert the current date into a cell (H11) in the form
Friday 25th June 2004.

2) Increment the value in cell "J9" by 1, where the J9
value was /1000/ and would become /1001/
(can I do this quickly without using Split?).

Regards and thanks.

 

[Bob Phillips]

1) Range("H11").Value = Format(Date,"dddd dd mmm yyyy")

can't get the date suffix (st, th, etc.) without lots of work

2) With Range("J9")
,Value = "/" &Clng(Mid(.Value,2,4))+1 & "/"
End With

All in the WorkbookOpen event in ThisWorkbook code module

--

HTH

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

 

[Stuart]

Many thanks.

Lots of work not required !

Regards.

 

[Bob Phillips]

Just spotted a type

2) With Range("J9")
,Value = "/" &Clng(Mid(.Value,2,4))+1 & "/"
End With

should be

2) With Range("J9")
.Value = "/" &CLng(Mid(.Value,2,4))+1 & "/"
End With

--

HTH

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

 

[Stuart]

Found it too. No probs.
Regards.

Just spotted a type

2) With Range("J9")
,Value = "/" &Clng(Mid(.Value,2,4))+1 & "/"
End With

should be

2) With Range("J9")
.Value = "/" &CLng(Mid(.Value,2,4))+1 & "/"
End With

--

HTH

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