Auto Capitalization and Auto Capitalization of First Letter Only

A

Aphten

New Member
Joined
Jul 24, 2024
Messages
4
Office Version
  1. 2021
Platform
  1. Windows
Hello there,

I am trying to do a handful of things and keep running into problems. Any help would be appreciated.

Goals:
1. Auto Cap all letters in the following cells: L8,L19,L30,L41,L52,L63,L74,L85,L96,L107,L118,L129,L140,L151,L162,L173,L184,L195,L206,L217,L228,L239,R8,R19,R30,R41,R52,R63,R74,R85,R96,R107,R118,R129,R140,R151,R162,R173,R184,R195,R206,R217,R228,R239,T10,T21,T32,T43,T54,T65,T76,T87,T98,T109,T120,T131,T142,T153,T164,T175,T186,T197,T208,T219,T230,T241,U10,U21,U32,U43,U54,U65,U76,U87,U98,U109,U120,U131,U142,U153,U164,U175,U186,U197,U208,U219,U230,U241

2. Auto Cap only the first letter of each work in the following cells: X9,X10,X11,X12,X20,X21,X22,X23,X31,X32,X33,X34,X42,X43,X44,X45,X53,X54,X55,X56,X64,X65,X66,X67,X75,X76,X77,X78,X86,X87,X88,X89,X97,X98,X99,X100,X108,X109,X110,X111,X119,X120,X121,X122,X130,X131,X132,X133,X141,X142,X143,X144,X152,X153,X154,X155,X163,X164,X165,X166,X174,X175,X176,X177,X185,X186,X187,X188,X196,X197,X198,X199,X207,X208,X209,X210,X218,X219,X220,X221,X229,X230,X231,X232,X240,X241,X242,X243

This is what I was working with:

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Z As Long

    Dim xVal As String

    On Error Resume Next

    If Intersect(Target, Range("")) Is Nothing Then Exit Sub

    Application.EnableEvents = False

    For Z = 1 To Target.Count

        If Target(Z).Value > 0 Then

            Target(Z).Value = UCase(Target(Z).Value)

        End If

    Next

    Application.EnableEvents = True

End Sub

I was watching youTube videos for help and I could only figure out how to auto cap everything OR auto cap only the first letter. And it seems like if I put too many cell references, then nothing works.
 
Last edited by a moderator:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Welcome to the MrExcel forum

Use the patterns in your cell list. Try:

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim fl As String, r As Long

    fl = Left(Target.Address(, 0), 1)
    r = Target.Row Mod 11
    Application.EnableEvents = False
        
    Select Case fl
        Case "L", "R", "T", "U"
            If r = 8 Then Target.Value = UCase(Target.Value)
        Case "X"
            If r = 9 Or r = 10 Or r = 0 Or r = 1 Then Target.Value = UCase(Left(Target, 1)) & Mid(Target, 2)
    End Select
    
    Application.EnableEvents = True

End Sub
 
Upvote 0
If you need to convert the current values in your ranges :
VBA Code: 
Sub ChangeCase()
Dim str1$, str2, arr1, arr2, a
str1 = "L8,L19,L30,L41,L52,L63,L74,L85,L96,L107,L118,L129,L140,L151,L162,L173,L184,L195,L206,L217,L228,L239,R8,R19,R30,R41,R52,R63,R74,R85,R96,R107,R118,R129,R140,R151,R162,R173,R184,R195,R206,R217,R228,R239,T10,T21,T32,T43,T54,T65,T76,T87,T98,T109,T120,T131,T142,T153,T164,T175,T186,T197,T208,T219,T230,T241,U10,U21,U32,U43,U54,U65,U76,U87,U98,U109,U120,U131,U142,U153,U164,U175,U186,U197,U208,U219,U230,U241"
str2 = "X9,X10,X11,X12,X20,X21,X22,X23,X31,X32,X33,X34,X42,X43,X44,X45,X53,X54,X55,X56,X64,X65,X66,X67,X75,X76,X77,X78,X86,X87,X88,X89,X97,X98,X99,X100,X108,X109,X110,X111,X119,X120,X121,X122,X130,X131,X132,X133,X141,X142,X143,X144,X152,X153,X154,X155,X163,X164,X165,X166,X174,X175,X176,X177,X185,X186,X187,X188,X196,X197,X198,X199,X207,X208,X209,X210,X218,X219,X220,X221,X229,X230,X231,X232,X240,X241,X242,X243"
arr1 = Split(str1, ",")
arr2 = Split(str2, ",")
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each a In arr1
    Range(a) = UCase(Range(a))
Next
For Each a In arr2
    Range(a) = WorksheetFunction.Proper(Range(a))
Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Eric W said:
Welcome to the MrExcel forum

Use the patterns in your cell list. Try:

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim fl As String, r As Long

    fl = Left(Target.Address(, 0), 1)
    r = Target.Row Mod 11
    Application.EnableEvents = False
       
    Select Case fl
        Case "L", "R", "T", "U"
            If r = 8 Then Target.Value = UCase(Target.Value)
        Case "X"
            If r = 9 Or r = 10 Or r = 0 Or r = 1 Then Target.Value = UCase(Left(Target, 1)) & Mid(Target, 2)
    End Select
   
    Application.EnableEvents = True

