Uppercase date format

[rjplante]

I am trying to use VBA to enter the date in a dd-mmm-yyyy format with the month in all caps (05-SEP-2018). I have a merged cell C4:D4 and I have the cell custom formated as dd-mmm-yyyy (easy), but no matter what I try I cannot seam to get the month to display in all caps. I have the code below, but it does not work.

Sheets("Sheet2").Range("C4:D4").Value = Format(Date, "dd-mmm-yyyy")
UCase (Range("C4").Value)

I have also tried the following:

UCase (Format(Date, "dd-mmm-yyyy"))

Neither one seems to work. What can be done to make this work?

Thanks for any help or direction.

 

[Joe4]

You will need to make sure that the format of the cell is Text first. We can do that in the code.
Also, since C4 and D4 are merged, you still only write the value to the first cell in that merge (C4).

So this should do what you want:

    Range("C4").NumberFormat = "@"
    Range("C4") = UCase(Format(Date, "dd-mmm-yyyy"))

 

[rjplante]

Thanks, that worked great! What does the NumberFormat = "@" do in the code?

 

[Joe4]

What does the NumberFormat = "@" do in the code?

It is the "Text" format. It is the same if you went to that cell, right-clicked, select Format Cells, go to the Number tab and select the "Text" option.

You can get this code by turning on the Macro Recorder, and record yourself performing those steps above manually.

 

[rjplante]

Great, thanks for the help.

 

[Joe4]

You are welcome!

 

[Rick Rothstein]

You will need to make sure that the format of the cell is Text first.

If the OP would like the cell to contain a real Excel date, but display it as was requested, he could use this code...

[table="width: 500"]
[tr]
	[td]With Sheets("Sheet2").Range("C4")
  .Value = Date
  .NumberFormat = "dd-""" & UCase(Format(Date, "mmm")) & """-yyyy"
End With[/td]
[/tr]
[/table]

 

[SpillerBD]

I like it, except it could get problematic if the cell refers to a another cell with changing values or if we were to copy and paste the formatting on another cell.

 

[Rick Rothstein]

I like it, except it could get problematic if the cell refers to a another cell with changing values or if we were to copy and paste the formatting on another cell.

The OP used the Date function in his code which, if truly representative of his needs, means the date will not be dependent on the value in another cell. If it turns out that the date is dependent on another cell's value, I believe we could use my solution (modified for the cell reference) in a Change event procedure rather than a macro (as I think the OP might be looking to use).