Trigger a Macro from the result of a function? "If A1= Yes, Enable Macro1"

[apalmer]

I have a program in which I import live data into excel and feed i
through a set of conditions I have made, if my conditions are met
cell will populate with the word "yes", if my conditions are unmet th
cell will display the word "no". I have a total of 6 cells next to eac
other, 2 rows and 3 across, which are programmed to display "yes" o
"no" depending on if the conditions are met.



What I need to do is have a macro triggered off when the word "yes" i
displayed in any of those 6 cells, doesn't matter which, this macr
will then highlight that area of cells and "insert the copied cells
down below.



I know how to record the macro, what I really need is a way for thi
macro to be triggered off by the product of any 6 particular cells; i
any cell displays “yes” enable macro.



My programming skills are limited, so if you could please be a
specific as possible if speaking about anything in visual basic.



Thank you.

Ala

 

[Dave Peterson]

Excel has some events that you can tie into. Since your 6 cells are formulas,
you could tie into the worksheet_calculate event.

That means that each time the worksheet is calculated and a "yes" is found in
one of those 6 cells, the macro would run. This might not be what you really
want. Worksheets get calculated a lot.

But if you want to learn about events...

Chip Pearson has notes about worksheet events:
http://www.cpearson.com/excel/events.htm

David McRitchie's notes:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[michael.beckinsale]

Alan,

From my interpretation of your posting the following code should do

what you want. There are probably better ways to do this but the code
is easily readable to a novice and you should be able to edit the code
to suit your exact requirements

Sub testYESx()

Dim countyes As Integer
Dim myRange As Range

Set myRange = Sheets("Sheet1").Range("A1:C2")
countyes = 0
For Each iCell In myRange
If iCell.Value = "Yes" Then
countyes = countyes + 1
End If
Next

If countyes > 0 Then
Call myMacro >>>>> SUBSTITUTE YOUR MACRO NAME HERE!!!!
Else
MsgBox ("There are no instances of YES")
Exit Sub
End If


End Sub

Regards Michael Beckinsale

 

[Dave Peterson]

And to add to michael's code...

You (Alan) may want to put a button from the forms toolbar on that same
worksheet and then assign this code to that button. But it won't be
automatic--it'll depend on the user clicking the button.

 

[apalmer]

I really appreciate all your help. I have a couple more question
regarding this issue. I am having a problem implementing Michael’
code. I can’t get it to run the macro. I am going to MVB, righ
clicking on sheet 1, clicking "view code", and pasting the code yo
gave me, changing the macro to "Macro1" or the macro I am using. The
I actually go to one of those cells and manually type "yes" into it
nothing happens.

I'll try to be more specific with my specific program. The followin
cells will display either the words "nothing" or "No", which are all o
Sheet 2, "B4, F4, I4" and the following cells will display either th
words "nothing" or "Yes", "B6, F6, I6". A total of 6 cells. Mayb
this will help, I would like the macro titled "Macro1" to run if an
one of those cells change to anything besides the word "nothing"
doesn't matter if its "yes" or "no", just as long as its not the wor
"nothing". Now these cells should only change 3 to 4 times per day
for short periods of time before going back to nothing.

I have fooled around using other sample codes in MVB to trigger macro'
based on a condition and have got a macro to run based on the product o
a cell, however only if i type the right thing into that exact cell an
hit "enter". For example Macro5 will run if cell A5 displays th
number 8, but will only run if I go to cell A5 and enter the number
and press enter. However if I make A5=B3 and type into B3 the numbe
8, the macro will not enable. Does the fact that its a function o
something matter?

I really thank you for taking the time to read through this and woul
appreciate your help

 

[michael.beckinsale]

Alan,

I must have misread your post as l did not relaise you wanted the macro
to fire automatically. Using your example the following code will fire
if the user enters anything into the target cells expect the word
'nothing'.

The code must be placed, as in your example, in the sheet 2 code module
and the Worksheet_Change event.

Please note that if the values in the target cells are changed by way
by of a formula the event will not trigger. The target addresses must
be cells that are changed manually.


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$4" Or _
Target.Address = "$F$4" Or _
Target.Address = "$I$4" Or _
Target.Address = "$B$6" Or _
Target.Address = "$F$6" Or _
Target.Address = "$I$6" Then
If Target.Value <> "Nothing" Then
MsgBox ("You have entered something") >>>CHANGE THIS TO CALL
YOUR MACRO
End If
End If

End Sub

Regards

Michael Beckinsale