How do I ensure that all the values typed in different fields are
capitalised? Please let me know if there si something we can do so that when
we type into a spread sheet the values automatically default to Uppercase.

Prasad Gopinath

Hi,

Right click your sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
On Error GoTo 0
End Sub

Mikr

"Prasad Gopinath" wrote:

How do I ensure that all the values typed in different fields are
capitalised? Please let me know if there si something we can do so that when
we type into a spread sheet the values automatically default to Uppercase.

Prasad Gopinath

Mike

Thank you.

Prasad




"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
On Error GoTo 0
End Sub

Mikr

"Prasad Gopinath" wrote:

How do I ensure that all the values typed in different fields are
capitalised? Please let me know if there si something we can do so that when
we type into a spread sheet the values automatically default to Uppercase.

Prasad Gopinath

Mike,
If we wanted range of cells in a particular column say A1:A1000, could you
revise and post the code for it.?

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
On Error GoTo 0
End Sub

Mikr

"Prasad Gopinath" wrote:

How do I ensure that all the values typed in different fields are
capitalised? Please let me know if there si something we can do so that when
we type into a spread sheet the values automatically default to Uppercase.

Prasad Gopinath

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Me.Range("A1:A1000")) Is Nothing Then
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
End If
ErrHandler:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Fri, 29 Feb 2008 12:41:02 -0800, murthy
wrote:

Mike,
If we wanted range of cells in a particular column say A1:A1000, could you
revise and post the code for it.?

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
On Error GoTo 0
End Sub

Mikr

"Prasad Gopinath" wrote:

How do I ensure that all the values typed in different fields are
capitalised? Please let me know if there si something we can do so that when
we type into a spread sheet the values automatically default to Uppercase.

Prasad Gopinath

Thanks Gord Dibben,
I made the same chage at range and it did work for me. I am glad that you
confirmed the code revision I was thinking of.


"Gord Dibben" wrote:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Me.Range("A1:A1000")) Is Nothing Then
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
End If
ErrHandler:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Fri, 29 Feb 2008 12:41:02 -0800, murthy
wrote:

Mike,
If we wanted range of cells in a particular column say A1:A1000, could you
revise and post the code for it.?

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
On Error GoTo 0
End Sub

Mikr

"Prasad Gopinath" wrote:

How do I ensure that all the values typed in different fields are
capitalised? Please let me know if there si something we can do so that when
we type into a spread sheet the values automatically default to Uppercase.

Prasad Gopinath

You can use a font (Balloon, Copperplate, etc.) that shows the text in uppercase. If you
have Excel 2000 or higher, you can see the fonts in their actual font with the font box on
the formatting toolbar. The text won't really be in uppercase, so if it's pasted elsewhere,
it will still be in the case in which it was originally entered.

Or you can use an event-fired macro that corrects the particular cells, columns, etc. that
you want in caps. Pasting this into the relevant sheet module will correct all entries in
column A to upper case:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A:A"), Target) Is Nothing Then ' is this our column?
Target.Value = UCase(Target.Value) ' put it in upper case
End If
End Sub

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeyl.com
-----------------------------------------------------------------------
"Prasad Gopinath" <Prasad wrote in message
...
How do I ensure that all the values typed in different fields are
capitalised? Please let me know if there si something we can do so that when
we type into a spread sheet the values automatically default to Uppercase.

Prasad Gopinath

Prased,

If you're going to use a macro, use Mike H's code. I forgot to disable events. It still
works, but isn't a good idea.

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeyl.com
-----------------------------------------------------------------------
"Earl Kiosterud" wrote in message
...
You can use a font (Balloon, Copperplate, etc.) that shows the text in uppercase. If you
have Excel 2000 or higher, you can see the fonts in their actual font with the font box on
the formatting toolbar. The text won't really be in uppercase, so if it's pasted
elsewhere, it will still be in the case in which it was originally entered.

Or you can use an event-fired macro that corrects the particular cells, columns, etc. that
you want in caps. Pasting this into the relevant sheet module will correct all entries in
column A to upper case:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A:A"), Target) Is Nothing Then ' is this our column?
Target.Value = UCase(Target.Value) ' put it in upper case
End If
End Sub

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeyl.com
-----------------------------------------------------------------------
"Prasad Gopinath" <Prasad wrote in message
...
How do I ensure that all the values typed in different fields are
capitalised? Please let me know if there si something we can do so that when
we type into a spread sheet the values automatically default to Uppercase.

Prasad Gopinath

"Earl Kiosterud" wrote:

Prased,

If you're going to use a macro, use Mike H's code. I forgot to disable events. It still
works, but isn't a good idea.

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeyl.com
-----------------------------------------------------------------------
"Earl Kiosterud" wrote in message
...
You can use a font (Balloon, Copperplate, etc.) that shows the text in uppercase. If you
have Excel 2000 or higher, you can see the fonts in their actual font with the font box on
the formatting toolbar. The text won't really be in uppercase, so if it's pasted
elsewhere, it will still be in the case in which it was originally entered.

Or you can use an event-fired macro that corrects the particular cells, columns, etc. that
you want in caps. Pasting this into the relevant sheet module will correct all entries in
column A to upper case:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A:A"), Target) Is Nothing Then ' is this our column?
Target.Value = UCase(Target.Value) ' put it in upper case
End If
End Sub

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeyl.com
-----------------------------------------------------------------------
"Prasad Gopinath" <Prasad wrote in message
...
How do I ensure that all the values typed in different fields are
capitalised? Please let me know if there si something we can do so that when
we type into a spread sheet the values automatically default to Uppercase.

Prasad Gopinath