6

I want to merge two Worksheet_Change events.

The aim of the code is to convert any uppercase text in the cell ranges given to lowercase.

I tried copying both into the same Worksheet_Change, but Excel crashed.

Range 1:

Private Sub Worksheet_Change(ByVal Target As Range) Dim ccr As Range Set ccr = Range("C6") For Each Cell In ccr Cell.Value = LCase(Cell) Next Cell End Sub

Range 2:

Private Sub Worksheet_Change(ByVal Target As Range) Dim acr As Range Set acr = Range("C9:G9") For Each Cell In acr Cell.Value = LCase(Cell) Next Cell End Sub
Improve this question
3
  • 2
    Just put the code in one event then. Commented Feb 13, 2019 at 14:52
  • 3
    The answers provided are all good except that they have no error handling. So if your code fails between the "Application.EnableEvents = False" and the "Application.EnableEvents = True" statements, then you just left Excel is a bad state where no events will fire. Any time you use "Application.EnableEvents", "Application.ScreenUpdating", etc. always use error handling to make sure that if your code fails, those kind of settings get set back to what they need to be. I'm a .NET/C# developer (VBA = Megablocks, C#=Lego Technic) and error handling is a must. Commented Feb 13, 2019 at 15:11
  • 1
    @FrankBall Right, added one in my answer. Commented Feb 13, 2019 at 15:38

4 Answers 4

Reset to default
6

The main issue is that changing a cell value Cell.Value will trigger another Worksheet_Change immediately. You need to Application.EnableEvents = False to prevent this.

Also I recommend to work with Intersect so the code only runs on the cells that are actually changed.

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim AffectedRange As Range Set AffectedRange = Intersect(Target, Target.Parent.Range("C6, C9:G9")) If Not AffectedRange Is Nothing Then Application.EnableEvents = False 'pervent triggering another change event Dim Cel As Range For Each Cel In AffectedRange.Cells Cel.Value = LCase$(Cel.Value) Next Cel Application.EnableEvents = True 'don't forget to re-enable events in the end End If End Sub

In addition to @Frank Ball's comment including error handling:

Private Sub Worksheet_Change(ByVal Target As Range) Dim AffectedRange As Range Set AffectedRange = Intersect(Target, Target.Parent.Range("C6, C9:G9")) Application.EnableEvents = False 'pervent triggering another change event On Error GoTo ERR_HANDLING If Not AffectedRange Is Nothing Then Dim Cel As Range For Each Cel In AffectedRange.Cells Cel.Value = LCase$(Cel.Value) Next Cel End If On Error GoTo 0 'no Exit Sub here! ERR_HANDLING: Application.EnableEvents = True If Err.Number <> 0 Then Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext End If End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

Like this you can do both the things in same event

You have to add Application.EnableEvents = False at the starting to avoid race condition.

Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Dim ccr As Range, acr as Range Set ccr = Range("C6") For Each Cell In ccr Cell.Value = LCase(Cell) Next Cell Set acr = Range("C9:G9") For Each Cell In acr Cell.Value = LCase(Cell) Next Cell Application.EnableEvents = True End Sub
Improve this answer

1 Comment

Thank you! That fixes it
2

The two Worksheet_Change events are quite the same, they are a loop around a range, returning LCase(). Thus, it is a good idea to make a separate Sub for it like this:

Sub FixRangeLCase(rangeToFix As Range) Dim myCell As Range For Each myCell In rangeToFix myCell.Value2 = LCase(myCell.Value2) Next myCell End Sub

Then, refer the Worksheet_Change event to it. As far as the Worksheet_Change event is quite "expensive", running always, it is a good idea to run it only when a specific Target cell is changed and otherwise exit the procedure - If Intersect(Target, Range("C6"), Range("C9:G9")) Is Nothing Then Exit Sub

The Application.EnableEvents = False is needed to disable the events. At the end it is set back to True.

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("C6"), Range("C9:G9")) Is Nothing Then Exit Sub Application.EnableEvents = False FixRangeLCase Range("C6") FixRangeLCase Range("C9:G9") Application.EnableEvents = True End Sub
Improve this answer

Comments

0

Also you can use:

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range, cell As Range Application.EnableEvents = False If Not Intersect(Target, Range("C6")) Is Nothing Or Not Intersect(Target, Range("C9:G9")) Is Nothing Then Set rng = Range("C9:G9", "C6") For Each cell In rng cell.Value = LCase(cell.Value) Next End If Application.EnableEvents = True End Sub
Improve this answer

9 Comments

This is not right because it only changes target cell
This Target.Value = LCase(Target.Value) fails if Target is a range of multiple cells. LCase cannot handle an array of values Target.Value.
@newguy what you mention is not an issue here because only Target cells actually changed so the others are already lower case (and don't need to be converted again). • The issue here would be that it would run on all changed cells even outside the given range not only on the cells in C6, C9:G9 (but actually it fails because of the reason I mentioned above).
Better in the meaning of not crashing because of Target beeing a range. But note that if eg only cell C10 is edited the complete rng is converted to lower case while only C10 changed and it would be sufficient to convert this cell only. To prevent this see my answer where I used AffectedRange = Intersect(Target, Target.Parent.Range("C6, C9:G9")) to get only the changed cells of the desired range. So you would end up with what I answered.
If the event is triggered while another worksheet has focus I'm not sure to which worksheet Range would default Target.Parent gives the worksheet where the range Target is in. So this ensures that Range("C6, C9:G9") points to the same worksheet as Target points to. Just to be save and not let Excel decide which sheet I mean.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.