How to implement Customer ID auto increment when using alpha numeric values with VBA

I have created a simple UserForm to enter new customer details to the Customer List in the spreadsheet, form works fine except for one little thing, which is New Customer ID, which works, but only with numeric values like 1, 2, 3 and so on.

Basically what I need this for to do is once form is opened/called new customer ID need to be created, which could be and Alfa numerical set of characters like AA-01234, AA-01235, AA-01236 and so on.

Also, is there a way of posting newly added Customer ID in the MsgBox along with MsgBox "One record added to Customers List. New Customer ID is "

All of my attempts to create this are failing and causing errors, which I really cannot figure out since I am new to VBA and had never used it until now.

Please help me a little.

Here is my code, Customer ID is TextBox1.

Thanks in advance

Private Sub UserForm_Activate()
Dim iRow As Long
Dim ws As Worksheet

    Set ws = Worksheets("Customers")

    RefNo.Enabled = True
    'find last data row from database
    iRow = ws.Cells(Rows.Count, 8).End(xlUp).Row

    If ws.Range("A" & iRow).Value = "" Then
        RefNo.Text = "TAS1"
        ws.Range("A" & iRow).Value = RefNo
    Else
        RefNo.Text = "TAS" & Val(Mid(ws.Cells(iRow, 1).Value, 4)) + 1
        ws.Range("A" & iRow + 1).Value = RefNo
    End If
    TextBox1.Value = WorksheetFunction.Max(Range("Customers!A8:A65536")) + 1
End Sub

Private Sub Addreccord_Click()
    Dim LastRow As Object

    Set LastRow = Range("Customers!A65536").End(xlUp)

    LastRow.Offset(1, 0).Value = WorksheetFunction.Max(Range("Customers!A8:A65536")) + 1
    LastRow.Offset(1, 1).Value = TextBox2.Text
    LastRow.Offset(1, 2).Value = TextBox3.Text
    LastRow.Offset(1, 3).Value = TextBox4.Text
    LastRow.Offset(1, 4).Value = TextBox5.Text
    LastRow.Offset(1, 5).Value = TextBox6.Text
    LastRow.Offset(1, 6).Value = TextBox7.Text
    LastRow.Offset(1, 7).Value = TextBox8.Text
    LastRow.Offset(1, 8).Value = TextBox9.Text
    LastRow.Offset(1, 9).Value = TextBox10.Text
    LastRow.Offset(1, 10).Value = TextBox11.Text

    MsgBox "One record added to Customers List"

    response = MsgBox("Do you want to enter another record?", _
              vbYesNo)

    If response = vbYes Then
        TextBox1.Value = WorksheetFunction.Max(Range("Customers!A8:A65536")) + 1
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox4.Text = ""
        TextBox5.Text = ""
        TextBox6.Text = ""
        TextBox7.Text = ""
        TextBox8.Text = ""
        TextBox9.Text = ""
        TextBox10.Text = ""
        TextBox11.Text = ""

        TextBox2.SetFocus

    Else
       Unload Me
    End If

End Sub
Private Sub Exitform_Click()
    End
End Sub
Sub ClearFields_Click()
    For Each ctrl In Me.Controls
        Select Case TypeName(ctrl)
            Case "TextBox"
                ctrl.Text = ""
        End Select
    Next ctrl
End Sub

If my spreadsheet is required, please let me know and I can post it here

@AlphaFrog, thanks for your suggestion about "Unload Me", it is always good to learn something new.

The firs part part of the code did not work at all, possible reason is that I might have done something that I should not have.

If it is not too much can you please post entire code as it should be?

Thanks in advance.

I've posted code that I was trying at that point of time by accident. Here I am posting another version of the code and Image of the Customer DB where Data posted via UserForm is stored.

Private Sub UserForm_Activate()
    Dim LastRow As Range, strNextID As String
    
    Set LastRow = Range("Customers!A65536").End(xlUp)
    
    strNextID = "AA-" & Format(Right(WorksheetFunction.Max(Range("Customers!A8:A65536")), 5) + 1, "00000")
    
    TextBox1.Value = strNextID
    Dim v, e
    With Sheets("Settings").Range("_paymentTerms")
        v = .Value
    End With
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For Each e In v
            If Not .exists(e) Then .Add e, Nothing
        Next
        If .Count Then Me.ComboBox1.List = Application.Transpose(.keys)
    End With
    
End Sub
Private Sub Addreccord_Click()
    Dim LastRow As Range, strNextID As String
    
    Set LastRow = Range("Customers!A65536").End(xlUp)
    
    strNextID = "AA-" & Format(Right(WorksheetFunction.Max(Range("Customers!A8:A65536")), 5) + 1, "00000")
    
    LastRow.Offset(1, 0).Value = strNextID
    LastRow.Offset(1, 1).Value = TextBox2.Text
    LastRow.Offset(1, 2).Value = TextBox3.Text
    LastRow.Offset(1, 3).Value = TextBox4.Text
    LastRow.Offset(1, 4).Value = TextBox5.Text
    LastRow.Offset(1, 5).Value = TextBox6.Text
    LastRow.Offset(1, 6).Value = TextBox7.Text
    LastRow.Offset(1, 7).Value = TextBox8.Text
    LastRow.Offset(1, 8).Value = TextBox9.Text
    LastRow.Offset(1, 9).Value = TextBox10.Text
    LastRow.Offset(1, 10).Value = TextBox11.Text
    LastRow.Offset(1, 11).Value = ComboBox1.Value

    MsgBox "One record added to Customers List"

    response = MsgBox("Do you want to enter another record?", _
              vbYesNo)

    If response = vbYes Then
        TextBox1.Value = strNextID
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox4.Text = ""
        TextBox5.Text = ""
        TextBox6.Text = ""
        TextBox7.Text = ""
        TextBox8.Text = ""
        TextBox9.Text = ""
        TextBox10.Text = ""
        TextBox11.Text = ""

        TextBox2.SetFocus

    Else
       Unload Me
    End If

End Sub
Private Sub Exitform_Click()
    Unload Me
End Sub
Sub ClearFields_Click()
    For Each ctrl In Me.Controls
        Select Case TypeName(ctrl)
            Case "TextBox"
                ctrl.Text = ""
            Case "ComboBox", "ListBox"
                ctrl.ListIndex = -1
        End Select
    Next ctrl
End Sub

No matter how many times I try to enter a new customer details ID remains the same when realistically it should change every time, I cannot figure out what I am doing wrong. Please help and thanks for being patient

Sweet, works like magic, but little too slow, otherwise perfect

Thanks a lot and once again thank for being patient!!

Originally Posted by Alexb77

Sweet, works like magic, but little too slow, otherwise perfect

Are you talking about the code I posted?

I have tested the code for myself with 47000 rows of data and took only 0.109375 sec.

It must be other part of your code....

I have done so but it did not work, I ended with and Error

Try

strNextID = Format$(Sheets("customers").[max(if(a1:a65536<>"",mid(a1:a65536,4,5)+0))] + 1, "AA-00000")