Changing values of cells to uppercase in a private sub

[Dallie]

Hi all,

I have this code here that simply turns the cells to uppercase.

[
Sub Turn_Uppercase()
   For Each x In Range("C3:H200")
           x.Value = UCase(x.value)
   Next
End Sub
/CODE]

Does anyone know how would I adapt this code so that I can run in a sheet tab (Private sub worksheet_change)? rather than a module as others maybe using this sheet and have no knowledge of macros.

Thanks in advance guys!

 

[Dallie]

Whoops!

 Sub Turn_Uppercase()
   For Each x In Range("C3:H200")
           x.Value = UCase(x.value)
   Next
 End Sub

 

[mikerickson]

How do you intend the others to call that routine, a button, automatically capitalize every entry or something else?

 

[Dallie]

Thanks for the response Mike,

My intention was for automatic capitalization of every entry.

 

[mikerickson]

If you want it to be automatic for user entry, but not for copy pasted ranges, put this in the code module for the sheet in question.

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Cells.Count = 1 Then
            Application.EnableEvents = False
            .FormulaR1C1 = UCase(.FormulaR1C1)
        End If
    End With
    Application.EnableEvents = True
End Sub

If you want it to also effect copy pasted multi-cell ranges, this (slower) code would work.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oneCell As Range
    Application.EnableEvents = False
    For Each oneCell In Target.Cells
            With oneCell
            .FormulaR1C1 = UCase(.FormulaR1C1)
            End With
    Next oneCell
    Application.EnableEvents = True
End Sub

In either case, you would have to run something like your sub to make all the existing entries upper case before automaticaly capitalizing the new data.

 

[Dallie]

I would not need this for copy-pasted ranges I don't think, so the faster macro would be perfectly suitable. If I needed to change this to specific cells (C3:200) would I need to change the Target.Cells? reference to Target.Range?

Thanks again,
Dallie

 

[mikerickson]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If Not Application.Intersect(.Cells, Range("C3:C200")) Is Nothing
            Application.EnableEvents = False
            .FormulaR1C1 = UCase(.FormulaR1C1)
        End If
    End With
    Application.EnableEvents = True
End Sub