Date Format?

hallingh

Well-known Member
Joined
Sep 11, 2010
Messages
769
I have some code that sets rsp as a string and equal to the month and year selected by the user from a userform in the format of "Oct-10" or "Feb-11." My code is working great, but when I set a cell on the worksheet equal to rsp it puts it in the format "11-Oct" instead of "Oct-11." Is there anyway I can easily fix this to make it go in the correct format? Here is my code (shortened to show the relevant portions:)

Code:
Dim rsp As String
rsp = Me.MonthBox.Value & "-" & Right(Me.YearBox.Value, 2)
ActiveSheet.Range("F" & CellRow).Value = rsp

CellRow is defined as a long and was given a value earlier. Right now, it is setting the correct cell equal to the correct date, it is just in the incorrect format. Any help would be greatly appreciated.

Thanks!

Hank
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,451
Code:
ActiveSheet.Range("F" & CellRow).Value = DateSerial(Me.YearBox.Value, Me.MonthBox.Value, 1)
ActiveSheet.Range("F" & CellRow).NumberFormat = "mmm-yy"
 
Last edited:

hallingh

Well-known Member
Joined
Sep 11, 2010
Messages
769
Hmmm when I ran that it didn't change the cell at all. I changed the second line to read:

Code:
ActiveSheet.Range("F" & CellRow).NumberFormat = "[B]mm[/B]-yy"

Instead of:

Code:
ActiveSheet.Range("F" & CellRow).NumberFormat = "mmm-yy"


Should I not have done that?

Thanks for the help.

Hank
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,451
What is in Me.MonthBox.Value ? Text like "Oct" or the month number like 10 ?

And do you want an actual date in cell F? or do you only want the Text of Month-Year?

If Me.MonthBox.Value contains text like "Oct" and you don't want an actual date in cell F then try this...
Code:
ActiveSheet.Range("F" & CellRow).NumberFormat = "@"
ActiveSheet.Range("F" & CellRow).Value = Me.MonthBox.Value & "-" & Right(Me.YearBox.Value, 2)
 
Last edited:

hallingh

Well-known Member
Joined
Sep 11, 2010
Messages
769

ADVERTISEMENT

My combo boxes in the userform are just text values from a range. They are not actual dates.

It doesn't have to populate the cell with a date, it just needs to appear in the format "Oct-10."
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,451
I updated post #4.

Format the F cell as Text first...
ActiveSheet.Range("F" & CellRow).NumberFormat = "@" ' Text cell format
...then add your Month-Year text
 
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,168,032
Messages
5,856,939
Members
431,841
Latest member
jaybeem

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