Worksheet Change Event

DBaker7777

Board Regular
Joined
Feb 3, 2009
Messages
52
Office Version
  1. 365
  2. 2016
I am trying to combine the following different intersects. Any help is appreciated :


VBA Code:
private sub worksheet_change(byval target as range)

Dim Fnd As Range
If target.countlarge > 1 then exit sub
If intersect (target, range (“b3:b23”) is nothing then exit sub
Application.enableevents=false
Set fnd = Sheets(“sheet2”).range (“a2:a25”).find(target.value, , , xlwhole, , , false, , false)
If not fnd is nothing then
Target.offset (, 2).value = “Yes”
End if
Dim Bnd as Range
If target.countlarge > 1 the. Exit sub
If intersect (target.range. (“C3:c23”) is nothing the. Exit sub
Application.enableevents=false
Set bnd =Sheets(“sheet2”).range (“A2:A25”).find(target.value , , , xlwhole , , , false , , false)
If not bnd is nothing the
Target.offset),1).value = “No”
End if
Application.EnableEvents = True
End sub
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,276
May be this following approach:
VBA Code:
If target.CountLarge > 1 Then Exit Sub
    If Not Intersect(target, Range("b3:b23")) Is Nothing Then
        'what to do witd the first range
        
    ElseIf Not Intersect(target, Range("C3:C23")) Is Nothing Then
        'what to do with the second range
    
    End If

End If

Try...
 
Solution

DBaker7777

Board Regular
Joined
Feb 3, 2009
Messages
52
Office Version
  1. 365
  2. 2016
Thank you for the response. Nothing happened when I entered data into a cell in column b or c.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,782
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Note that you have to be real careful when you use code like:
VBA Code:
Application.EnableEvents=False

It is important to understand what this does. It disables event procedure code (like "Worksheet_Change") from running, so you do not get caught in an infinite loop.
The logic is if you have code that runs when values are changing, and your code itself changes values, then you need to temporarily disable events so the changes made by the code do not call the code again, and you get caught in a loop.

So quite often, you temporarily disable events with that line of code, make the changes, and then re-enable it with:
VBA Code:
Application.EnableEvents=True

However, what sometimes happens is if your code runs and disables the code, and either it errors out, or exits before it turns them back on, this automated event procedure code won't run anymore in that Excel session until you turn events back on.

You can do that by adding the procedure and running it manually:
VBA Code:
Sub ReEnableEvents()
    Application.EnableEvents=True
End Sub
 

DBaker7777

Board Regular
Joined
Feb 3, 2009
Messages
52
Office Version
  1. 365
  2. 2016
Could you share the code you used?
VBA Code:
Dim Fnd As Range
If target.countlarge > 1 then exit sub
If not intersect (target, range (“b3:b23”) is nothing then
Application.enableevents=false
Set fnd = Sheets(“sheet2”).range (“a2:a25”).find(target.value, , , xlwhole, , , false, , false)
If not fnd is nothing then
Target.offset (, 2).value = “Yes”
elseIf not intersect (target.range. (“C3:c23”) is nothing then
Application.enableevents=false
Set bnd =Sheets(“sheet2”).range (“A2:A25”).find(target.value , , , xlwhole , , , false , , false)
If not bnd is nothing the
Target.offset),1).value = “No”
[/QUOTE]

[QUOTE="Anthony47, post: 5754346, member: 60147"]

Could you share the code you used?
VBA Code:
Private Sub Worksheet_Change(byval target as range)

Dim Fnd As Range
If target.countlarge > 1 then exit sub
If Not Intersect (Target, Range (“B3:B23”) is nothing then
Application.enableevents=false
Set Fnd = Sheets(“Sheet2”).range (“A2:A25”).Find(Target.value, , , xlwhole, , , false, , false)
If not fnd is nothing then
Target.offset (, 2).value = “Yes”
ElseIf Not Intersect (Target.Range. (“C3:C23”) is nothing then
Dim Bnd as Range
Set bnd = Sheets(“Sheet2”).range (“A2:A25”).find(target.value , , , xlwhole , , , false , , false)
If not bnd is nothing the
Target.offset (,1).value = “No”
End If
End If
Application.EnableEvents = True
End sub
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,782
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Nothing happened when I entered data into a cell in column b or c.
Did you heed the advice in my last post, run that short macro, and try again?
 

DBaker7777

Board Regular
Joined
Feb 3, 2009
Messages
52
Office Version
  1. 365
  2. 2016
VBA Code:
Private Sub Worksheet_Change(byval target as range)

Dim Fnd As Range
If target.countlarge > 1 then exit sub
If Not Intersect (Target, Range (“B3:B23”) is nothing then
Application.enableevents=false
Set Fnd = Sheets(“Sheet2”).range (“A2:A25”).Find(Target.value, , , xlwhole, , , false, , false)
If not fnd is nothing then
Target.offset (, 2).value = “Yes”
ElseIf Not Intersect (Target.Range. (“C3:C23”) is nothing then
Dim Bnd as Range
Set bnd = Sheets(“Sheet2”).range (“A2:A25”).find(target.value , , , xlwhole , , , false , , false)
If not bnd is nothing the
Target.offset (,1).value = “No”
End If
End If
Application.EnableEvents = True
End sub
Nevermind, I got it to work with the above. sorry for the trouble and thank you for the help!
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,276
I got it to work with the above. sorry for the trouble and thank you for the help!
Thank you for the feedback.

Don't ignore Joe's warning: when you seem that an event is not triggered that run his small macro:
VBA Code:
Sub ReEnableEvents()
Application.EnableEvents=True
End Sub
In case excel has been left with the "event detection" disabled that macro will reenable it (without the need for restarting Excel)

Bye
 

DBaker7777

Board Regular
Joined
Feb 3, 2009
Messages
52
Office Version
  1. 365
  2. 2016
Thank you for the feedback.

Don't ignore Joe's warning: when you seem that an event is not triggered that run his small macro:
VBA Code:
Sub ReEnableEvents()
Application.EnableEvents=True
End Sub
In case excel has been left with the "event detection" disabled that macro will reenable it (without the need for restarting Excel)

Bye
I did add Joe’s code. Appreciate both of you. Thank you
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,699
Messages
5,766,001
Members
425,322
Latest member
galaxy6623top

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top