Upper Case

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

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> ceemo
> ------------------------------------------------------------------------
> ceemo's Profile:
> http://www.excelforum.com/member.php...o&userid=10650
> View this thread: http://www.excelforum.com/showthread...hreadid=545914
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> ceemo
> ------------------------------------------------------------------------
> ceemo's Profile:
> http://www.excelforum.com/member.php...o&userid=10650
> View this thread: http://www.excelforum.com/showthread...hreadid=545914
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> ceemo
> ------------------------------------------------------------------------
> ceemo's Profile:
> http://www.excelforum.com/member.php...o&userid=10650
> View this thread: http://www.excelforum.com/showthread...hreadid=545914
>

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

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


On Fri, 26 May 2006 12:53:58 -0500, ceemo
<[email protected]> wrote:

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

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.

excellent thank you peeps