How to use uppercase in an additional column?

[ham fam]

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?

[ham fam]

Figured it out, I was only adding it to the For Each rng... line, but not adding it to the If Not Intersect... line.:doh:

[Bob Phillips]

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

[ham fam]

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

[Bob Phillips]

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.