Change range of columns to Uppercase

DezB · Apr 20th 2016

Hi folks

I use the following code to change the text on a sheet to uppercase but would like to refine it so that only selected columns are changed.

I've tried altering the Target.column to something like ="C:F" but without success, any ideas please?

Kind regards

DezB

Code

Private Sub Worksheet_Change(ByVal Target As Range)
     '````````````````````````````````````````````````````'
     'code to change lowercase to uppercase'
     
    If Target.column > 17 Then Exit Sub
    On Error GoTo ErrHandler
    Application.EnableEvents = False
    Target.Formula = UCase(Target.Formula)
ErrHandler:
    Application.EnableEvents = True
     
    Dim sReason As String
     
    If Target.Cells.Count > 1 Then Exit Sub
     
    Application.ScreenUpdating = False
    
    
End Sub

Display More

Grimes0332 · Apr 20th 2016

Re: Change range of columns to Uppercase

Code

Private Sub Worksheet_Change(ByVal Target As Range)




   Dim c As Excel.Range
   
   On Error GoTo ErrHandler
   Application.EnableEvents = False
   
   For Each c In Intersect(Target, Range("C:F"))
      c.Value = UCase(c.Value)
   Next
   
ErrHandler:




   Application.EnableEvents = True
   
End Sub

Display More

**pike** · Apr 20th 2016

Re: Change range of columns to Uppercase

or maybe

Code

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Range("C:F")) Is Nothing Then
        Target = UCase(Target)
    End If
    Application.EnableEvents = True
End Sub

DezB · Apr 20th 2016

Re: Change range of columns to Uppercase

Hi pike

That works just fine, much appreciated. Unfortunately I have another piece of VBA on the same sheet and I'm now getting a conflict:

Code

Option Explicit
 
     
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Range("C:F")) Is Nothing Then
        Target = UCase(Target)
    End If
    Application.EnableEvents = True


     '`````````````````````````````````````````````````````'
     'code to create pop up input box and add notes if "R" input'
     
   If Not Intersect(Target, Range("B6:Q50")) Is Nothing Then
    If Target.Value = "R" Or Target.Value = "A" Or Target.Value = "R/A" Then
         ' Careful of the spaces to handle the 'a Red/an Amber'...
        sReason = Application.InputBox("Please provide a brief comment on why a" & IIf(Target.Value = "R", " Red", "n Amber") & " rating has been given", Type:=2)
        Target.AddComment
        Target.Comment.Visible = False
        Target.Comment.Text Text:=sReason
    End If
End If
     
    Application.ScreenUpdating = True
     
End Sub

Display More

As you can see I'm on a steep learning curve here, but what fun....

Kind regards

DezB

**pike** · Apr 20th 2016

Re: Change range of columns to Uppercase

have fun

Code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim sReason As String
    Application.EnableEvents = False
    If Not Intersect(Target, Range("C:F")) Is Nothing Then
        Target = UCase(Target)
    End If
    Application.EnableEvents = True


    'code to create pop up input box and add notes if "R" "A" "R/A" input'


    If Not Intersect(Target, Range("B6:Q50")) Is Nothing Then
        If Target.Value = "R" Or Target.Value = "A" Or Target.Value = "R/A" Then
            ' Careful of the spaces to handle the 'a Red/an Amber'...
            sReason = Application.InputBox("Please provide a brief comment on why a" & IIf(Target.Value = "R", " Red", "n Amber") & " rating has been given", Type:=2)
            Target.AddComment
            Target.Comment.Visible = False
            Target.Comment.Text Text:=sReason
        End If
    End If


    Application.EnableEvents = True


End Sub

Display More

DezB · Apr 20th 2016

Re: Change range of columns to Uppercase

Hi Pike

Excellent, you're a star...

Much appreciated.

Kind regards

DezB

Change range of columns to Uppercase

Participate now!

Share