help on triggering macro if cells change

[cuewoz]

Hi
I have checked around but cannot seem to find out an answer for this.

My worksheet has 24 cells where I am able to enter a number, and I want to be able to trigger a macro when a cell has a new number input.

This I can do with

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Intersect(Target, Range("C31")) Is Nothing Then

Exit Sub
Else


If True Then Run ("gettemp1")

End If
Application.ScreenUpdating = True

End Sub

This only works if you only have 1 cell to check on.

How can I check on 24 different cells and each triggering a different macro?

I thought I may have to add a command button after each cell but the user may forget to push it and not update the rest of the sheet.

Thanks in advance.

Warren

[Bob Phillips]

This will check a change in any of C31:F37

If Intersect(Target, Range("C31:F37")) Is Nothing Then

This will check a change in any of C31:C37 or G31:G37

If Intersect(Target, Range("C31:C37, G31:G37")) Is Nothing Then

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"cuewoz" <[email protected]> wrote in
message news:[email protected]...
>
> Hi
> I have checked around but cannot seem to find out an answer for this.
>
> My worksheet has 24 cells where I am able to enter a number, and I want
> to be able to trigger a macro when a cell has a new number input.
>
> This I can do with
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Application.ScreenUpdating = False
> If Intersect(Target, Range("C31")) Is Nothing Then
>
> Exit Sub
> Else
>
>
> If True Then Run ("gettemp1")
>
> End If
> Application.ScreenUpdating = True
>
> End Sub
>
> This only works if you only have 1 cell to check on.
>
> How can I check on 24 different cells and each triggering a different
> macro?
>
> I thought I may have to add a command button after each cell but the
> user may forget to push it and not update the rest of the sheet.
>
> Thanks in advance.
>
> Warren
>
>
> --
> cuewoz
> ------------------------------------------------------------------------
> cuewoz's Profile:
http://www.excelforum.com/member.php...o&userid=32161
> View this thread: http://www.excelforum.com/showthread...hreadid=519139
>

[Gary''s Student]

In addition to Bob's comments on testing target against a general range.
Once you have decided not to exit, you can test each cell in the range
against target to decide what to do:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Intersect(Target, Range("A1:B1")) Is Nothing Then
Exit Sub
Else
If Not (Intersect(Target, Range("A1")) Is Nothing) Then
MsgBox (Range("A1").Value)
Else
MsgBox (Range("B1").Value)
End If
End If
Application.ScreenUpdating = True
End Sub
--
Gary''s Student


"cuewoz" wrote:

>
> Hi
> I have checked around but cannot seem to find out an answer for this.
>
> My worksheet has 24 cells where I am able to enter a number, and I want
> to be able to trigger a macro when a cell has a new number input.
>
> This I can do with
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Application.ScreenUpdating = False
> If Intersect(Target, Range("C31")) Is Nothing Then
>
> Exit Sub
> Else
>
>
> If True Then Run ("gettemp1")
>
> End If
> Application.ScreenUpdating = True
>
> End Sub
>
> This only works if you only have 1 cell to check on.
>
> How can I check on 24 different cells and each triggering a different
> macro?
>
> I thought I may have to add a command button after each cell but the
> user may forget to push it and not update the rest of the sheet.
>
> Thanks in advance.
>
> Warren
>
>
> --
> cuewoz
> ------------------------------------------------------------------------
> cuewoz's Profile: http://www.excelforum.com/member.php...o&userid=32161
> View this thread: http://www.excelforum.com/showthread...hreadid=519139
>
>

[Ardus Petus]

select case Target.address
case "$A$1"
do something
case "$G$9"
do something else
...
end select

HTH
--
AP

"cuewoz" <[email protected]> a écrit dans
le message de news:[email protected]...
>
> Hi
> I have checked around but cannot seem to find out an answer for this.
>
> My worksheet has 24 cells where I am able to enter a number, and I want
> to be able to trigger a macro when a cell has a new number input.
>
> This I can do with
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Application.ScreenUpdating = False
> If Intersect(Target, Range("C31")) Is Nothing Then
>
> Exit Sub
> Else
>
>
> If True Then Run ("gettemp1")
>
> End If
> Application.ScreenUpdating = True
>
> End Sub
>
> This only works if you only have 1 cell to check on.
>
> How can I check on 24 different cells and each triggering a different
> macro?
>
> I thought I may have to add a command button after each cell but the
> user may forget to push it and not update the rest of the sheet.
>
> Thanks in advance.
>
> Warren
>
>
> --
> cuewoz
> ------------------------------------------------------------------------
> cuewoz's Profile:
http://www.excelforum.com/member.php...o&userid=32161
> View this thread: http://www.excelforum.com/showthread...hreadid=519139
>

[Tom Ogilvy]

If Intersect(Target, Range("C31").Resize(24,1)) Is Nothing Then


If they are not contiguous

If intersect(Target,Range("C31,D9:D15,F11,G39,. . .,M3:M2")) is nothing then
--
Regards,
Tom Ogilvy

"cuewoz" <[email protected]> wrote in
message news:[email protected]...
>
> Hi
> I have checked around but cannot seem to find out an answer for this.
>
> My worksheet has 24 cells where I am able to enter a number, and I want
> to be able to trigger a macro when a cell has a new number input.
>
> This I can do with
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Application.ScreenUpdating = False
> If Intersect(Target, Range("C31")) Is Nothing Then
>
> Exit Sub
> Else
>
>
> If True Then Run ("gettemp1")
>
> End If
> Application.ScreenUpdating = True
>
> End Sub
>
> This only works if you only have 1 cell to check on.
>
> How can I check on 24 different cells and each triggering a different
> macro?
>
> I thought I may have to add a command button after each cell but the
> user may forget to push it and not update the rest of the sheet.
>
> Thanks in advance.
>
> Warren
>
>
> --
> cuewoz
> ------------------------------------------------------------------------
> cuewoz's Profile:
http://www.excelforum.com/member.php...o&userid=32161
> View this thread: http://www.excelforum.com/showthread...hreadid=519139
>

[cuewoz]

Thanks for those useful replys They helped a lot!

Unfortunatly they dont seem to work for cells that change but by way of a drop down menu changing a cell contents.
any ideas?

[Bob Phillips]

You must have Excel97.

Set another cell to refer to the DV cell (=D10 for instance), and test in a
Worksheet_Calculate event.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"cuewoz" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for those useful replys They helped a lot!
>
> Unfortunatly they dont seem to work for cells that change but by way of
> a drop down menu changing a cell contents.
> any ideas?
>
>
> --
> cuewoz
> ------------------------------------------------------------------------
> cuewoz's Profile:
http://www.excelforum.com/member.php...o&userid=32161
> View this thread: http://www.excelforum.com/showthread...hreadid=519139
>