How do i format cells to always change to capital letters?

You don't. You could use event code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
.Value = Proper(.Value)
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gaute" wrote in message
...
How do i format cells to always change to capital letters?

Gaute

Use a worksheet event.

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


Gord Dibben Excel MVP

On Tue, 8 Mar 2005 23:48:07 +0100, "Gaute" wrote:

How do i format cells to always change to capital letters?

Hi, BobL

He said capitals, from which I assume upper case, so it should be

.Value = UCase$(.Value)

But if he did mean proper case, it has to be

.Value = Application.Proper(.Value)


On Tue, 8 Mar 2005 23:12:23 -0000, "Bob Phillips"
wrote:

You don't. You could use event code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
.Value = Proper(.Value)
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

Hi Myrna,

I think I was mixing this with another post that asked for proper case :-)

Bob


"Myrna Larson" wrote in message
...
Hi, BobL

He said capitals, from which I assume upper case, so it should be

.Value = UCase$(.Value)

But if he did mean proper case, it has to be

.Value = Application.Proper(.Value)


On Tue, 8 Mar 2005 23:12:23 -0000, "Bob Phillips"
wrote:

You don't. You could use event code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
.Value = Proper(.Value)
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.