Capital Letters

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

This code may do the trick. To enter this code, right-click on the sheet tab to open the VB Editor and simply paste the code in. This particular example will change any entry in column A to Upper Case but it can be adjusted to suit your particular needs.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Finish

If Left(Target.Address, 2) = "$A" Then Target.Value = UCase(Target.Value)

Finish:

Application.EnableEvents = True

End Sub

HTH

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> 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"
<[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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"
> <[email protected]> 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.
>