Passing Listbox data to range and back retaining date format

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
437
I have a listbox of 9 columns that has a fair number of listing in it (enough that I wouldn't want to cycle through the entire listbox row by row). I want to dump the contents to a range, do a sort, and reload it into the listbox. Two of the columns (5 & 6) are in the format "dd-mmm-yy AM/PM" (e.g. 01-Jan-21 3:10 PM).

I normally would push the listbox data to an array and paste to the range:

vArray = Me.ListBox1.List
rRange = vArray

but this converts the dates to yyyy-mm-dd AM/AM (e.g. 2021-01-01 3:10PM using the example above). How can I retain the original date formatting?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Maybe like this

VBA Code:
Sub jec()
 vArray = Me.ListBox1.List
 With rRange
   .NumberFormat = "dd-mmm-yy AM/PM"
   .Value = vArray
 End With
End Sub
 
Upvote 0
Hi JEC, I tried your code and have two issues:
  • It worked going from the ListBox to the range but unfortunately other columns are numbers and it converted all those as well (e.g. the number 2 in another column converted to 02-Jan-00)
  • But even if that didn't happen, when I went back in reverse (from range to listbox), it once more converted "dd-mmm-yy AM/PM) back this time to "d/mm/yyyy AM/PM"
John
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top