add data, sort data A-Z, populate combobox

  • Hi. I have this code which works almost fine:



    1. New data entry via "TextBox1" to a range named "glist".
    2. Sort items A-Z in extended "glist"
    3. Populate "ComboBox1"


    Error on 2nd and 3rd : Sorting items and populating combobox from one step behind. First data entry isnt sorted until I enter second data.


    Thanks in advance.


  • Re: add data, sort data A-Z, populate combobox


    Try this. :cool:


    Code
    Private Sub CommandButton1_Click()
        
        Range("glist").Offset(Range("glist").Rows.Count, 0).Resize(1, 1).Value = TextBox1.Value  ' dynamic defined range, scope:workbook, Column "J"
         
        With Range("glist")
              .Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
             ComboBox1.RowSource = .Address(external:=True)
        End With
    End Sub
  • Re: add data, sort data A-Z, populate combobox


    acell isn't going anywhere so why do we need to create a variable and run unnecessary lines of code and use up memory for it?


    When you set rng in your code, it's set to whatever glist is when that line of code was run.


    Since you added the text box value below what glist was after you ran the set line, rng did not include the added value.


    Setting rng to glist doesn't mean it updates when glist updates, it doesn't.


    Yes your dynamic range updates, but you had already set rng to what it was before it was updated.


    In my code I updated glist before running the with range("glist") code, therefore it was with the updated value and there's no reason to create a variable, you already have a name for the range, why not use it?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!