Macro to CONVERT to UPPERCASE needs to be tweaked. Help PLEASE.

[Mister H]

Hi All:

I am using the code below to convert all input on the spreadsheet to UPPERCASE. This was working fine until I added a Column that contains dates (Column D is formatted like 03/Mar/2009). So when I type in 03-03 in column D I get: 03/Mar/2009 instead of 03/MAR/2009

Can the code be tweaked to accommodate the dates?

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
 
'Converts ALL input to UPPERCASE
 
  Dim cel As Range
  Application.EnableEvents = False
  
  On Error Resume Next
  For Each cel In Intersect(Target, Target.SpecialCells(xlCellTypeConstants, xlTextValues))
    cel.Value2 = UCase(cel.Value2)
  Next cel
  
  Application.EnableEvents = True
    
End Sub

THANKS to anyone that can assist,
Take Care,
Mark

 

[Richard Schollar]

Hi

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
 
'Converts ALL input to UPPERCASE
 
  Dim cel As Range
  Application.EnableEvents = False
  
  On Error Resume Next
  For Each cel In Intersect(Target, Target.SpecialCells(xlCellTypeConstants, xlTextValues))
    cel.NumberFormat = "@"
    cel.Value2 = UCase(cel.Text)
  Next cel
  
  Application.EnableEvents = True
    
End Sub

 

[Mike Blackman]

Hi,

How about;

Private Sub Worksheet_Change(ByVal Target As Range)
 
'Converts ALL input to UPPERCASE
 
  Dim cel As Range
  Application.EnableEvents = False
  
  On Error Resume Next
  For Each cel In Intersect(Target, Target.SpecialCells(xlCellTypeConstants, xlTextValues))
    If IsDate(cel) = False Then cel.Value2 = UCase(cel.Value2)
  Next cel
  
  Application.EnableEvents = True
    
End Sub

 

[Mister H]

Hi Richard, Hi Mikey:

THANKS for your replies. Column D is still keeping the lowercase

Upon further review column D is formatted as Custom dd/mmm/yyyy;@

Not sure if that makes a difference or not. Any other suggestions? The code seems to work everywhere but where the Custom formatting is.

THANKS,
Mark

 

[sailepaty]

Do you want to keep the dates as a dates? Or do you want to change them as a text?

 

[Richard Schollar]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
 
'Converts ALL input to UPPERCASE
 
  Dim cel As Range, temp
  Application.EnableEvents = False
  
  On Error Resume Next
  For Each cel In Intersect(Target, Target.SpecialCells(xlCellTypeConstants))
    temp = cel.Text
    cel.NumberFormat = "@"
    cel.Value = UCase(temp)
  Next cel
  
  Application.EnableEvents = True
    
End Sub

 

[indiantrix]

I'm not a "coder", so here's a conventional solution: why not set the default font to either Felix Titling or Perpetua Titling? That way, as I tested it, the dates appear as all caps and so does the rest of the data.
HTH - Larry.

 

[Mister H]

THANKS Richard. That seems to do the trick

I guess in order for it to convert it has to be text. Correct? I am hoping that this does not cause the user any grief.

If there is a way to leave it as a date but display it is UPPERCASE that would work for me better but I think this will do.

Also just wanted to say THANKS to sailepaty as well for jumping into the Post

Take Care and THANKS to Everyone for their assistance,
Mark

 

[Richard Schollar]

You'd have to go with Larry's font suggestion to keep them as upper case and dates I think. Do the users really need an upper case month name?

 

[Mister H]

Hi Larry:

THANKS for your suggestion as well. That is actually what I tried and it worked on a new spreadsheet but not on the one I was trying to revise?

It must have been the way the file was brought into Excel or something. It was very weird. Anyway, I think the supplied code will do the trick I just wanted to THANKS you for your input

Richard: THANKS Again. As mentioned above the font for some reason isn't working for me. I think the user needed CAPS for the date as the data is being cut and paste into a different program which requires CAPS. Anyway, I am pretty sure that your supplied code will do the trick

Have a GREAT day,
Bye 4 Now,
Mark