What's wrong with this picture? VBA Date code..

  • Thread starter Thread starter mriley
  • Start date Start date
M

mriley

I am attempting to format a date within Excel as DD-MMM-YYYY where the
MMM would be all caps. The only way that I have seen to do this so far
is to write a VB snippet that would convert it to text. MS actually has
sample code in KB Q213503 which works fine, it's listed below:

Sub UpperMonth()
Dim Cell As Object 'Declare the Cell variable.
For Each Cell In Selection
' If the cell is blank or a text string, then
' skip to the next cell in the selection.
If Cell.Value <> "" And Val(Cell.Value) > 0 Then
'Format the cell as text in a 'mmm' number format,
'and change it to uppercase.
Cell.Value = UCase(Format(Cell.Value, "mmm dd yyyy"))
End If
Next
End Sub

The only catch is if I try and change the date mask to "dd-mmm-yyyy",
the code does nothing. If I throw in a variable in the code and step
thru it I can see it will convert the date to MyStr as say 15 0CT 2003
as a string, but never changes the cell value..i.e.

Sub UpperMonth()
Dim MyStr
Dim Cell As Object 'Declare the Cell variable.
For Each Cell In Selection
' If the cell is blank or a text string, then
' skip to the next cell in the selection.
If Cell.Value <> "" And Val(Cell.Value) > 0 Then
'Format the cell as text in a 'mmm' number format,
'and change it to uppercase.
MyStr = UCase(Format(Cell.Value, "dd mmm yyyy"))
Cell.Value = UCase(Format(Cell.Value, "dd mmm yyyy"))
End If
Next
End Sub

Any ideas?
Thanks,
Mike
 
Format the cell as "@" (Text) prior to inserting the datestring
Note that the cell will now be stored as TEXT, so you cannot
use it in calculations.


Sub UpperMonth()

Dim Cell As Object 'Declare the Cell variable.
For Each Cell In Selection
' If the cell is blank or a text string, then
' skip to the next cell in the selection.
If Cell.Value <> "" And Val(Cell.Value) > 0 Then
'Format the cell as text in a 'mmm' number format,
'and change it to uppercase.
Cell.NumberFormat = "@"
Cell.Value = UCase(Format(Cell.Value, "dd mmm yyyy"))
End If
Next
End Sub


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
Back
Top