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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

lhernandez

Active Member
Joined
May 22, 2006
Messages
282
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 :)
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

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
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

lhernandez

Active Member
Joined
May 22, 2006
Messages
282

ADVERTISEMENT

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?
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,398
Messages
5,547,729
Members
410,810
Latest member
lilwayne
Top