Forcing Uppercase in a cell

[Guest]

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

 

[Voodoodan]

Hi,

Check out this link for info:

http://www.excelforum.com/showthread.php?s=&threadid=148078

Dan.

 

[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
(e-mail address removed)

How would one setup forcing a cell to always be uppercase format

regardless of how data is input?

 

[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

--

 

[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.

 

[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.

 

[Don Guillett]

Yes, I should know better.