Macro to generate next number with one click in cell?

aherzog

New Member
Joined
May 27, 2015
Messages
33
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?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Mike LH

Well-known Member
Joined
Mar 17, 2015
Messages
566
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.


Code:
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

New Member
Joined
May 27, 2015
Messages
33
I did, and this message popped up:

Compile Error:
Ambiguous name detected: Worksheet_BeforeDoubleClick
 

Mike LH

Well-known Member
Joined
Mar 17, 2015
Messages
566
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

New Member
Joined
May 27, 2015
Messages
33
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,592
Messages
5,637,290
Members
416,962
Latest member
samfuge

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