Forcing Uppercase in a cell

Z

Zac

How would one setup forcing a cell to always be uppercase format regardless of how data is input?
 
D

Don Guillett

right click on the sheet tab>view code>copy/paste this>SAVE

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End Sub

--
Don Guillett
SalesAid Software
[email protected]
Zac said:
How would one setup forcing a cell to always be uppercase format
Click to expand...
regardless of how data is input?
 
D

David McRitchie

Hi Donald,

Don't think Don meant SelectionChange
here is another version with a bit more sample coding
to modify to your own requirement. Since it applies only
to the worksheet it is associated with it won't affect other worksheets.

modify to allow only the column you want to be changed
like a zip state code i.e.
if Target.column <> 7 then exit sub


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'to install -- right-click on the sheettab of the corresponding
' sheet and choose 'view code'. Paste the following procedure
' in the module. -- this is for EVENT macros ONLY.
If Target.Column = 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
Application.EnableEvents = True
End Sub

--
 
D

Don Guillett

David. Works either way as user is going to hit enter or move to another
selection anyway. So, instead of deleting the selection part, I just left it
in.

--
Don Guillett
SalesAid Software
[email protected]
David McRitchie said:
Hi Donald,

Don't think Don meant SelectionChange
here is another version with a bit more sample coding
to modify to your own requirement. Since it applies only
to the worksheet it is associated with it won't affect other worksheets.

modify to allow only the column you want to be changed
like a zip state code i.e.
if Target.column <> 7 then exit sub


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'to install -- right-click on the sheettab of the corresponding
' sheet and choose 'view code'. Paste the following procedure
' in the module. -- this is for EVENT macros ONLY.
If Target.Column = 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
Application.EnableEvents = True
End Sub

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

right click on the sheet tab>view code>copy/paste this>SAVE

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End Sub

--
Don Guillett
SalesAid Software
[email protected]

regardless of how data is input?
Click to expand...
Click to expand...
 
H

Harald Staff

Hi Don

I'm very picky on the following -an obsession probably, I know <g>:
Target = UCase(Target)
will replace a formula with it's resulting value. So please
Target.Formula = UCase(Target.Formula)
no matter which event that's used.

Best wishes Harald
Followup to newsgroup only please.
 
Top