View original (French)

VBA

Solved
Jenni -  
 Jenni -

Hello,

With the aid of a tutorial, I copied a macro that works well, my form fills in correctly, but it completes the 1st tab of my workbook. However, I have several sheets in my Excel workbook and I am trying to fill a table named Liste_candidat on the sheet named Sheet1 (CANDIDATS). I tried several times by changing the variables in my code, but there is always something that doesn't work. Can someone help?

Thanks in advance!

Private Sub BtAjouter_Click() Dim onglet As Worksheet Dim derniere_ligne As Long Dim No As Long Set onglet = Worksheets(CANDIDATS - RH) derniere_ligne = onglet.Cells(Rows.Count, 1).End(xlUp).Row If dernere_ligne = 1 Then No = 1 Else No = onglet.Cells(derniere_ligne, 1) + 1 End If With onglet .Cells(derniere_ligne + 1, 1) = No .Cells(derniere_ligne + 1, 2) = tbxNom .Cells(derniere_ligne + 1, 3) = tbxCourriel .Cells(derniere_ligne + 1, 4) = TbxTel .Cells(derniere_ligne + 1, 5) = TbxVille .Cells(derniere_ligne + 1, 6) = TbxSecteur .Cells(derniere_ligne + 1, 7) = BoxChoix1 .Cells(derniere_ligne + 1, 8) = BoxChoix2 .Cells(derniere_ligne + 1, 9) = BoxChoix3 .Cells(derniere_ligne + 1, 10) = BoxEntretien .Cells(derniere_ligne + 1, 11) = TbxDate .Cells(derniere_ligne + 1, 12) = TbxDoc .Cells(derniere_ligne + 1, 13) = BoxStatut .Cells(derniere_ligne + 1, 14) = TbxQualification .Cells(derniere_ligne + 1, 15) = TbxQuestionnaire .Cells(derniere_ligne + 1, 16) = TbxRemarque .Cells(derniere_ligne + 1, 17) = BoxDispo End With End Sub

