How can I generate an auto number in cell A1 by simply clicking on cell B1

I can think of 2 ways to do it quickly in VB code attached to the worksheet
itself.

I am thinking this would be the more certain, less prone to accident way:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim iSect As Range
'did you double-click in cell B1?
Set iSect = Application.Intersect(Range(Target.Address), Range("B1"))
If iSect Is Nothing Then
' no, you did not
Exit Sub
End If
'yes, you did, increment contents of A1 by 1
Application.EnableEvents = False
Range("A1") = Range("A1") + 1
Cancel = True ' negate the double-click action
Application.EnableEvents = True
End Sub

That will increment the contents of A1 by 1 each time you double-click in
cell B1.

The other way is to literally increment A1 by 1 every time you go to cell B1
(but not each time you click it - just when you choose it after some other
cell on the sheet has been previously selected).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iSect As Range
'did you select cell B1?
Set iSect = Application.Intersect(Range(Target.Address), Range("B1"))
If iSect Is Nothing Then
' no, you did not
Exit Sub
End If
'yes, you did, increment contents of A1 by 1
Application.EnableEvents = False
Range("A1") = Range("A1") + 1
Application.EnableEvents = True
End Sub

Use one or the other of these, not both. If you need help getting the code
into the Worksheet code module, easy way is to right-click on the sheet's tab
and choose [View Code]. For more help, see
http://www.jlathamsite.com/Teach/WorksheetCode.htm


"JohannM" wrote:

How can I generate an auto number in cell A1 by simply clicking on cell B1