Macro to generate next number with one click in cell?

[aherzog]

I already have a macro created for my worksheet where a keyboard short cut of "Ctrl+T" generates the next "ticket number" by adding 1 to the last number. For example, if the ticket number in Column C, Row 4 is 10902 then I would click Column C, Row 5 and press "Ctrl+T" shortcut and the ticket number 10903 would pop up. Here is the macro I am already using:

Sub NextTicket()
ActiveCell.Formula = "=MAX(C2:C" + CStr(ActiveCell.Row - 1) + ")+1"
ActiveCell.Formula = ActiveCell.Value 'Convert formula to a value
End Sub

The problem with this is that I did not write this macro, my co-worker did. Also, I am fairly new to working with macros so it is hard for me to decipher this own on my own. My ultimate goal is to double click the cell in that column and it automatically generates the next ticket number, rather than having to manually enter the short cut on the keyboard. Any suggestions?

 

[Mike LH]

Hi,

Right click your sheet tab, view code and paste this code in on the right. If you double click in the empty cell below your last number in col c then the code will add the next number in the sequence.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim lastrow As Long
lastrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row + 1
If Not Intersect(Target, Range("C:C")) Is Nothing Then
If Target.Address = Cells(lastrow, "C").Address Then
Application.EnableEvents = False
Cancel = True
Cells(lastrow, "C") = Cells(lastrow - 1, "C") + 1
Application.EnableEvents = True
End If
End If
End Sub

 

[aherzog]

I did, and this message popped up:

Compile Error:
Ambiguous name detected: Worksheet_BeforeDoubleClick

 

[Mike LH]

Hi,

That means you already have some code in your workbook that uses the double click event. Post that code and we can try and combine them.

 

[aherzog]

This is my other code, which is a double click to generate the current date:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Target <> "" Then On Error GoTo 0: Exit Sub
If Target.Cells.Count > 6 Then Exit Sub
If Intersect(Target, Range("G:H")) Is Nothing Then Exit Sub
With Target
.Value = Time
.NumberFormat = "h:mm AM/PM"
End With
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("B1:B1100,K1:K1100")) Is Nothing Then
Cancel = True
Target.Value = Date
End If
End Sub

The first code is also for a one-click to enter the current time. I am also using another code that applies to all worksheets to generate the next ticket number, though that is only for a keyboard shortcut and I want it altered to be a double click instead. Here is the original number generator code I have now:

Sub NextTicket()
ActiveCell.Formula = "=MAX(C2:C" + CStr(ActiveCell.Row - 1) + ")+1"
ActiveCell.Formula = ActiveCell.Value 'Convert formula to a value
End Sub