10 answers

  1.
    jordane45 Posted messages 30426 Registration date   Status Moderator Last intervention   4 830
     

    Hello,

    To begin

    Set onglet = Worksheets("CANDIDATS - RH")

    .
    Best regards,
    Jordane

    1
    1. Jenni
       

      Of course!! I couldn't see clearly anymore, it looks like it. Thank you very much!

      And if I want the information to be entered into my structured table named Liste_candidat, do you also know how? ;)

      0
  2. thev Posted messages 2005 Registration date   Status Member Last intervention   721
     

    To finish, just in case, code for deletion

     Private Sub BtSupprimer_Click() Dim i As Long 'search index line in table from the value of the Listbox (no) With [Liste_candidats].ListObject i = 0 On Error Resume Next i = Application.Match(ListBox1.Value, .ListColumns(1).DataBodyRange, 0) On Error GoTo 0 If i = 0 Then MsgBox "aucune ligne à supprimer": Exit Sub 'deletion .ListRows(i).Delete 'reload Listbox ListBox1.Clear If .ListRows.Count > 0 Then ListBox1.List = .DataBodyRange.Value End With End Sub
    1
  3. thev Posted messages 2005 Registration date   Status Member Last intervention   721
     

    Good evening,

    below is the code for your structured table Liste_candidat :

    Private Sub BtAjouter_Click() Dim ligne As ListRow Dim i As Long, no As Long With [Liste_candidat].ListObject Set ligne = .ListRows.Add: i = ligne.Index 'add row to the end of the table no = Application.Max(.ListColumns(1).DataBodyRange.Value) + 1 'number calculation .ListColumns(1).DataBodyRange(i) = no .ListColumns(2).DataBodyRange(i) = tbxNom .ListColumns(3).DataBodyRange(i) = tbxCourriel .ListColumns(4).DataBodyRange(i) = TbxTel .ListColumns(5).DataBodyRange(i) = TbxVille .ListColumns(6).DataBodyRange(i) = TbxSecteur .ListColumns(7).DataBodyRange(i) = BoxChoix1 .ListColumns(8).DataBodyRange(i) = BoxChoix2 .ListColumns(9).DataBodyRange(i) = BoxChoix3 .ListColumns(10).DataBodyRange(i) = BoxEntretien .ListColumns(11).DataBodyRange(i) = TbxDate .ListColumns(12).DataBodyRange(i) = TbxDoc .ListColumns(13).DataBodyRange(i) = BoxStatut .ListColumns(14).DataBodyRange(i) = TbxQualification .ListColumns(15).DataBodyRange(i) = TbxQuestionnaire .ListColumns(16).DataBodyRange(i) = TbxRemarque .ListColumns(17).DataBodyRange(i) = BoxDispo End With End Sub
    0
    1. Jenni
       

      Oh wow thanks a lot!

      However a message appears "The 'Add' method of the 'ListRows' object failed.

      I’ve done some research to try to understand, but I can’t seem to figure it out.

      0
  4. thev Posted messages 2005 Registration date   Status Member Last intervention   721
     

    However a message appears: "The method 'Add' of the 'ListRows' object failed."

    If you see this message, it means your structured table name is not exactly Liste_candidat. Perhaps there is a space after the t?

    Check the name of your table by selecting it in full.

    0
  6. Jenni
     

    Indeed, a missing "s" was present, but now the application closes automatically after displaying the error message.

    0
  7. Jenni
     

    Finally, after several tries, it seems to work, other times not, everything closes and I don’t understand why.

    0
  8. thev Posted messages 2005 Registration date   Status Member Last intervention   721
     

    Check that your "Liste_candidats" table is not used in the RowSource property of a ComboBox or a ListBox (this property can only be used for an unmodified array at runtime)

    If not, attach an anonymized file via https://www.cjoint.com/

    0
    1. Jenni
       

      Yes, that's right, I inserted a list to be able to visualize the candidates already added so I can modify them later, so I understand that I can't do it this way.

      I will continue to look at how to do this for the next steps. At least the addition of candidates works now, thank you for your valuable time!!

      0
  9. thev Posted messages 2005 Registration date   Status Member Last intervention   721
     

    If you want to insert, for example, a ListBox to view the candidates, you must then use the List property at runtime.

    Below is code displaying in a ListBox the numbers and names of the candidates

    Option Explicit Private Sub UserForm_Initialize() ListBox1.ColumnCount = 2 With [Liste_candidats].ListObject If .ListRows.Count > 0 Then ListBox1.List = .DataBodyRange.Value End With End Sub Private Sub BtAjouter_Click() Dim ligne As ListRow Dim i As Long, no As Long Dim ctrl As Control With [Liste_candidats].ListObject Set ligne = .ListRows.Add: i = ligne.Index 'add row at the end of the table no = Application.Max(.ListColumns(1).DataBodyRange.Value) + 1 'calculate number .ListColumns(1).DataBodyRange(i) = no .ListColumns(2).DataBodyRange(i) = tbxNom .ListColumns(3).DataBodyRange(i) = tbxCourriel .ListColumns(4).DataBodyRange(i) = TbxTel .ListColumns(5).DataBodyRange(i) = TbxVille .ListColumns(6).DataBodyRange(i) = TbxSecteur .ListColumns(7).DataBodyRange(i) = BoxChoix1 .ListColumns(8).DataBodyRange(i) = BoxChoix2 .ListColumns(9).DataBodyRange(i) = BoxChoix3 .ListColumns(10).DataBodyRange(i) = BoxEntretien .ListColumns(11).DataBodyRange(i) = TbxDate .ListColumns(12).DataBodyRange(i) = TbxDoc .ListColumns(13).DataBodyRange(i) = BoxStatut .ListColumns(14).DataBodyRange(i) = TbxQualification .ListColumns(15).DataBodyRange(i) = TbxQuestionnaire .ListColumns(16).DataBodyRange(i) = TbxRemarque .ListColumns(17).DataBodyRange(i) = BoxDispo ListBox1.List = .DataBodyRange.Value End With '// reset of the textboxes For Each ctrl In Me.Controls If TypeOf ctrl Is MSForms.TextBox Then ctrl = Empty Next ctrl End Sub
    0
    1. Jenni
       

      Wonderful !! It works perfectly well. A big thank you to you. :)

      0
  10. Jenni
     

    And is it really complicated if I want to display the information of a selected candidate in my form so that I can modify it later?

    0
  11. thev Posted messages 2005 Registration date   Status Member Last intervention   721
     

    Non. below is an example of code :

    Option Explicit Private Sub UserForm_Initialize() ListBox1.ColumnCount = 2 With [Liste_candidats].ListObject If .ListRows.Count > 0 Then ListBox1.List = .DataBodyRange.Value End With End Sub Private Sub ListBox1_Click() Dim i As Long With [Liste_candidats].ListObject 'search row index in the table from the Listbox value (no) i = Application.Match(ListBox1.Value, .ListColumns(1).DataBodyRange, 0) 'display selected row tbxNom = .ListColumns(2).DataBodyRange(i) tbxCourriel = .ListColumns(3).DataBodyRange(i) TbxTel = .ListColumns(4).DataBodyRange(i) TbxVille = .ListColumns(5).DataBodyRange(i) TbxSecteur = .ListColumns(6).DataBodyRange(i) BoxChoix1 = .ListColumns(7).DataBodyRange(i) BoxChoix2 = .ListColumns(8).DataBodyRange(i) BoxChoix3 = .ListColumns(9).DataBodyRange(i) BoxEntretien = .ListColumns(10).DataBodyRange(i) TbxDate = .ListColumns(11).DataBodyRange(i) TbxDoc = .ListColumns(12).DataBodyRange(i) BoxStatut = .ListColumns(13).DataBodyRange(i) TbxQualification = .ListColumns(14).DataBodyRange(i) TbxQuestionnaire = .ListColumns(15).DataBodyRange(i) TbxRemarque = .ListColumns(16).DataBodyRange(i) BoxDispo = .ListColumns(17).DataBodyRange(i) End With End Sub Private Sub BtAjouter_Click() Dim ligne As ListRow Dim i As Long, no As Long 'insertion ligne With [Liste_candidats].ListObject Set ligne = .ListRows.Add: i = ligne.Index 'add row at the end of the table no = Application.Max(.ListColumns(1).DataBodyRange.Value) + 1 'calculate number .ListColumns(1).DataBodyRange(i) = no End With 'update the table maj_tableau i End Sub Private Sub BtModifier_Click() Dim i As Long 'search row index in the table from the Listbox value (no) With [Liste_candidats].ListObject i = 0 On Error Resume Next i = Application.Match(ListBox1.Value, .ListColumns(1).DataBodyRange, 0) On Error GoTo 0 If i = 0 Then MsgBox "aucune ligne à modifier": Exit Sub End With 'update the table maj_tableau i End Sub Private Sub maj_tableau(i As Long) Dim ctrl As Control With [Liste_candidats].ListObject 'fill the table .ListColumns(2).DataBodyRange(i) = tbxNom .ListColumns(3).DataBodyRange(i) = tbxCourriel .ListColumns(4).DataBodyRange(i) = TbxTel .ListColumns(5).DataBodyRange(i) = TbxVille .ListColumns(6).DataBodyRange(i) = TbxSecteur .ListColumns(7).DataBodyRange(i) = BoxChoix1 .ListColumns(8).DataBodyRange(i) = BoxChoix2 .ListColumns(9).DataBodyRange(i) = BoxChoix3 .ListColumns(10).DataBodyRange(i) = BoxEntretien .ListColumns(11).DataBodyRange(i) = TbxDate .ListColumns(12).DataBodyRange(i) = TbxDoc .ListColumns(13).DataBodyRange(i) = BoxStatut .ListColumns(14).DataBodyRange(i) = TbxQualification .ListColumns(15).DataBodyRange(i) = TbxQuestionnaire .ListColumns(16).DataBodyRange(i) = TbxRemarque .ListColumns(17).DataBodyRange(i) = BoxDispo 'loading Listbox ListBox1.List = .DataBodyRange.Value End With '// resetting the textboxes For Each ctrl In Me.Controls If TypeOf ctrl Is MSForms.TextBox Then ctrl = Empty Next ctrl End Sub
    0
    1. Jenni
       

      Wow, I thank you so much, it works wonderfully once again!!! I've been on this project for several weeks trying to do it alone, but your help allowed me to finally launch this project! Thank you again and have a good evening!

      0
    2. Jenni
       

      I have one last question, I’m trying to retrieve the data entered in the textbox tbxNom to paste it into my code that sends an email, but I’m missing something.

      Sub EnvoyerCourrielNouveauCandidat() Dim LeMail As Variant Set LeMail = CreateObject("Outlook.Application") With LeMail.CreateItem(olMailItem) .Subject = "Séquence d'embauche - Nouveau candidat ajouté" .To = .htmlbody = "Bonjour" & ",<br><br>" & _ "Un candidat a été ajouté :" & "<br><br>" & _ ".FrmCandidats.tbxNom.Value" & "<br><br>" & _ "Belle journée!" .send End With Range("E6").Select End Sub

      Merci encore.

      0
    3. thev Posted messages 2005 Registration date   Status Member Last intervention   721 > Jenni
       

      Try this code

       Sub SendNewCandidateEmail() Dim OlkApp As Object Set OlkApp = CreateObject("Outlook.Application") With OlkApp.CreateItem(0) .Subject = "Hiring sequence - New candidate added" .To = .HTMLBody = "Hello" & ",<br><br>" & _ "A candidate has been added :" & "<br><br>" & _ Me.FrmCandidats.tbxNom.Value & "<br><br>" & _ "Have a nice day!" .Send End With Range("E6").Select End Sub
      0
    4. Jenni > thev Posted messages 2005 Registration date   Status Member Last intervention  
       

      I had to put Me.FrmCandidats.tbxNom.Value in quotes or it indicated Incorrect use of the keyword Me and here is the result:

      Hello,

      A candidate has been added :

      Me.FrmCandidats.tbxNom.Value

      Have a nice day!

      0
    5. thev Posted messages 2005 Registration date   Status Member Last intervention   721 > Jenni
       

      Put your SendMail procedure in your UserForm’s code or replace Me with your UserForm’s name.

      0