Sort Multi-Column List Box by Clicking Header / Date Format Issue

VBA_newbie_2021 · Jun 9th 2021

Hi,

I have a list box and I want to be able to sort it by clicking or double clicking on the header of each column. Is this even possible?

I have 7 columns: Tracking Number, Location, Date Received, Date Moved, Date Completed, Submitted By and Submitted On.

I've attached the document.

Also, when I update the record the date formatting changes. When I save the initial record, the date format is dd-mmm-yyyy, but when I update it, it puts the date format exactly how you enter it in the date textbox.

For instance, when I save the initial record as 6/1/21, it puts it in the spreadsheet and list box as 01-Jun-2021, but when I update the record and type 6/1/21, it puts it in the spreadsheet and list box as 6/1/21.

It's not a big deal, just a minor annoyance because the date format isn't uniform.

Ingo_Ingo · Jun 10th 2021

Hi,

I'm not a programmer....

Try to format all txt......(who have data)...

In button UPDATE

Sheets("Worksheet").Cells(y, 3).Value = Format(txtReceived, "dd.mmm.YYYY") put data in your format mm/dd/yy or mmm/dd/yyyy ...

Sheets("Worksheet").Cells(y, 4).Value = Format(txtMoved, "dd.mmm.YYYY")

Sheets("Worksheet").Cells(y, 5).Value = Format(txtCompleted, "dd.mmm.yyyy")

Change everywhere you need.

gijsmo · Jun 10th 2021

This sounds similar to my reply to your other post:

Search for partial value

So try changing:

Code

Sheets("Worksheet").Cells(y, 3).Value = txtReceived
Sheets("Worksheet").Cells(y, 4).Value = txtMoved
Sheets("Worksheet").Cells(y, 5).Value = txtCompleted

to:

Code

 Sheets("Worksheet").Cells(y, 3).Value = DateValue(Format(txtReceived, "mm/dd/yyyy"))
 Sheets("Worksheet").Cells(y, 4).Value = DateValue(Format(txtMoved, "mm/dd/yyyy"))
 Sheets("Worksheet").Cells(y, 5).Value = DateValue(Format(txtCompleted, "mm/dd/yyyy"))

In terms of your other request:

Quote

I have a list box and I want to be able to sort it by clicking or double clicking on the header of each column.

I assume you are referring to the data starting at A5 on the sheet called Worksheet.

If so, I've added a Worksheet_BeforeDoubleClick event to the Sheet1 code which should enable to sort by double clicking on any of the headers in A5 to G5.

Sample is attached.

Possible Tracking Number Database Version 3-sort cols.xlsm

VBA_newbie_2021 · Jun 10th 2021

That worked for the formatting. Thank you!

**royUK** · Jun 10th 2021

The simplest way is to sort the data in the worksheet

VBA_newbie_2021 · Jun 10th 2021

Is there no way to click or double click on the header within the list box to sort the data? Would I have to create "invisible" buttons over top of each header?

gijsmo · Jun 10th 2021

There may be a way to sort the listbox but the code in my sample allows you to at least sort the data on the worksheet, which should then be reflected in your listbox.

VBA_newbie_2021 · Jun 10th 2021

Ok. Thank you very much for your help.

**royUK** · Jun 10th 2021

This example is taken from a more complex form which is part of a bundle that I created.

You select the column to sort using the ComboBox then click the appropriate sort button

Sort Multi-Column List Box by Clicking Header / Date Format Issue

Participate now!

Share