Passing Listbox data to range and back retaining date format

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
354
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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
802
Office Version
  1. 365
Platform
  1. Windows
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
 

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
354
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,115
Messages
5,768,204
Members
425,459
Latest member
Danniey

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
Top