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
 

Some videos you may like

Excel Facts

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

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,276
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,276
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,276
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,810
Messages
5,525,010
Members
409,615
Latest member
papaluigi94

This Week's Hot Topics

Top