add new item to dynamic list, sort list and show in listbox

[mdavid]

Hi,
I have a userform with a listbox that is populated from a dynamic named list (range: G2:H69).
The userform also has a TextBox1 and CommandBtn1, when a value is entered into TextBox1 and CommandBtn1 is clicked I need to add the value to column G, sort the new range (G2:H70) alphabetically and refresh the list box so it includes the new item.
Really appreciate any code snippets showing how I can do this.

Thanks
David

 

[Hui]

it will be something like this:

Sub Update_ListBox()

Sheet1.Activate
' Find last row
Dim lr As Long
lr = Range("G" & Rows.Count).End(xlUp).Row

' Add value to range
Range("G" & CStr(lr + 1)).Value = Textbox1.Text

' Sort range
Range("G2:H" & CStr(lr + 1)).Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
  Key:=Range("G1"), _
  SortOn:=xlSortOnValues, _
  Order:=xlAscending, _
  DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("G2:H" & CStr(lr + 1))
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

' Add range to llistbox
ListBox1.RowSource = Range("G2:H" & CStr(lr + 1)).Address

End Sub

 

[mdavid]

Hi Hui,
Thanks very much for this, much appreciated.
One question - the listbox RowSource is "=ActionsList", and the Names Manager Refers to: "=Lists!$G$2:$H$69", do I need to update this ActionList also?

Thanks for your help
David

 

[Hui]

Action list is a named formula
Goto the Name Manager

 

[mdavid]

Hi Hui, Sorry don't understand - can't the named list also be updated in the code - my excel users are not to savvy with Name Manager.

Thanks
David

 

[mdavid]

Please can you explain how to Resize the RefersToRange to add extra rows to the named list.
I can add to the range with your code, but the listbox remains the same size so every time I add an item, an item disappears from the end.
Thanks for your help
David

 

[Hui]

I am making assumptions about what you are dong with minimal input

Can you please attach the file?

This code sets up or updates a Named Formula

' Setup Named formula
ActiveWorkbook.Names.Add Name:="ActionsList", RefersTo:="=" & CStr(Range("G2:H" & CStr(lr + 1)).Address)

 

[mdavid]

Hi Hui,
This is what worked for me in the end:

With ThisWorkbook.Names.Item("ActionsList")
    .RefersTo = .RefersToRange.Resize(lr, 2)
End With

Thanks for your time
David