Click for Date & Next Number

[Excel-ent]

Dear Experts,

I have a sheet, which is used for recording the Correspondences. This Sheet contains 5 columns i.e. Date, Reference Number, Sent to, Subject of Correspondence & Remarks.

What I am looking for is to facilitate this work. Is it possible that starting from B2 till B65536, whenever I click on the cell, current date should appear and in C2 till C65536, if I click, should show me next number e.g. if first number is 3127 then by clicking the next cell, it should show me 3128 and so on.

Other cells will certainly be done manually.

I am using Windows 98 & Office 2000.

Thanks for your efforts.

Regards,

Shan

 

[Excel-ent]

I have tried HalfAce's code mentioned in his second last Post, works fine, except for reference. I have entered the first reference as 0127 in the cell D8, when i click for next reference, it shows 128 instead of 0128.

Thanks for help.

Shan

 

[onlyadrafter]

Hello,

Further to your PM, here is the code I cam up with

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Column = 2 And Target.Row > 1 Then
    If Target.Value = "" Then
        Target.Value = Format(Now, "dd/mm/yy")
    End If
End If
If Target.Column = 4 And Target.Row > 1 Then
    CURRENT_MAX = Range("D1").Value
    If Target.Value = "" Then
        Target.Value = CURRENT_MAX + 1
    End If
End If
End Sub

You will need this formula somewhere, I have it in D1

=MAX($D$2:$D$65536)

as for the format, why not format Column D as a CUSTOM and enter 0000

Re reading all posts, if Half Ace's is working, all yo need to do is format Column D.

Does this answer your question?

 

[Excel-ent]

Yes it does perfectly.

Thanks for your prompt reply.

Regards,

Shan