Consulting

Results 1 to 5 of 5

Thread: How to use uppercase in an additional column?

  1. #1
    VBAX Regular
    Joined
    Dec 2021
    Posts
    8
    Location

    How to use uppercase in an additional column?

    I am trying to add one more column to my macro. So far I have this:
    
    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim rng As Range
       If Not Intersect(Range("j2:o1000,r2:ab1000"), Target) Is Nothing Then
         Application.ScreenUpdating = False
        Application.EnableEvents = False
     For Each rng In Intersect(Range("j2:o1000,r2:ab1000"), Target)
     rng.Value = UCase(rng.Value)
     Next rng
    Application.EnableEvents = True
     Application.ScreenUpdating = True
    End If
    End Sub
    
    
    It works great, Capitalizing anything in the J2:O1000 and R2:ab1000. but now I want to add E3:e1000 as well. How do i do this?

  2. #2
    VBAX Regular
    Joined
    Dec 2021
    Posts
    8
    Location
    Figured it out, I was only adding it to the For Each rng... line, but not adding it to the If Not Intersect... line.

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Don't you just want this code?

    Private Sub Worksheet_Change(ByVal Target As Range)
        
        On Error GoTo wsc_exit
    
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        If Target.Cells.Count = 1 Then
        
            If Not Intersect(Range("E3:E1000, J2:O1000,R2:AB1000"), Target) Is Nothing Then
        
                Target.Value = UCase(Target.Value)
            End If
        End If
        
    wsc_exit:
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End Sub
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    VBAX Regular
    Joined
    Dec 2021
    Posts
    8
    Location
    Bob Phillips, thanks for the alternate option. However, a new problem has surfaced. The macro doesn't work at all, not the old version or the new version that you wrote. I should point out that it stopped working after playing around with my conditional formatting. However, I have taken the drastic step of deleting all of those in an effort to figure out what the problem is. Also the problem surfaced before I even tried your code. What would cause my macro to stop working? the other macros still work, so I didn't "turn off macros..." somehow.

    Here is all the macros in Sheet1:

    Private Sub CommandButton1_Click()
    ' Sort button
        Range("b1").sort Key1:=Range("b4"), Order1:=xlAscending, Key2:=Range("h1"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End Sub
    
    Sub CommandButton_Bottom_row_Click()
    ' Move_To_bottom_Row Macro
    ' Keyboard Shortcut: Ctrl Shift + Z
        Worksheets("Coordinator list").Activate
        Range("c1").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(1, -1).Activate
    End Sub
    
    Sub CommandButtonUP_Top_Row_Click()
    ' Move_To_top_Row Macro
        Worksheets("Coordinator list").Activate
        Range("b3").Select
    End Sub
    
    Sub CommandButton_LAST_column_Click()
    ' Move to last column
        ActiveCell.EntireRow.Range("x1").Activate
    End Sub
    
    Sub CommandButton_First_Column_Click()
    ' Move to first column
        ActiveCell.EntireRow.Range("a1").Activate
    End Sub
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        
        On Error GoTo wsc_exit
    
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        If Target.Cells.Count = 1 Then
        
            If Not Intersect(Range("E3:E1000, J2:O1000,R2:AB1000"), Target) Is Nothing Then
        
                Target.Value = UCase(Target.Value)
            End If
        End If
        
    wsc_exit:
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End Sub

    Private Sub CommandButton1_Click()
    ' Sort button
    Range("b1").sort Key1:=Range("b4"), Order1:=xlAscending, Key2:=Range("h1"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End Sub
    
    Sub CommandButton_Bottom_row_Click()
    ' Move_To_bottom_Row Macro
    ' Keyboard Shortcut: Ctrl Shift + Z 
    Worksheets("Coordinator list").Activate 
    Range("c1").Select    
    Selection.End(xlDown).Select    
    ActiveCell.Offset(1, -1).Activate 
    End Sub
    
    Sub CommandButtonUP_Top_Row_Click()
    ' Move_To_top_Row Macro 
    Worksheets("Coordinator list").Activate Range("b3").Select 
    End Sub
    
    Sub CommandButton_LAST_column_Click()
    ' Move to last column
    ActiveCell.EntireRow.Range("x1").Activate
    End Sub
    
    Sub CommandButton_First_Column_Click()
    ' Move to first column
    ActiveCell.EntireRow.Range("a1").Activate
    End Sub
    
    Private Sub Worksheet_Change(ByVal Target As Range)        
    On Error GoTo wsc_exit
    Application.ScreenUpdating = False    
    Application.EnableEvents = False        
    If Target.Cells.Count = 1 Then            
    If Not Intersect(Range("E3:E1000, J2:O1000,R2:AB1000"), Target) Is Nothing Then                
    Target.Value = UCase(Target.Value)        
    End If    
    End If    
    wsc_exit:    
    Application.EnableEvents = True    
    Application.ScreenUpdating = True
    End Sub
    Last edited by Bob Phillips; 01-21-2022 at 04:55 PM. Reason: Added code tags

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    My guess is that you have somehow turned events off. Try typing this directly into the immediate window in the VBIDE

    Application.EnableEvents = True

    Other than that, I would need to see the workbook.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •