Showing last data entry on the top of listbox

[p9326]

Hello friends, I would like to seek some help here, I have a user form with Listbox, I want to show on the top of the listbox the new data , when he hit the command button submit.


Here are my code, can someone can modify?

Thanks in advance

-Module
Sub Submit()
Dim Sh As Worksheet
Dim iRow As Long
Set Sh = ThisWorkbook.Sheets("Database")
iRow = [Counta(Database!A:A)] + 1
With Sh
.Cells(iRow, 1) = iRow - 1
.Cells(iRow, 2) = frmForm.txtName.Value
.Cells(iRow, 3) = frmForm.txtID.Value
.Cells(iRow, 4) = [Text(Now(),"YYYY-MM-DD HH:MM:SS")
End With
End Sub





Private Sub cmdSubmit_Click()

Dim msgvalue As VbMsgBoxResult

msgvalue = MsgBox("Do you want to save the data?", vbYesNo + vbInformation, "Confirmation")

If msgvalue = vbNo Then Exit Sub

If txtName.Value = "" Or txtID.Value = "" Then
If msgvalue = MsgBox("Data not complete!", vbYesNo + vbQuestion + vbYesNo) <> vbYes Then
Exit Sub
End If
End If
txtName.SetFocus
With Me.lstDatabase
.RowSource = .RowSource 'Forces update if data added or deleted from Data range after Userform is shown
.Selected(.ListCount - 1) = True 'Selects newentry item in the ListBox
End With


Call Submit
Call Reset


End Sub

 

[nemmi69]

Do you mean to add this data to the top of existing data?

 

[p9326]

I mean when I enter new data to the Listbox it goes to the top even though the number is descending, 3,2,1 something like that.
Please see my attached image and the data on the Listbox, number 1 & 2 with same date of entry, I want my data number 3 on the top when i click the submit button.

 

[nemmi69]

This is just an example of putting an item at the top of a list in a Listbox. "InitializeListbox" just there to add some items to the listbox. "AddAtTopListbox" does the work.

Dim Wb As Workbook
Dim Ws  As Worksheet
Dim AValue As Variant
Dim LastRow As Long
Dim ALoop As Long

Sub InitializeListbox()
Set Wb = ThisWorkbook
Set Ws = Wb.ActiveSheet

LastRow = Ws.Cells(Rows.Count, 1).End(xlUp).Row
Worksheets(Ws.Name).ListBox1.Clear
For ALoop = 1 To LastRow
    AValue = Ws.Cells(ALoop, "A").Value
    Worksheets(Ws.Name).ListBox1.AddItem Ws.Cells(ALoop, "A").Value
Next ALoop
End Sub

Sub AddAtTopListbox()
Dim TArray()

'To put new value on top *****
ReDim TArray(0)
'Set temporary array with new value
TArray(0) = Ws.Cells(1, "B").Value
' Loop through listbox items and add to array
For ALoop = 0 To LastRow - 1
    ReDim Preserve TArray(ALoop + 1)
    TArray(ALoop + 1) = Worksheets(Ws.Name).ListBox1.List(ALoop)
Next ALoop
'Clear listbox and add temporary array
Worksheets(Ws.Name).ListBox1.Clear
Worksheets(Ws.Name).ListBox1.List = TArray

End Sub

 

[nemmi69]

Ignore my previous. I just discovered you can declare where you want the dat added. By default its the last but you can do -

.AddItem "Some Text", 0

 

[p9326]

@nemmi69 thank you for the effort, but doesn't work

 

[nemmi69]

are you adding multiple columns?

 

[p9326]

are you adding multiple columns?

More than 30 columns a day. That's why I need to make it on the top when they enter new data on my userform listbox, from highest to lowest serial number

 

[nemmi69]

If you want to Insert the item at a certain position you can use the second parameter. Keep in mind that this is a zero-based position, so if you want the item in position one then the value is 0, position 2 the value is 1, and so on.

With ListBox1
    .AddItem "Apple"
    .AddItem "Orange"
    
    ' Add "Banana" to position 1(Index 0)
    .AddItem "Banana", 0
End With

If you want to add multiple columns with AddItem then you need to use the List property after you use AddItem:

With listboxFruit
    .List = myRange.Value
    .AddItem "Banana"
    
    ' Add to the second column of 'Banana' row
    .List(2, 1) = "$2.99"
 End With