View original (French)

Automatic number userform vba

tuxy88 Posted messages 11 Status Member -  
tuxy88 Posted messages 11 Status Member -
Hello everyone,

I know my question has already been addressed, but I can't seem to apply it to my file (after countless attempts!). This is probably due to my inexperience in VBA!!! But let's get to the subject of my post.

I want to insert an automatic number on a userform when this userform is opened. This number would be the logical continuation of the one used previously. For now, I am copying data from my userform to my Excel sheet.

Here is my code (the incremented number would be visible in TextBox1 and coming from the Préparation sheet)

Private Sub CommandButton1_Click()
Worksheets("Préparation").Rows("2:2").Insert
Worksheets("Préparation").Cells(2, 1) = TextBox1
Worksheets("Préparation").Cells(2, 2) = nom
Worksheets("Préparation").Cells(2, 3) = dat
Worksheets("Préparation").Cells(2, 4) = heure
Worksheets("Préparation").Cells(2, 5) = venue
Worksheets("Préparation").Cells(2, 6) = dcolorant1
Worksheets("Préparation").Cells(2, 7) = ncolorant1
Worksheets("Préparation").Cells(2, 8) = dosec1
Worksheets("Préparation").Cells(2, 9) = dcolorant2
Worksheets("Préparation").Cells(2, 10) = ncolorant2
Worksheets("Préparation").Cells(2, 11) = dosec2
Worksheets("Préparation").Cells(2, 12) = dcolorant3
Worksheets("Préparation").Cells(2, 13) = ncolorant3
Worksheets("Préparation").Cells(2, 14) = dosec3
Worksheets("Préparation").Cells(2, 15) = darome1
Worksheets("Préparation").Cells(2, 16) = narome1
Worksheets("Préparation").Cells(2, 17) = dosea1
Worksheets("Préparation").Cells(2, 18) = darome2
Worksheets("Préparation").Cells(2, 19) = narome2
Worksheets("Préparation").Cells(2, 20) = dosea2
Worksheets("Préparation").Cells(2, 21) = darome3
Worksheets("Préparation").Cells(2, 22) = narome3
Worksheets("Préparation").Cells(2, 23) = dosea3
UserForm2.PrintForm
Unload Me
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Thank you in advance for any responses
Best regards
Tuxy88

6 answers

  1. foo
     
    Hello

    give at least one example to see how to put your numbering

    and see the changes to be made on your UserForm

    See you later
    Maurice
    0
    1. tuxy88 Posted messages 11 Status Member
       
      Thank you for your interest in my post.
      I don't know how to attach my file :/

      See you+
      0
  2. foo
     
    Hello

    Upload your file at http://www.cjoint.com and paste the link provided here.

    See you later
    Maurice
    0
  4. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Good evening,

    try with this: 
    Private Sub UserForm_Initialize()
     TextBox1 = Worksheets("Preparation").Cells(2, 1) + 1
    End Sub
    eric

    --
    1) In addition to saying thank you (and yes, it is done !!!), remember to mark as resolved (at the top next to your title) when it's the case. 2) You should never respond to an unsolicited private message...
    Well, that's done.
    0
  6. foo
     
    Hello

    Congratulations eriiic
    here is my modification

    Private Sub UserForm_Initialize()
    TextBox1.Value = Sheet1.Cells(2, 1) + 1
    TextBox1.Enabled = False
    End Sub

    Private Sub CommandButton1_Click()
    ' Sheet1 = CodeName (Preparation)
    With Sheet1
    .Rows("2:2").Insert
    .Cells(2, 1) = TextBox1
    .Cells(2, 2) = name
    .Cells(2, 3) = date
    .Cells(2, 4) = time
    .Cells(2, 5) = venue
    .Cells(2, 6) = colorant1
    .Cells(2, 7) = colorant1_name
    .Cells(2, 8) = dose1
    .Cells(2, 9) = colorant2
    .Cells(2, 10) = colorant2_name
    .Cells(2, 11) = dose2
    .Cells(2, 12) = colorant3
    .Cells(2, 13) = colorant3_name
    .Cells(2, 14) = dose3
    .Cells(2, 15) = aroma1
    .Cells(2, 16) = aroma1_name
    .Cells(2, 17) = dosea1
    .Cells(2, 18) = aroma2
    .Cells(2, 19) = aroma2_name
    .Cells(2, 20) = dosea2
    .Cells(2, 21) = aroma3
    .Cells(2, 22) = aroma3_name
    .Cells(2, 23) = dosea3
    End With
    UserForm2.PrintForm
    Unload Me
    End Sub

    See you
    Maurice
    0
  7. tuxy88 Posted messages 11 Status Member
     
    Hello everyone,

    Thank you for your answers, it's great, I can do what I want now ;)

    New questions, I would now like that when I enter a number in TextBox1 of a userform, that this number is searched in column A of sheet 1 and thus, to put the data from my userform (from TextBox2, TextBox3,...) into columns H, I,... of the same sheet 1 for example
    This in order to align on the same Excel sheet the preparation and use of a mixture called 1 for example.

    I don't know if this is possible :/

    Thank you in advance

    Sincerely,

    Pierrick
    0