Search code examples
excelvbaformssortingcommandbutton

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

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.

code

form

[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: form

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

Solution

  • 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...