Force text to Uppercase in active cells

Hi all

To ensure correct data entry, I need to ensure that the filled cells in a column are always in Uppercase for example, irrespective of whether they are typed in upper or lower case.

I have found the following code, which seems to work quite well, even though I have the impression that it slowed my workbook down slightly.

Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
 
    On Error Resume Next
 
    If Not Intersect(Target, Range("D:D,F:F")) Is Nothing Then
 
        Application.EnableEvents = False
 
        Target = UCase(Target)
 
        Application.EnableEvents = True
 
    End If
 
    On Error GoTo 0
End Sub

Firstly, I would like to know if the code could be amended so that it only works on the cells in a column from the top of the column (either D1 or D2 down to the last occupied or active cell in that column.

Secondly, is it possible to have a combined code that also creates a capitalised word for each word in a column but for different specified colums.

In other words, if you have an address list where columns A, B, C & D are used for the main parts of the address; these would need the 1st letter to be capitalised and then Column E would contain the postcode, which needs to all in uppercase.

This code would need to be used in various worksheets within one workbook on different columns and I fopund the above code was very easy to customise by changing the column reference.

TIA ...spellbound

Hello Spellbound,

This should slow you down. It will convert columns "A:D" to proper case (First letter of each word is capitalized and the rest lower case) while column "E" is converted to uppercase. Change the StartCell to the cell address the range starts at. It is set at "D2".

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim LastRow As Long
  Dim Rng As Range
  Dim StartCell As String
  Dim StartRow As Long
  
    StartCell = "D2"
    
      With Range(StartCell)
        C = .Column
        StartRow = .Row
      End With
      
      LastRow = Cells(Rows.Count, C).End(xlUp).Row
      LastRow = IIf(LastRow < StartRow, StartRow, LastRow)
      Set Rng = Range(Cells(StartRow, C), Cells(LastRow, C))
      
   Application.EnableEvents = False
   
        Select Case Target.Column
          Case 1, 2, 3, 4
            Target = WorksheetFunction.Proper(Target)
          Case 5
            Target = UCase(Target)
        End Select
      
    Application.EnableEvents = True

End Sub

Hi Roy

Thanks for your suggestion, which I tried out and this works on its own but as my knowledge of VBA is zero, so I could not figure out how to combine both statements in one piece of code.

The solution from Leith works with the combination that I need.

Always open to suggestions though!

spellbound

Have you tried

 Target = StrConv(Target, vbProperCase)