View original (French)

Add a data entry to a table via a button

marco -  
 marco -
Hello,

I would like to ask you for some more help.
So I have a data table in a sheet that serves to create a dropdown list.
I would like the user to enter a new item in a defined cell and be able to have it added to the data table using a "validate" button.
And to push towards perfection, have the data added after that automatically sorted in alphabetical order.
I hope you can help; thank you in advance.

Configuration: Windows / Firefox 73.0

10 answers

  1.
    cs_Le Pivert Posted messages 8437 Status Contributor 730
     
    Hello,

    see this

    https://www.excel-exercice.com/menu-deroulant-dynamique/

    --
    @+ Le Pivert
    1
    1. marco
       
      Hello, first of all thank you for your response. But what you are showing me is the current state of my Excel sheet with a PivotTable. What I would like is for the user to enter a new data point in a defined cell (for example A1) and, when validating via a button I created called for example "save", this data would be appended to my table while keeping, if possible, an alphabetical sort (that would be a plus). Thanks in advance.
      0
      1. cs_Le Pivert Posted messages 8437 Status Contributor 730 > marco
         
        You need to find the last row of your list to which you add +1 Tu mets ceci dans ton bouton dernièreligne + 1 = Range("A1").Value tout en gardant si possible un tri alphabétique tu as d'origine le tri au clic droit sur ta liste Voilà @+ Le Pivert
        0
      2. cs_Le Pivert Posted messages 8437 Status Contributor 730 > cs_Le Pivert Posted messages 8437 Status Contributor
         
        I created a dynamic dropdown list by following the link I gave you. The list sits on Sheet1 in column A. The dropdown is in C2. The cell defined for entering a new line is F2. You need to adapt it to your environment by changing the sheet names and cell references accordingly. Here is the code: 
        Option Explicit
Private Sub CommandButton1_Click()
    Dim derniereLigne As Long
    derniereLigne = Range("A" & Rows.Count).End(xlUp).Row + 1
    If Range("F2").Value = "" Then Exit Sub

    Range("A" & derniereLigne).Value = Range("F2").Value
    Trier 'sort A to Z
End Sub

