Excel Form Listbox VBA: how to sort last date and time entry

Question

I want to ask help about this code I have here:

Private Sub CommandButton1_Click()
 Dim Row As Long
 Row = ThisWorkbook.Sheets("ExcelEntryDB").Cells(Rows.Count, "C").End(xlUp).Row
 Me.ListBox1.ColumnCount = 3
 Me.ListBox1.ColumnHeads = True
 Me.ListBox1.ColumnWidths = "75;75;75"

 If Row > 1 Then
  Me.ListBox1.Rowsource = "ExcelEntryDB!C2:E" & Row
 Else
  Me.ListBox1.Rowsource = "ExcelEntryDB!C2:E2" & Row
 End If

 Dim sh As Worksheet
 Set sh = 
 ThisWorkbook.Sheets("ExcelEntryDB")
 Dim n As Long

 n = sh.Range("C" & Application.Rows.Count).End(xlUp).Row
     sh.Range("C" & n + 1).Value = Format(Date, "mm/dd/yyyy")
   sh.Range("D" & n + 1).Value = Format(Time, "hh:nn:ss" AM/PM)
   sh.Range("E" & n + 1).Value = Me.TextBox3.Value

 Me.TextBox3.Value = ""

End Sub

The code above works perfectly fine. The listbox displays the entries. However, listbox does not display the recent entry as the first row to appear at the very top below the headers.

It goes to the bottom which makes me scroll more down. I have another code that I tried inserting in the If condition:

Range("C:E").Sort Key1:=Range("C:C"), Order1:=xlDescending, Header:=xlYes

But I get an error that says:

Sort method Range class failed

Apologies on the code not being indented properly. I am using mobile to type and ask. I am using a laptop to code. However, it is not allowed to connect to the internet during not work hours. If someone could arrange it, it would be very much appreciated.

My question is almost the same with this but I am not that code master enough to digest the codes found in the site. If there could any other code or code that is derived from my codes and rearrange it to display my needed output.

So in short, I would like to see my recent entered data just below the header of my listbox. Thank you.

[SOLVED] BIG THANKS FaneDuru's Code:

Private Sub CommandButton1_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("ExcelEntryDB")
     Dim n As Long

 n = sh.Range("C" & Application.Rows.Count).End(xlUp).Row
     sh.Range("C" & n + 1).Value = Format(Date, "mm/dd/yyyy")
     sh.Range("D" & n + 1).Value = Format(Time, "hh:nn:ss" AM/PM)
     sh.Range("E" & n + 1).Value = Me.TextBox3.Value
 Me.TextBox3.Value = ""

 Me.ListBox1.ColumnCount = 3
 Me.ListBox1.ColumnHeads = True
 Me.ListBox1.ColumnWidths = "75;75;75"

 Me.ListBox1.Rowsource = "ExcelEntryDB!C2:E2" & n

 sortByTimeInTwoColumns()

End Sub
    
Sub sortByTimeInTwoColumns()
  Dim ws As Worksheet, lastR As Long, rngFlt As Range, arrT, filtCol As Long
  
  Set ws = ActiveSheet
  lastR = ws.Range("C" & ws.rows.count).End(xlUp).Row 'last row in C:C
  
  Set rngFlt = ws.Range("C2:E" & lastR) 'the range to be filtered (any column instead of E may be used)
  filtCol = cells(1, Split(rngFlt.address, "$")(3)).column + 1 'extract column after the last in the range to be filtered
  
  'extract the time array by combination between C:B columns:
  arrT = Evaluate(rngFlt.Columns(1).address & "+" & rngFlt.Columns(2).address) 'extract the C:D columns time combination an array
  
  ws.cells(2, filtCol).Resize(UBound(arrT, 1)).Value2 = arrT 'place the time array value in the next empty column
  ws.Range("C2:F" & lastR).Sort key1:=ws.cells(2, filtCol), Order1:=xlDescending, header:=xlNo 'apply descending sorting
  
  ws.cells(2, filtCol).Resize(UBound(arrT, 1)).ClearContents 'clear the helper column content

End Sub

Output:

I am after with colors because I have a small system that needs String entries like Colors. TY

Is "ExcelEntryDB" sheet the active one when run the problematic piece of sorting code? — FaneDuru, Aug 26 '23 at 12:27
Looking to your above code, the header looks to be on the second row. So, please try: `Range("C2:D" & row).Sort key1:=Range("C2"), Order1:=xlDescending, header:=xlYes`. If it does not work I can show the variant of not setting `RowSource`. Loading an array, then using a change event to sort the array extracted from the list box and loaded again. What the mentioned `Save` button does? — FaneDuru, Aug 26 '23 at 12:36
the save button does save the entries to the active excel and shows the listbox upon save. i will be posting images so you will see yes i will try. i will be back in a bit — Shiela, Aug 26 '23 at 19:29
You can make screenshot the following way also. When the screen shows the desired picture press `PrintScreen`. Then you can `Paste` it (the clipboard content) to a picture editor (e.g `Paint`) — Black cat, Aug 27 '23 at 04:25
thank you yes that will be best. but i cannot transfer files like images from my laptop to mobile because it's restricted. that's why im trying my best to show the form by capturing it using mobile — Shiela, Aug 27 '23 at 04:42

FaneDuru · Accepted Answer · 2023-08-27T18:39:23.230

I didn't understand yesterday that your time to be filtered on is split on two columns...

Please, try the next code. A column after the last in the range to be filtered should be empty, to use it as helper one. You may use any range (in terms of used columns) but its first two columns must be the ones containing the time components:

Sub sortByTimeInTwoColumns()
  Dim ws As Worksheet, lastR As Long, rngFlt As Range, arrT, filtCol As Long
  
  Set ws = ActiveSheet
  lastR = ws.Range("C" & ws.rows.count).End(xlUp).Row 'last row in C:C
  
  Set rngFlt = ws.Range("C2:E" & lastR) 'the range to be filtered (any column instead of E may be used)
  filtCol = cells(1, Split(rngFlt.address, "$")(3)).column + 1 'extract column after the last in the range to be filtered
  
  'extract the time array by combination between C:B columns:
  arrT = Evaluate(rngFlt.Columns(1).address & "+" & rngFlt.Columns(2).address) 'extract the C:D columns time combination an array
  
  ws.cells(2, filtCol).Resize(UBound(arrT, 1)).Value2 = arrT 'place the time array value in the next empty column
  ws.Range("C2:F" & lastR).Sort key1:=ws.cells(2, filtCol), Order1:=xlDescending, header:=xlNo 'apply descending sorting
  
  ws.cells(2, filtCol).Resize(UBound(arrT, 1)).ClearContents 'clear the helper column content
End Sub

Of course, the first two columns must be formatted As Date...

**Comments have been [moved to chat](https://chat.stackoverflow.com/rooms/255116/discussion-on-answer-by-faneduru-excel-form-listbox-vba-how-to-sort-last-date-a); please do not continue the discussion here.** Before posting a comment below this one, please review the [purposes of comments](/help/privileges/comment). Comments that do not request clarification or suggest improvements usually belong as an [answer](/help/how-to-answer), on [meta], or in [chat]. Comments continuing discussion may be removed. — blackgreen, Aug 30 '23 at 14:36

Excel Form Listbox VBA: how to sort last date and time entry

1 Answers1

Linked