VBA newbie needs help with triggering a macro

[mav93]

Hi Everybody!!!!

I need some help with a solution for running a macro that I recorded.
Just so you know recording macros is about the limit of my VBA
knowledge.

Here's my problem!!!!!
Right now I have a shortcut of ctrl f that triggers my Macro. I would
like to trigger the macro when specific cells in sheet 3 are changed.

I want to run a macro if cells $K$3, or $J$3, or $E$10, or $E$11
are changed, changed in any way (no criteria)

example:
If $K$3 is changed from true to false by a check box 83
or if $E$10 is changed from blank to "lumber" by way of Data validation
list or $E$11 is Changed from "steel" to blank

all of these events should run my macro

For your information

$K$3 is linked to Forms check box 83

$J$3 is linked to Forms checkbox 86

$E$10 has a data valitdation list in it

$E$11 has a data valitdation list in it

also I need the macro to be triggered upon entering sheet3

the macro is in module #2 and is called Macro2

Thanks for any help in advance it is really appreciated

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "K3,J3,E10,E11"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Call myMacro
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[mav93]

Bob

Thanks a lot for the code, there are a couple of problems though

Two of the cells that trigger the macro are linked to check boxes

J3 is linked to forms check box 83
K3 is linked to forms check box 86

I'm not sure what to tell you the problem is but I do know that when I
manually typed in true or false (refering to whether or not the check
box was checked) into J3 or K3 your code worked great. So i'm assuming
that the checking the check boxes does't actually cause J3 or K3 to
change.

I'm not sure how to make the checking a forms check box a change
event?

Also, is there a way to trigger the macro each time the I enter the
sheet?

Thanks agin for any help

 

[David McRitchie]

For the last part of your question
You can trigger a macro upon sheet activation
http://www.mvps.org/dmcritchie/excel/event.htm
look for Worksheet_Activate

The first part you indicated the checkmark is on a form, so
don't have an answer for you. If on a worksheet it would
be a change event and you would test the value.