Assigning a new customer ID when using UserForm to add new customer

[Narcie]

Hello there!

I believe this is a issue that has been asked about a lot, both on here and on many other websites related to Excel (VBA) and yet I've still not been able to figure it out and to make it work for my code.

Currently I'm using a UserForm to enter data into my sheets. However, I'm required to assign a customer ID by myself which is kind of annoying because I would have to check the last customer I added manually to find out what customer ID to assign to the new one. I know this can be done automaticly and that's what I'm going for here.

What I'm working with is a UserForm that, when you click on cmdNew, allows you to enter customer details, which is then stored in a sheet called 'Klantenlijst'. 'txtKlantnr' is the textbox that should have the customer ID in it, which should be stored (and obtained) in column A, starting at row 2. So for that to work, I'd need Excel to check the values in column A and assign the highest value in the column +1 to 'txtKlantnr' whenever I click on 'cmdNew'.

I've tried several methods found on here but none seem to work for me. I do have to add that I'm very new to Excel VBA so please be thorough and specific with your tips .

Thanks in advance and my code is found below, I only copied the cmdNew code but I can provide more upon request of course.

Private Sub cmdNew_Click()
    blnNew = True
        
    txtKlantnr.Text = ""
    txtTitel.Text = ""
    txtVnaam.Text = ""
    txtAnaam.Text = ""
    txtStraat.Text = ""
    txtPcode.Text = ""
    txtStad.Text = ""
    txtTelefoon.Text = ""
    txtEmail.Text = ""
    
    cmdClose.Caption = "Annuleren"
    cmdNew.Enabled = False
    cmdDelete.Enabled = False
    cmdSave.Enabled = True
    cmdFactuur.Enabled = False
    Frame2.Enabled = True
    
    txtKlantnr.Enabled = False
    txtTitel.Enabled = True
    txtVnaam.Enabled = True
    txtAnaam.Enabled = True
    txtStraat.Enabled = True
    txtPcode.Enabled = True
    txtStad.Enabled = True
    txtTelefoon.Enabled = True
    txtEmail.Enabled = True
    
    Label2.Enabled = True
    Label5.Enabled = True
    Label4.Enabled = True
    Label3.Enabled = True
    Label6.Enabled = True
    Label7.Enabled = True
    Label8.Enabled = True
    Label9.Enabled = True
    Label10.Enabled = True
    
    ComboBox1.Enabled = False
    ComboBox2.Enabled = False
    
    ComboBox1.Text = ""
    ComboBox2.Text = ""
    
    OptionButton1.Enabled = False
    OptionButton2.Enabled = False
End Sub

 

[mjf55]

How about this?

txtKlantnr.Test = Application.WorksheetFunction.Max(range("a2:a" & Range("A" & Rows.Count).End(xlUp).Row))

Excel function MAX will return the Max value in a range
Application.WorksheetFunction.Max is VBA was of calling that function
Range("A" & Rows.Count).End(xlUp).Row) finds the last row in column a

 

[Kyle123]

Why not just use Access? It will do this automatically with no code

 

[Narcie]

@mjf55 that works, thanks! I can't believe how simple that was and yet I haven't been able to figure it out before. Thanks for the help!

@Kyle123 I might look into that as well, but for now I actually almost have everything working so it's all the same to me.

 

[Narcie]

Actually, it doesn't seem to work! I thought it did but then I realized it's adding a 0 as customer ID even after adding a new one it still says 0. While it should actually start at 1 (or 0 doesn't really matter) and then add one to the next.

 

[Narcie]

I fixed it by adding +1 to the end of the code, as well as telling the range which sheet to use for column A. Everything's working like a charm now. Thanks for the help.