View original (French)

Incrementing Number in Excel VBA

GromGrom Posted messages 102 Status Member -  
ThauTheme Posted messages 1564 Status Member -
Good evening everyone,

I am currently creating a form to record, modify, and view data.
In the case of registering a new record, I would like a value to be incremented automatically (1, 2, ...).
Here is my snippet of code that currently requires me to manually enter a value in comboBox1.
Do you have any idea for a solution?

Thank you for your help

Clem 

Private Sub CommandButton1_Click()

 Dim L As Integer

 If MsgBox("Do you confirm the insertion of this new contact?", vbYesNo, "Confirmation request for addition") = vbYes Then

 L = Sheets("Clients").Range("a65536").End(xlUp).Row + 1

 Range("A" & L).Value = ComboBox1
 Range("B" & L).Value = ComboBox2
 Range("C" & L).Value = TextBox1
 Range("D" & L).Value = TextBox2
 Range("E" & L).Value = TextBox3
 Range("F" & L).Value = TextBox4
 Range("G" & L).Value = TextBox5
 Range("H" & L).Value = TextBox6
 Range("I" & L).Value = TextBox7

 End If

End Sub

1 answer

  1.
    ThauTheme Posted messages 1564 Status Member 160
     
    Hello GromGrom, hello forum

    Replace your ComboBox1 with a textbox (TexBox8 normally).

    If you already have an initialization code for your UserForm, add the line: 
    Me.TextBox8.Value = Application.WorksheetFunction.Max(Sheets("Clients").Columns(1)) + 1


    Otherwise add this initialization code: 
    Private Sub UserForm_Initialize() Me.textbox8.Value = Application.WorksheetFunction.Max(Sheets("Clients").Columns(1)) + 1 End Sub


    And adapt the code of the CommandButton1: 
    Range("A" & L).Value = TextBox8.Value


    --
    See you later,
    ThauTheme
    0