Click for Date & Next Number

Excel-ent

Active Member
Joined
Mar 4, 2004
Messages
338
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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Chitosunday

Well-known Member
Joined
Jul 14, 2003
Messages
1,017
Put this in the sheet code (right click the "sheet name" tab below left of screen, view code)

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim xrange As Range
Set xrange = Application.Intersect(Range("b2:b65536"), Target)
If xrange Is Nothing Then Exit Sub
Range("c1") = Range("c1").Value + 1
MsgBox Range("c1").Value
Range("c2:c65536") = Date
End Sub
 

WJReid

Active Member
Joined
Jul 26, 2002
Messages
317
Hi Shan,

Can't you put a New Record button on your sheet and each time you click it, it adds the next record number and date?

Private Sub CommandButton1_Click()
Sheet1.Range("B65536").End(xlUp).Offset(1, 0).Value = "=today()"
Sheet1.Range("B65536").End(xlUp).Offset(0, 1).Value = "=max(C:C)+1"
End Sub

Regards,

Bill
 

WJReid

Active Member
Joined
Jul 26, 2002
Messages
317
Hi Shan,

If you add the following line to the end of code to the command button code I posted, it will automatically go to the record you created, so you don't have to be at the last record on column B to create a new one.

Sheet1.Range("B65536").End(xlUp).Select

Regards,

Bill
 

WJReid

Active Member
Joined
Jul 26, 2002
Messages
317

ADVERTISEMENT

Hi Shan,

The code that I gave you will create a circular reference because of choosing the complete column C. The following code will stop that happening and place values rather than formulas into the cells.
For the following, I have put the formulas:

=Max(C:C)+1 in cell IV1
=Today() in cell IV2

Private Sub CommandButton1_Click()
Dim val As Double
Dim dt As Date
val = Sheet1.Range("IV1").Value
dt = Sheet1.Range("IV2").Value
Sheet1.Range("B65536").End(xlUp).Offset(1, 0).Value = dt
Sheet1.Range("B65536").End(xlUp).Offset(0, 1).Value = val
Sheet1.Range("B65536").End(xlUp).Select
End Sub


Regards,

Bill
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hi Shan,
To use Bill's idea without the formulas in IV1 & IV2, or getting formulas & circular references in the B & C columns, you can use this modified version of his code for the command button.
Code:
Private Sub CommandButton1_Click()
Dim val As Double
val = Sheet1.Range("B65536").End(xlUp).Offset(, 1).Value + 1
Sheet1.Range("B65536").End(xlUp).Offset(1).Value = Date
Sheet1.Range("C65536").End(xlUp).Offset(1).Value = val
Sheet1.Range("B65536").End(xlUp).Select
End Sub

(Just another variation on an already good idea...)
Dan
 

Excel-ent

Active Member
Joined
Mar 4, 2004
Messages
338

ADVERTISEMENT

I appreciate your replies, but sorry ! it doesn't work.

I will try to explain the sheet.

A8 - A65536 is the Serial Number (Which is already set)
B8 - B65536 is the Required Dates (to show today's date on click)
C8 - C65536 (Used for some other purpose / not required)
D8 - D65536 (Required Next Number from the Current Number, I'll put the first number in Cell D8.

Is the Code required to be changed ?

as

When I click the command button, the first cell for the Date gives me something like this 12:00:00 A.M.

and D8 is the Reference number gives me 00/01/1900.

Help! please.

Regards,

Shan
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Yep, Sorry. I was reading the solutions and not the question well enough. :oops:
If you change the code to this:
Code:
Private Sub CommandButton1_Click()
Dim val As Double
val = Sheet1.Range("D65536").End(xlUp).Value + 1
Sheet1.Range("B65536").End(xlUp).Offset(1).Value = Date
Sheet1.Range("D65536").End(xlUp).Offset(1).Value = val
Sheet1.Range("B65536").End(xlUp).Select
End Sub
and then enter 3/23/2004 in cell B8 and the "current number" in cell D8, then the button should enter the current date in the next row down in column B and increment the number up by 1 in the next row down in column D every time it gets clicked. (Is it not doing this? If not, what results / error messages do you get?)

Dan
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hi Shan, I appologize again. I just read your original post carefully and realized you want these changes to take place when you select the cells in column B & C. (Am I finally getting it right? :rolleyes: )
If this is correct then you can paste this in the sheet module of whatever sheet you want it to work in.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Columns.Count > 1 Or Target.Rows.Count > 1 Or Target.Row < 2 Or Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 2 Then Target.Value = Date
If Target.Column = 3 Then Target.Value = Target.Offset(-1).Value + 1
End Sub

Let me know if this is what you want, and I'm sorry for not paying closer attention to what you asked for the first time.
Dan
 

Chitosunday

Well-known Member
Joined
Jul 14, 2003
Messages
1,017
Again put this in the sheetcode (right click sheetname below left, click view code)

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim xrange As Range
Set xrange = Application.Intersect(Range("b8:b65536"), Target)
If xrange Is Nothing Then Exit Sub
Target = Date
If IsEmpty(Range("d8")) Then
Range("d8") = 1
Else
Range("d65536").End(xlUp).Offset(1, 0).Value = Range("d65536").End(xlUp) + 1
End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,545
Members
414,316
Latest member
ExcelLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top