change column from date to number format and opposite after copy from form

**MKLAQ** · 08-27-2023, 05:36 PM

Hello
I have this code to copy data from listbox to sheet . in first column in listbox contains date will be like this DD/MM/YYYY, then will show DATE in column A and it's ok ,but my problem when select optionbutton1 or optionbutton2 and populate numbers in first column like this 1,2,3 in listbox .... then should copy to column A like this 1,2,3.... but the problem will show like this 45155(DATEVALUE) .
so I want when copy data from listbox date in first column then should show date in column A , if when copy data from listbox numbers in first column then should show number in column A like this 1,2,3 .

Please Login or Register  to view this content.

also posted here
https://www.mrexcel.com/board/threads/change-column-from-date-to-number-format-and-opposite-after-copy-from-form.1243350/
thanks

**craigcurtin** · 08-28-2023, 12:52 AM

Can you not just run a macro after you copy the data to reset it to the appropriate format ?

Something like this

Please Login or Register  to view this content.

**MKLAQ** · 08-28-2023, 03:28 AM

why I should use two codes? !
by the way I don't see about formatting of number .

**Marc L** · 08-28-2023, 06:34 AM

Hello,

as an Excel date is stored as a number then within your ListBox you must use the cell Text property to have the same display layout.

For further help if really necessary then attach at least your workbook …

**MKLAQ** · 08-28-2023, 01:29 PM

within your ListBox you must use the cell Text property to have the same display layout.

do you mean like this ?

Please Login or Register  to view this content.

well, doesn't work for me and I attached file .
when you click optionbutton1 will match with sheet name and populate data in listbox and when click command button1 should copy to sheet1 based on the same formatting in listbox based on DATE or numbers .

**MatrixMan** · 08-28-2023, 01:43 PM

You haven't set the date format ... try this:

Please Login or Register  to view this content.

**MKLAQ** · 08-29-2023, 03:41 AM

Hi MatrixMan ,
your suggestion works for me greatly !

thank you so much .

**MatrixMan** · 08-29-2023, 08:47 AM

You're welcome and thank you for the rep point :-)

change column from date to number format and opposite after copy from form

LinkBack

Thread Tools

Rate This Thread

Display

change column from date to number format and opposite after copy from form

Re: change column from date to number format and opposite after copy from form

Re: change column from date to number format and opposite after copy from form

Re: change column from date to number format and opposite after copy from form

Re: change column from date to number format and opposite after copy from form

Re: change column from date to number format and opposite after copy from form

Re: change column from date to number format and opposite after copy from form

Re: change column from date to number format and opposite after copy from form

Thread Information

Users Browsing this Thread

Similar Threads

How to change Pivot Chart axis number format to a custom date format

Convert forced change of date to String or Text format back to Date format in a column

Unabel to find and replace the date format and number format form excel to word using VBA

[SOLVED] VBA To change Date format in Column A and Copy a cell if it contains specific Text

Show Error when a cell with number format change to date format

Change backwards number format to date format

copy a number typed in a cell to another & change it to word form.

Bookmarks

Bookmarks

Posting Permissions