Macro for making one cell uppercase

[katieACI]

I have 3 excel templates that I use to create a form for all employees. I have a few cells (in different columns and rows) that I need to always be uppercase. Is there a macro that will ensure whatever is typed into a single cell will automatically be switched to uppercase? I will need to know which field(s) in the macro correlate to the affected cell so I can change it to the correct cell on each template. Thank you!

 

[Cubist]

Try this macro. Right-click on the sheet name -> View code -> Paste the code in the editor. Current affecting cells in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' This subroutine runs whenever a change is made in the worksheet
 
    Dim cell As Range ' Declare a variable to represent each cell in the target range
    Dim rng As Range ' Declare a variable to represent the target range
 
    ' Any change in column A will be affected
    Set rng = Intersect(Target, Me.Columns("A"))
 
    ' Check if the intersected range is not empty
    If Not rng Is Nothing Then
        ' Disable events to prevent triggering this macro recursively
        Application.EnableEvents = False
 
        ' Loop through each cell in the intersected range
        For Each cell In rng
            ' Check if the cell is not empty
            If Not IsEmpty(cell) Then
                ' Convert the cell value to uppercase
                cell.Value = UCase(cell.Value)
            End If
        Next cell
 
        ' Re-enable events to allow other macros to run normally
        Application.EnableEvents = True
    End If
End Sub

 

[rlv01]

If you have a list of not too many cells that need to be uppercase, but they are scattered around, here is an alternative approach.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim RCell As Range, rng As Range
    Dim CellArray As Variant
    Dim I As Long
    
    CellArray = Array("A2:A8", "B10", "G5:K5", "M22", "O22") 'list of data entry cells to enforce upper case
    
    For I = 0 To UBound(CellArray)
        If I = 0 Then
            Set rng = Me.Range(CellArray(I))
        Else
            Set rng = Union(rng, Me.Range(CellArray(I)))
        End If
    Next I
    
    If Not Application.Intersect(Target, rng) Is Nothing Then
        Application.EnableEvents = False
        For Each RCell In rng
                RCell.Value = UCase(RCell.Value)
        Next RCell
        Application.EnableEvents = True
    End If
End Sub

 

[katieACI]

I need this for a specific cell, not all cells within a column.

 

[katieACI]

If you have a list of not too many cells that need to be uppercase, but they are scattered around, here is an alternative approach.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim RCell As Range, rng As Range
    Dim CellArray As Variant
    Dim I As Long
   
    CellArray = Array("A2:A8", "B10", "G5:K5", "M22", "O22") 'list of data entry cells to enforce upper case
   
    For I = 0 To UBound(CellArray)
        If I = 0 Then
            Set rng = Me.Range(CellArray(I))
        Else
            Set rng = Union(rng, Me.Range(CellArray(I)))
        End If
    Next I
   
    If Not Application.Intersect(Target, rng) Is Nothing Then
        Application.EnableEvents = False
        For Each RCell In rng
                RCell.Value = UCase(RCell.Value)
        Next RCell
        Application.EnableEvents = True
    End If
End Sub

This worked! Thank you so much, I appreciate it!