Kaniguan1969
03-13-2014, 09:53 PM
Hi ,
I have a userform with button to generate automatic ticket number.
Ticket number contains "L1"+yy+mm-999. I have already the codes but
my problem with my codes it did not add a number. thank you in advance.
my worksheet name as reference is "TransactionDB"
Column "A"
Here is my code:
Public Sub cmdnew_Click()
Dim iRow As Long
Dim wb As Workbook
Dim ws As Worksheet
Dim yfmt As String, fmt As String, datefmt As String, TicketNum As String, sample As String
yfmt = Format(Date, "yy")
mfmt = Format(Date, "mm")
datefmt = yfmt & mfmt
Set wb = ThisWorkbook
Set ws = wb.Sheets("TransactionDB")
Me.TextBox1.Enabled = True
With ws
my_sheet = ActiveSheet.Name
If Trim(Me.TextBox1.Value) = "" Then
'Generate Ticket Number
Me.TextBox1.SetFocus
MsgBox "Please enter Ticket Number; one will be suggested…", vbExclamation, "Ticket Number"
TicketNum = Format(Application.Max(ws.Range("A:A")) + 1, "000")
Me.TextBox1.Value = "L1" & datefmt & "-" & Format(TicketNum + 1, "000")
Exit Sub
End If
End With
Me.CmdNew.Enabled = True
End Sub
I have a userform with button to generate automatic ticket number.
Ticket number contains "L1"+yy+mm-999. I have already the codes but
my problem with my codes it did not add a number. thank you in advance.
my worksheet name as reference is "TransactionDB"
Column "A"
Here is my code:
Public Sub cmdnew_Click()
Dim iRow As Long
Dim wb As Workbook
Dim ws As Worksheet
Dim yfmt As String, fmt As String, datefmt As String, TicketNum As String, sample As String
yfmt = Format(Date, "yy")
mfmt = Format(Date, "mm")
datefmt = yfmt & mfmt
Set wb = ThisWorkbook
Set ws = wb.Sheets("TransactionDB")
Me.TextBox1.Enabled = True
With ws
my_sheet = ActiveSheet.Name
If Trim(Me.TextBox1.Value) = "" Then
'Generate Ticket Number
Me.TextBox1.SetFocus
MsgBox "Please enter Ticket Number; one will be suggested…", vbExclamation, "Ticket Number"
TicketNum = Format(Application.Max(ws.Range("A:A")) + 1, "000")
Me.TextBox1.Value = "L1" & datefmt & "-" & Format(TicketNum + 1, "000")
Exit Sub
End If
End With
Me.CmdNew.Enabled = True
End Sub