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

 

[RoryA]

Try this Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    ' ignore entire row/column changes as may be inserting/deleting
    If Target.Rows.Count = Rows.Count Or Target.Columns.Count = Columns.Count Then Exit Sub

    On Error GoTo clean_exit

    ' check if cells in column c or d are changed
    If Not Intersect(Target, Range("C:D")) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False

        For Each cell In Intersect(Target, Range("C:D")).Cells
            Select Case cell.Column
                Case 3
                    cell.Value = Application.WorksheetFunction.Proper(cell.Value)
                Case 4
                    cell.Value = UCase(cell.Value)
            End Select
            Cells(cell.Row, "E").FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"
        Next cell
    End If
    
clean_exit:
    Application.EnableEvents = True

End Sub

 

[TheShyButterfly]

Hi Rory .... Wow ... this is fantastic Thank you so much for your quick response ... it works a charm

I was also wondering about the Vlookup Macro which I mentioned below ....

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

The code for the Vlookup is linked to a button (I had no choice, because I couldn't get the macro to run in the worksheet module ... so I had to create a button to assign it to .... but I'm trying to get a streamlined workflow where the user has to do the minimum amount of clicking etc... .)

Is there any reason in the code why it shouldn't be successfully called via the worksheet module?
There doesn't seem to be a way to upload the actual workbook so that you can really see what is/is not happening... so I hope you can make sense of what I've provided.

Thank you VERY much again .... I remember you were instrumental in helping me out a few times over the last few years and I am so grateful ..
Cheers,
TheShyButterfly

 

[RoryA]

There doesn't seem much point in processing all the rows every time, so this version will just process the ones being changed. If the SAC# is not matched, it will clear the cells in J and K.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' ignore entire row/column changes as may be inserting/deleting
    If Target.Rows.Count = Rows.Count Or Target.Columns.Count = Columns.Count Then Exit Sub

    On Error GoTo clean_exit

    ' check if cells in column c or d are changed
    If Not Intersect(Target, Range("C:D,I:I")) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        Dim cell As Range

        For Each cell In Intersect(Target, Range("C:D")).Cells
        
            Select Case cell.Column
                Case 3 ' col C
                    cell.Value = Application.WorksheetFunction.Proper(cell.Value)
                Case 4 ' col D
                    cell.Value = UCase(cell.Value)
                Case 9 ' col I
                
                    Dim lookupRange As Range
                    Set lookupRange = Application.Range("SAC_Details")
                    
                    Dim rwMatch
                    rwMatch = Application.Match(cell.Value, lookupRange.Columns(1), 0)
                    
                    If Not IsError(rwMatch) Then
                        Cells(cell.Row, "J").Value = lookupRange.Cells(rwMatch, 2).Value
                        Cells(cell.Row, "K").Value = lookupRange.Cells(rwMatch, 16).Value
                    Else
                        Cells(cell.Row, "J").Resize(, 2).Value = vbNullString
                    End If
                    
            End Select
            Cells(cell.Row, "E").FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"
        Next cell
        
    End If
    
clean_exit:
    Application.EnableEvents = True

End Sub

 

[TheShyButterfly]

Thank you again Rory,
I deleted all the code of the Worksheet module, and copied all the code from your latest post.
Your code relating to the text cases works perfectly, however, the code for the SAC# is not working, when I enter the 4 digit number, which I know exists, it doesn't do anything.
I've double/triple checked the Named Range and it is correct.

The results from the lookup of column I (eg. 7000, 7001, etc...) should return the values of the named range in column 2(Col J) and column 16 (Col K), which from what you have in the code is correct.

The only thing I am confused about is the following code, because I'm not familiar with the 'Match' function in VBA ... so I can't try to work out what it is looking to match, and which column data it is returning.
I presume it is doing the more 'efficient' way of the usual Excel function of Vlookup (Vlookup($I8,SAC_Details,2,0) and Vlookup($I8,SAC_Details,16,0) ...obviously for each row that Column I will be populated with the 4 digit number, and returning the results into column J and K.

Is there some easy/ier way to explain why your code isn't working? I have blocked out the code that was in the Macro Module, so it wouldn't interfere with your code.

Sorry to be a pain .... thank you for your patience

Cheers,
TheShyButterfly

 

[RoryA]

Does a VLOOKUP formula in the sheet work? My guess would be not, as I suspect you have a mismatch between numbers and numbers stored as text.

 

[TheShyButterfly]

Hi Rory,
I tried with the normal Excel function, =VLOOKUP(I9,SAC_Details,2,0) and it worked perfectly.

The code I provided that I had to put into the Macro Module, also worked when I assigned it to a button, it also worked perfectly ... I just wanted it to do it in the worksheet module ... not by pushing the button.

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 know it's strange ... but that isn't unusual for me ... unfortunately.

I unblocked the Vlookup1 code in the Macro Module.... and tested it with the button ... it populated the 2 columns with the correct data

What can I do to help you help me resolve this ?

Cheers,
TheShyButterfly

 

[RoryA]

Can you try editing this part of the code:

Else
                        Cells(cell.Row, "J").Resize(, 2).Value = vbNullString

to this:

Else
                        Cells(cell.Row, "J").Resize(, 2).Value = "No match"

just to be sure it's actually running?

 

[TheShyButterfly]

Hi Rory,

I replaced the code

Cells(cell.Row, "J").Resize(, 2).Value = vbNullString

with

Cells(cell.Row, "J").Resize(, 2).Value = "No match"

Unfortunately, this didn't work ... I entered the same number as I had before using the Macro button, but blocking that code and editing your code as requested, this didn't do anything ... i moved from the SAC# column (after entering the number) and moved to another cell and it remained empty.

I checked the 'Compile VBAproject' and received no errors. I tried to 'step through' the code, but I was unable to step through the code ...

I'm about to head to bed now ... (1am), but I'll return after work tomorrow ... in case you think of something while I'm sleeping
Thank you so much again for your time and effort to resolve this issue
Good night for now.
Cheers,
TheShyButterfly

 

[RoryA]

I've just spotted the issue - stupid error on my part! This line:

For Each cell In Intersect(Target, Range("C:D")).Cells

needs to be:

For Each cell In Intersect(Target, Range("C:D,I:I")).Cells