Date Format?

[hallingh]

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

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

 

[AlphaFrog]

ActiveSheet.Range("F" & CellRow).Value = DateSerial(Me.YearBox.Value, Me.MonthBox.Value, 1)
ActiveSheet.Range("F" & CellRow).NumberFormat = "mmm-yy"

 

[hallingh]

Hmmm when I ran that it didn't change the cell at all. I changed the second line to read:

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

Instead of:

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

Should I not have done that?

Thanks for the help.

Hank

 

[AlphaFrog]

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...

ActiveSheet.Range("F" & CellRow).NumberFormat = "@"
ActiveSheet.Range("F" & CellRow).Value = Me.MonthBox.Value & "-" & Right(Me.YearBox.Value, 2)

 

[hallingh]

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]

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

 

[hallingh]

Perfect! Works like a charm. Thanks a lot for the help.

Hank