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

TheShyButterfly

New Member
Joined
Nov 6, 2011
Messages
43
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


A
B
C
D
E
F
G
H
I
J
K
6


First Name (Propercase)Last Name (Uppdercase)Full Name (auto concatenated)PPort #
Date Issued
Date Expires
SAC#SAC DetailsSite Type
7











8











9












<tbody>
</tbody>


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 ....

Below is some of the combinations of code I tried:
Code:
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 ...

Code:
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

Code:
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
 
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
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

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