Capitalizing a Date

[Blue Max]

How do we format a cell so that the date will all be in uppercase (caps)
versus proper caps (only first letter of month or weekday capitalized)?

 

[Héctor Miguel]

hi, !

How do we format a cell so that the date will all be in uppercase (caps)
versus proper caps (only first letter of month or weekday capitalized)?

if you don't want to use a helper cell/column with upper(... worksheet function...
you might want to give a try to the following:

if available, change the font for your range/worksheet/workbook/style/...

this fonts shows text un uppercase:
- Castellar
- Engravers MT
- Felix Titling
- Stencil
- Technic (*versalles* type)

this font shows text in lowercase:
- Freshbot
- Poornut
- Pussycat

(no other tricks/code/stuff/... needed) no matter if user types in lower/upper case...

hth,
hector.

 

[muddan madhu]

try this

Private Sub Worksheet_Change(ByVal Target As Range)
Target = StrConv(Format(Target, "dd-mmm-yy dddd"), vbUpperCase)
End Sub

 

[Blue Max]

Héctor,

Thank you for the reply. Your listing was very informative. Nevertheless,
we sorely miss the convenience of simply setting a font attribute that would
force the case of the font to UPPER, lower, or Proper. Moreover, it adds
great flexibility since it can be applied universally to any standard font.
Other programs have this ability and makes managing headings and titles a
real cinch! Wish Microsoft Excel 2007 had this capability.

Thanks,
Richard

**************************

 

[Blue Max]

Hello muddan,

The code appears intriquing, but I cannot seem to get it to run as a macro
in Excel 2007. Can you help instruct us on how to use the code. We had
assumed that we could select a range and run this code as a macro (saved to
a code module), but the macro dialog does not provide this subroutine as a
run option. Any thoughts on what we might be doing wrong?

Thanks,
Richard

*************
try this

Private Sub Worksheet_Change(ByVal Target As Range)
Target = StrConv(Format(Target, "dd-mmm-yy dddd"), vbUpperCase)
End Sub

 

[Blue Max]

Hello Héctor,

Just an observation. Doesn't it seem a little dumb that Microsoft would
force you to use a helper cell versus being able to capitalize the date in
the cell directly? Capitalizing a date is not so uncommon that it should be
this difficult.


****************

 

[Héctor Miguel]

hi, Richard ! (I appreciate you "dare to expose" your name)

Just an observation.
Doesn't it seem a little dumb that Microsoft would force you to use a helper cell
versus being able to capitalize the date in the cell directly?
Capitalizing a date is not so uncommon that it should be this difficult.

1) including your 19 minutes previous post, I agree that using uppercase month-name is not "uncommon"
(but and AFAIK) for date-time representations "m" (lower or upper) refers to month (integer) or minutes (fraction)
and the "out-put" is (by default) a proper case for the month-name (disregarding the usedf ont)
so, for the upper/lower coercion it is not so difficult to change the font (if available) which...
(BTW and AFAIK) is OS (windows) the fonts provider, the (applications just "use" the fonts so provided)
with no more "escape" sequences (which becomes in a programming issue now)

2) regarding this post (IMHO) I "hate" when I see in a sentence words in ALL LETTERS UPPER CAPITALIZED
(acronyms could be an exception to "my rule")
(but that is "my preferences", and they have no reason to be "shared" with most of the people)

I'm sure this is not a big deal for the ms-excel developer team (but I would ask for other "issues" before this one)

cordially,
hector.

__ OP __

 

[Joerg Mochikun]

Richard, the code is a worksheet macro and must be put on the code area of
the worksheet, not a macro module.

The code tries to capitalize all inputs, incl. normal text. If you want it
to work only on dates, you could use something like

Private Sub Worksheet_Change(ByVal Target As Range)
If IsDate(Target) Then
Target = StrConv(Format(Target, "dd-mmm-yy dddd"), vbUpperCase)
End If
End Sub

Joerg

 

[Blue Max]

Thanks Joerg, I think I understand. This is apparently a special macro that
executes upon entering data into a cell. We just need one that can be
manually or automatically executed on a single cell, but I think we can
figure that out from the code here.

Is is normally for these special auto-execute macros not to show up in the
macro list so that it can be 'run' manually? Where can we get a list of
these 'reserved' macro names?

Thanks,
Richard

**********************

 

[Gord Dibben]

Event code will not show up in the macro list.

For help on events see Chip Pearson's site.

http://www.cpearson.com/excel/Events.aspx

Or David McRithchie's site.

http://www.mvps.org/dmcritchie/excel/event.htm

Note: Non-event macros labelled Private will not show up in the macro list
either.


Gord Dibben MS Excel MVP

 

[Blue Max]

Thanks, Gord!

************

Event code will not show up in the macro list.

For help on events see Chip Pearson's site.

http://www.cpearson.com/excel/Events.aspx

Or David McRithchie's site.

http://www.mvps.org/dmcritchie/excel/event.htm

Note: Non-event macros labelled Private will not show up in the macro
list
either.


Gord Dibben MS Excel MVP

 

[Blue Max]

Hello Héctor,

Just a note of interest. I just realized that Microsoft Word 2007 includes
all of these font attributes such as All Caps, Small Caps, Outline, and a
myriad other attributes. So the capability is there, it just has not been
integrated into Excel. Interesting, since these are native attributes that
would be so much easier to apply from the font dialogue versus code.

Thanks,
Richard

****************