End Sub
Click to expand...

Thank you for your reply. I tried this and it didn't work. Anything else up your sleeve?
 
Upvote 0
What do you mean "it doesn't work"? Do none of the cells get capitalized? Do some of them? Does it apply to cells it shouldn't? On the cells where you just want the first letter capitalized, do you just want the first letter (New york), or the first letter of all words (New York), or does it matter? Did you try footoo's macro? It's not an event handler, but if it does what you want, it can be made into one.
 
Upvote 0
Eric W said:
What do you mean "it doesn't work"? Do none of the cells get capitalized? Do some of them? Does it apply to cells it shouldn't? On the cells where you just want the first letter capitalized, do you just want the first letter (New york), or the first letter of all words (New York), or does it matter? Did you try footoo's macro? It's not an event handler, but if it does what you want, it can be made into one.
Click to expand...
I really have no clue what I am talking about, but I do really appreciate your help. 😆 Nothing happened, I'm sure due to user error. I've continued to hunt around on the internet and I think I found something simpler that my non-coding brain may be able to work with.

Maybe you can help me with.

My goal is to:
1. Automatically convert T:T, 22 specific R cells, and 22 specific T cells into all caps (Ucase?)
2. Automatically convert U:U and X:X so that the first letter of each word is capitalized (Proper Case?)

I have multiple sheets in my workbook that I would like where both goals as above are met. Right now, I have 1 sheet with the following code:

Private Sub Worksheet_Change(ByVal Target As Range)


Dim Z As Long

Dim xVal As String

On Error Resume Next

If Intersect(Target, Range("T:T,L8,L19,L30,L41,L52,L63,L74,L85,L96,L107,L118,L129,L140,L151,L162,L173,L184,L195,L206,L217,L228,L239,R8,R19,R30,R41,R52,R63,R74,R85,R96,R107,R118,R129,R140,R151,R162,R173,R184,R195,R206,R217,R228,R239")) Is Nothing Then Exit Sub

Application.EnableEvents = False

For Z = 1 To Target.Count

If Target(Z).Value > 0 Then

Target(Z).Value = UCase(Target(Z).Value)

End If

Next

Application.EnableEvents = True

End Sub

I also have another worksheet within the same book with the following code:

Private Sub Worksheet_Change(ByVal Target As Range)


Dim Z As Long

Dim xVal As String

On Error Resume Next

If Intersect(Target, Range("U:U,X:X")) Is Nothing Then Exit Sub

Application.EnableEvents = False

For Z = 1 To Target.Count

If Target(Z).Value > 0 Then

Target(Z).Value = StrConv(Target(Z).Value, 3)

End If

Next

Application.EnableEvents = True

End Sub


Both sheets meets 1 of my goals. How do I put it together so both my goals can be met within the same sheet?
 
Upvote 0
"1. Automatically convert T:T, 22 specific R cells, and 22 specific T cells into all caps (Ucase?)"

For these ranges perhaps you could format as a capitals font (e.g. Felix Titling) instead of the Worksheet_Change procedure.
 
Upvote 0
You can combine the two event handlers like this:

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Z As Long
Dim xVal As String

    On Error Resume Next
    Application.EnableEvents = False

    If Not Intersect(Target, Range("T:T,L8,L19,L30,L41,L52,L63,L74,L85,L96,L107,L118,L129,L140,L151,L162,L173,L184,L195,L206,L217,L228,L239,R8,R19,R30,R41,R52,R63,R74,R85,R96,R107,R118,R129,R140,R151,R162,R173,R184,R195,R206,R217,R228,R239")) Is Nothing Then

        For Z = 1 To Target.Count

            If Target(Z).Value > 0 Then
                Target(Z).Value = UCase(Target(Z).Value)
            End If

        Next Z
    End If
    
    If Not Intersect(Target, Range("U:U,X:X")) Is Nothing Then

        For Z = 1 To Target.Count

            If Target(Z).Value > 0 Then
                Target(Z).Value = StrConv(Target(Z).Value, 3)
            End If

        Next Z
    End If

    Application.EnableEvents = True
End Sub
 
Upvote 0
Eric W said:
You can combine the two event handlers like this:

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Z As Long
Dim xVal As String

    On Error Resume Next
    Application.EnableEvents = False

    If Not Intersect(Target, Range("T:T,L8,L19,L30,L41,L52,L63,L74,L85,L96,L107,L118,L129,L140,L151,L162,L173,L184,L195,L206,L217,L228,L239,R8,R19,R30,R41,R52,R63,R74,R85,R96,R107,R118,R129,R140,R151,R162,R173,R184,R195,R206,R217,R228,R239")) Is Nothing Then

        For Z = 1 To Target.Count

            If Target(Z).Value > 0 Then
                Target(Z).Value = UCase(Target(Z).Value)
            End If

        Next Z
    End If
   
    If Not Intersect(Target, Range("U:U,X:X")) Is Nothing Then

        For Z = 1 To Target.Count

            If Target(Z).Value > 0 Then
                Target(Z).Value = StrConv(Target(Z).Value, 3)
            End If

        Next Z
    End If

    Application.EnableEvents = True
End Sub
Click to expand...
Thank you so much!
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,220,965
Messages
6,157,119
Members
451,399
Latest member
alchavar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top