Upper Case

[ceemo]

Is there a way to set particular cells so that no matter what is typed
into them it will always apear in upper case. I realise you can do this
if you set the column next to it to read =upper([cell ref]) but i dont
want to do it this way is there an alternatve perhaps via code im not
sure

 

[ngenear11]

One possible solution is to use the Worksheet_Change event. I have not
assembled any code to solve your problem directly, but I have used this
event to perform similar tasks. Hope this is a start

 

[Daniel CHEN]

You can download some free add-in which allows you to change a range of text
to upper case immediately, not use Upper function on each individual cell.


--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download
Free Excel / VBA Training Materials is Available for Download
=================================

 

[ceemo]

thanks for your advise but im looking for someonthing automated as
others will be using the workbook and i want to keep manual
intervention to the minimum


suggestions welcome

 

[Gord Dibben]

ceemo

Right-click on the sheet tab and "View Code".

Paste this code into that sheet module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column > 3 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub


In columns A, B and C any text typed into a cell will be Upper case.
Adjust range to suit.


Gord Dibben MS Excel MVP

 

[ngenear11]

here is an example of how to use the event
right click on the sheet tab and click "view code"
paste the code below into the vb screen that opens

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Range("A1").Select


strSelected = ActiveCell.Value
UpperString = UCase(strSelected)
ActiveCell.Value = UpperString
End Sub

whatever you type in range a1 will be uppercase once you leave the
cell.
you could extend this to more cells.

 

[ceemo]

excellent thank you peep