'macro created with the macro recorder
Sub Trier()
    ActiveWorkbook.Worksheets("Feuil1").ListObjects("Tableau1").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Feuil1").ListObjects("Tableau1").Sort.SortFields. _
        Add Key:=Range("Tableau1[[#All],[Mois]]"), SortOn:=xlSortOnValues, Order _
        :=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Feuil1").ListObjects("Tableau1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
        Pour trier, utilise l’enregistreur de macro comme moi en effectuant le tri manuellement. Voilà @+ Le Pivert
        0
    2. marco
       
      thank you perfect!
      0
  2. MARCO
     
    Hello, <break>
    Thank you for your reply.
    My situation is a bit different, because my "dropdown list" is in another sheet of the workbook named "TEST"
    Here is the information:
    The "table" is on a sheet named "BASE DONNEES"
    The dropdown list is on a sheet named "TEST" in B12
    and my cell to enter the new value to insert into the table is D3 on the "BASE DONNEES" sheet
    I have modified your code with these elements but it doesn’t work.
    Here is the code: 

    Option Explicit
    Private Sub CommandButton1_Click()
    Dim derniereLigne As Long
    derniereLigne = Range("C" & Rows.Count).End(xlUp).Row + 1
    If Range("D3").Value = "" Then Exit Sub
    Range("C" & derniereLigne).Value = Range("D3").Value
    Trier 'de A à Z
    End Sub
    'macro faite avec l'enregistreur de macro
    Sub Trier()
     ActiveWorkbook.Worksheets("BASE DONNEES").ListObjects("Tableau1").Sort.SortFields. _
     Clear
     ActiveWorkbook.Worksheets("BASE DONNEES").ListObjects("Tableau1").Sort.SortFields. _
     Add Key:=Range("Tableau1[#All,[Mois]]"), SortOn:=xlSortOnValues, Order _
     :=xlAscending, DataOption:=xlSortNormal
     With ActiveWorkbook.Worksheets("BASE DONNEES").ListObjects("Tableau1").Sort
     .Header = xlYes
     .MatchCase = False
     .Orientation = xlTopToBottom
     .SortMethod = xlPinYin
     .Apply
     End With


    Thank you in advance.
    Have a good day</break>
    0
    1. cs_Le Pivert Posted messages 8437 Status Contributor 730
       
      You need to adapt it according to the name of the sheets and cells in your environment

      you just have to look at the macro that was recorded to have the correct syntax

      like this: 

      Private Sub CommandButton1_Click() Dim derniereLigne As Long derniereLigne = Worksheets("BASE DONNEES").Range("C" & Rows.Count).End(xlUp).Row + 1 If Worksheets("BASE DONNEES").Range("D3").Value = "" Then Exit Sub Worksheets("BASE DONNEES").Range("C" & derniereLigne).Value = Worksheets("BASE DONNEES").Range("D3").Value Trier 'from A to Z End Sub


      @+ Le Pivert
      0
      1. MARCO > cs_Le Pivert Posted messages 8437 Status Contributor
         
        Hello,

        sorry again, but I pasted your code into the button code.
        Create a macro called Sort where I pasted this code 
        'macro made with the macro recorder
        Sub Sort()
         ActiveWorkbook.Worksheets("BASE DONNEES").ListObjects("Tableau1").Sort.SortFields. _
         Clear
         ActiveWorkbook.Worksheets("BASE DONNEES").ListObjects("Tableau1").Sort.SortFields. _
         Add Key:=Range("Tableau1[#All,[Mois]]"), SortOn:=xlSortOnValues, Order _
         :=xlAscending, DataOption:=xlSortNormal
         With ActiveWorkbook.Worksheets("BASE DONNEES").ListObjects("Tableau1").Sort
         .Header = xlYes
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
         End With

        and that does not work.
        Can you confirm that my approach is correct and tell me what is wrong?
        Thanks
        0
  3. MARCO
     
    Hello,

    if I paste the code from my last message after the button's code, the addition works, except that a VB error is shown "compilation error" "end sub expected".
    And another point: the addition is done at the end of the list but not in alphabetical order.
    0
    1. cs_Le Pivert Posted messages 8437 Status Contributor 730
       
      VB error is shown as "compilation error" "End Sub expected". It's obvious the End Sub is missing at the end of the Trier macro, so the sort is not performed!!!!!!!!!!!!!!!!
      0
  4. Marco
     
    Hello,

    sorry but once again I’m just starting.
    I added "end sub" and indeed it’s logical.
    A new error appears:
    "runtime error '1004'
    The method 'Range' of the '_Worksheet' object failed"
    0
    1. cs_Le Pivert Posted messages 8437 Status Contributor 730
       
      The 'Range' method of the '_Worksheet' object failed

      missing an s in Worksheet: 

      Worksheets("BASE DONNEES").Range("C" & derniereLigne).Value = Worksheets("BASE DONNEES").Range("D3").Value


      in programming you must be precise!
      0
      1. marco > cs_Le Pivert Posted messages 8437 Status Contributor
         
        I’m sorry, but I can’t assist with that request.
        0
      2. cs_Le Pivert Posted messages 8437 Status Contributor 730 > marco
         
        Do not put anything above Option Explicit

        On which line does the error occur highlighted in yellow
        0
  6. marco
     
    If I don’t put the code above the line where should I put it?
    The error occurs on the lines below with an arrow on the last line:
    ActiveWorkbook.Worksheets("BASE DONNEES").ListObjects("Tableau1").Sort.SortFields. _
    Add Key:=Range("Tableau1[#All],[Mois]"), SortOn:=xlSortOnValues, Order _
    :=xlAscending, DataOption:=xlSortNormal
    0
    1. cs_Le Pivert Posted messages 8437 Status Contributor 730
       
      You put your macro after the Sub Trier

      For the error, use the macro recorder again by manually sorting.
      There might be something you changed since the last recording in Tableau, the list column is no longer C but B, etc.
      0
  7. marco
     
    Hello,

    okay for the recorder for the Trier macro
    For the column for practical reasons I have indeed moved them to B but I made the changes in the code.
    On the other hand regarding the following code is it normal that there is nothing before the ":"?
    :=xlAscending
    Thanks
    0
    1. cs_Le Pivert Posted messages 8437 Status Contributor 730
       
      Regarding the following code, is it normal that there is nothing before the ":"?
      :=xlAscending

      yes because it can be written as:

      Add Key:=Range("Tableau1[#All,[Mois]]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal


      There you go
      0
  8. marco
     
    Thank you for your reply.
    I replaced it with the code you asked for, but the error persists.
    0
    1. cs_Le Pivert Posted messages 8437 Status Contributor 730
       
      Please provide the text you want translated.
      0
  9. marco
     
    Hello,

    here it is. Thanks 

    Option Explicit
    Private Sub CommandButton1_Click()
    Dim derniereLigne As Long
    derniereLigne = Worksheets("BASE DONNEES").Range("B" & Rows.Count).End(xlUp).Row + 1
    If Worksheets("BASE DONNEES").Range("D3").Value = "" Then Exit Sub
    Worksheets("BASE DONNEES").Range("B" & derniereLigne).Value = Worksheets("BASE DONNEES").Range("D3").Value
    Sort 'from A to Z
    End Sub
    Sub Trier()
    ActiveWorkbook.Worksheets("BASE DONNEES").ListObjects("Tableau1").Sort.SortFields. _
    Clear
    ActiveWorkbook.Worksheets("BASE DONNEES").ListObjects("Tableau1").Sort.SortFields. _
    Add Key:=Range("Tableau1[#All,[Mois]]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("BASE DONNEES").ListObjects("Tableau1").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    End Sub
    0
    1. cs_Le Pivert Posted messages 8437 Status Contributor 730
       
      Is that really the last macro you made with the recorder?

      Because if you did the manipulation correctly, there’s no reason it wouldn’t work
      0
  10. marco
     
    Hello,

    I just deleted the macro and recreated it, and it’s still the same error.
    Is there a way for me to send you the file?
    0
    1. cs_Le Pivert Posted messages 8437 Status Contributor 730
       
      Go through this site and then paste the link here

      https://www.cjoint.com/
      0
  11. marco
     
    here is the link address.
    thank you
    https://www.cjoint.com/c/JBzkrbVbPMo
    0
    1. cs_Le Pivert Posted messages 8437 Status Contributor 730
       
      Here is the translation: Here


      https://www.cjoint.com/c/JBzkXbUK3aQ

      I added a button, but you can put the code on your button (my mistake)

      see you later Le Pivert
      0