All Caps, / All Lower, not the function

  • Thread starter Thread starter Nastech
  • Start date Start date
N

Nastech

Hi, am wondering if there is Custom Format, or a obtainable button (as in
ms-word for all caps). Have a strong need to have some columns data entered
just lower case, and some upper. Guesse would suggest include a "proper"
button as well.
thanks.
 
No special button in Excel, you have to use one of the existing functions or write one.
However, why not use the Caps Lock key on the keyboard?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming - check out "XL Extras")



"Nastech"
wrote in message
Hi, am wondering if there is Custom Format, or a obtainable button (as in
ms-word for all caps). Have a strong need to have some columns data entered
just lower case, and some upper. Guesse would suggest include a "proper"
button as well.
thanks.
 
Generally, I just use =UPPER(A1) in an empty cell, copy it down, then
copy/paste special/values over my original data and delete the helper column.
If you want a button, you could use a small macro and assign it to a button:

Sub UpperCase()
Dim rngCell As Range

For Each rngCell In Selection.Cells
rngCell.Value = UCase(rngCell.Value)
Next rngCell

End Sub

Sub LowerCase()
Dim rngCell As Range

For Each rngCell In Selection.Cells
rngCell.Value = LCase(rngCell.Value)
Next rngCell

End Sub


Sub ProperCase()
Dim rngCell As Range

For Each rngCell In Selection.Cells
rngCell.Value = Application.Proper(rngCell.Value)
Next rngCell

End Sub
 
Be aware the this code will wipe out any formulas you may have in the range.

Best to go with this revision to look for formulas.............

Sub Upper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
If Not Cell.HasFormula Then
Cell.Value = UCase(Cell.Value)
End If
Next
Application.ScreenUpdating = True
End Sub

Or this one which is slightly shorter....................

Sub Upper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = UCase(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP
 
good call - thanks!

Gord Dibben said:
Be aware the this code will wipe out any formulas you may have in the range.

Best to go with this revision to look for formulas.............

Sub Upper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
If Not Cell.HasFormula Then
Cell.Value = UCase(Cell.Value)
End If
Next
Application.ScreenUpdating = True
End Sub

Or this one which is slightly shorter....................

Sub Upper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = UCase(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP
Click to expand...
 
Back
Top