Excel 2016 - VBA - Problem - automatically converting text in column to Uppercase

[TheShyButterfly]

Hi there,
Thank you for viewing my problem.

I am having particular difficulty in getting code to automatically change text in a specific column. I'd like the code to change the case either as the user types, or after the user leaves the cell.

Actually, I would ideally like to have:

• All text in Column C to automatically convert to Proper case
• All text in Column D to automatically convert to Uppercase
• Concatenate the names into Column E

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]I
[/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]K
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]First Name (Propercase)[/TD]
[TD]Last Name (Uppdercase)[/TD]
[TD]Full Name (auto concatenated)[/TD]
[TD]PPort #
[/TD]
[TD]Date Issued
[/TD]
[TD]Date Expires
[/TD]
[TD]SAC#[/TD]
[TD]SAC Details[/TD]
[TD]Site Type
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]


I have tried several codes which don't seem to work. I put the code into the Worksheet module (code name Sheet2)
- Note: I don't use the sheet tab names in my projects because users change the sheet name all the time.

I was hoping to get one of the codes to work for 1 column (C) (Proper case) and then add a secondary line of code in for the Uppercase into the other column (D) and then find a way to automatically concatenate the two names together in column E. But I have failed and I can't work out why.

These are the links to the codes I have tried ....

• https://www.mrexcel.com/forum/excel-questions/314426-formatting-cells-input-uppercase-only.html
  
• https://www.ozgrid.com/VBA/force-case-text.htm
  
• https://www.automateexcel.com/vba/proper-upper-lower-case/

Below is some of the combinations of code I tried:

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim cell As Range
    Application.ScreenUpdating = False
     Application.EnableEvents = False

    For Each cell In Target
        Select Case cell.Column
            Case 4 ' column numbers eg. 10, 11, 12
                cell = UCase(cell)

        End Select
      Application.EnableEvents = True

    Next cell

End Sub

and this one ...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   Dim cell As Range
    Application.ScreenUpdating = False
     Application.EnableEvents = False

    For Each cell In Target
        Select Case cell.Column
            Case 4 ' column numbers eg. 10, 11, 12
                cell = UCase(cell)

        End Select
      Application.EnableEvents = True

    Next cell
'
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column.Count = 4 Then
    Target.Value = UCase(Target.Value)
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
 
 
 
Application.EnableEvents = False
 
If Target.Column = 3 Then
 
Target = StrConv(Target, vbProperCase)
 
End If
 
Application.EnableEvents = True

End Sub

The only other code I have in this workbook, is the Vlookup for looking up the SAC# ... the code is unfortunately a 'macro button' ... it would be nice if it just looked up the values and updated the cells when user clicks outside the column/cell.
Anyhow, this is the code for the Vlookup ... (even though I'm not sure that it would interfere with the case coding ... but then I'm still on a VBA learning curve).

The named range: SAC_Details
Field range: =Locations!$C:$AD

Option Explicit

Sub lookup1()

     Dim ws1 As Worksheet
     Dim x As Long

     Set ws1 = Sheet2


     Sheet2.Select
          For x = 1 To Range("C100000").End(xlUp).Row - 7 '7 is the number of rows before the start of the data

     Range("J" & x + 7).Value = WorksheetFunction.VLookup(Range("I" & x + 7).Value, Range("SAC_Details"), 2, 0)

          Next

          For x = 1 To Range("C100000").End(xlUp).Row - 7 '7 is the number of rows before the start of the data
     Range("K" & x + 7).Value = WorksheetFunction.VLookup(Range("I" & x + 7).Value, Range("SAC_Details"), 16, 0)

          Next

End Sub

I really appreciate your assistance
Thank you in advance.

Cheers,
The ShyButterfly

 

[TheShyButterfly]

Thank you Rory !!! you are a champion !!!
Now it works puuurrrfectly .... I had no doubt that you would figure it out thank you so very much ... have a wonderful day / night ...

Til Next time ...
God Bless you

TheShyButterfly

 

[RoryA]

Glad to help.