Updateing info using Change macro

lhernandez

Active Member
Joined
May 22, 2006
Messages
282
I have written a macro in sheet 2 _change (ByVal target as range).
Anytime I change something in this worksheet this macro is run, i there a way I can i can set up this macro to run only when a certain cell is changed in stead of when anything in the worksheet is changed?
Any help would be great!
Thank you
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
You can define the target range in a change event:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
         <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#007F00">'   Set Target Range</SPAN>
        Set rng = Range("A1")
        <SPAN style="color:#007F00">'   Only look at that range</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
            <SPAN style="color:#007F00">'   Action if Condition(s) are met</SPAN>
            
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty
 
Upvote 0
Hi

There are many ways to limit the Change Event
Code:
If Target.Address <> "$C$4" Then Exit Sub ' Works only on $C$4
If Target.Column <> 3 Then Exit Sub 'Works only on column C
If Target.Column > 4 Then Exit Sub " Works only on column A,B,C,D
'similar for row
If Intersect(Target, Range($A$1:$C$10)) Is Nothing Then Exit Sub 'Works for that range
Numerous other ways

lenze
 
Upvote 0
This seems what I am looking for, but I am having a little difficulty applying it to my macro. can you please clear something up for?

The idea I was trying to use for thie _change worksheet was whenever cells "O3" = true execute the following steps, if it is false then execute a different set of steps. And I only wanted it to happen when cell O3 was changed not when anything in the worksheet changed.
So I am confused as to how to apply your code to what I am trying to do.

Thank you for the help :)
 
Upvote 0
You just need a slight ammendment:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
         <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#007F00">'   Set Target Range</SPAN>
        Set rng = Range("O3")
        <SPAN style="color:#007F00">'   Only look at that range</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
            <SPAN style="color:#007F00">'   Action if Condition(s) are met</SPAN>
            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target
                <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">True</SPAN>
                    <SPAN style="color:#007F00">'   Do your True thing</SPAN>
                    MsgBox <SPAN style="color:#00007F">True</SPAN>
                <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">False</SPAN>
                    <SPAN style="color:#007F00">'   Do your False thing</SPAN>
                    MsgBox <SPAN style="color:#00007F">False</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Smitty
 
Upvote 0
OK
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count>1 Then Exit Sub
    If Target.Address <> "$O$3" Then Exit Sub
    Select Case Target
    Case "True"
        'somecode
    Case "False"
        'different code
    Case Else: Exit Sub
    End Select
End Sub

Both Smitty and I are assuming O3 is changed manually. If from a formula, neither of our codes will work

HTH

lenze
 
Upvote 0
Thank you two so much for your help, this is exactly what I was looking for. I just have one more question: is there a way I can use that code if O3 is a linked cell from a check box that toggles between true or false?
 
Upvote 0
Give it a try and see if the code fires, but I don't think it will work. Why not use Data Validation dropdown instead?

lenze
 
Upvote 0
Select the Cell

Choose Data>Data Validation

From the menu, select List

In the allows field enter

True,False

Click OK

You will get a drop down with the choices of True or False when That cell is active

lenze
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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
Back
Top