If statement with macro

[bbot23]

I google'd the code to run a macro depending on a met condition, but I can't seem to make it call the macro. Can anyone help? Here's the code that I got:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("AG6")) Is Nothing Then
Exit Sub
End If

If Range("AG6") = 1 Then
SomeMacro
End If

End Sub

Sub SomeMacro()
MsgBox ("Hi!")
End Sub

If I change AG6 to 1, nothing happens. Help?

 

[cb12]

The code looks fine. Make sure you're putting it into the relevant sheet

 

[Dryver14]

I have just tried this out and it works fine, you can not get it to run by
pressing F5 or F8 in the VBA Editor , you have to go to AG6 and place a 1 in it.

 

[Ruddles]

That code needs to be in the code module of the same worksheet that AG6 is in. Right-click the tab for that sheet and select View Code. That's where it goes.

It will trigger when AG6 changes, so if it's already equal to 1, you need to go into edit on the cell and press Enter.

 

[Marcelo Branco]

Hi,

Maybe you have disabled events involuntarily. Just in case, copy the code below

Sub enblEvents()
    Application.EnableEvents = True
End Sub

and with the cursor inside press F5

HTH

M.

 

[bbot23]

Here's a little update:

If I type in "1" in AG6, it works. But there's a formula in AG6 that makes AG6 equal to 1 if a condition in another cell is met. When that condition is met and AG6 changes to 1, the macro is not launched.

Help? Why doesn't it work?

 

[Marcelo Branco]

Because the Worksheet_Change event is not triggered by a formula

Try this

Private Sub Worksheet_Calculate()
If Range("A6") = 1 Then
SomeMacro
End If
End Sub


But be aware that everytime a calculation occurs, even in another cell(s), the message wil appear. Probably you dont want this...

HTH

M.

 

[Marcelo Branco]

Complementing my previous post.

If you tell us the formula you are using in AG6 (sorry, i wrongly wrote A6 in the previous post) maybe we can arrange the things better.

M.

 

[bbot23]

Aye, the best would be if ONLY the result changes in the cell with the formula. Here's the formula, which is a bit complicated and might not mean much honestly!

=IF(AND(F6="BUY",IF(H6>U6, AF6<AE6, AF6>AE6)),1,IF(AND(F6="SHORT",IF(H6>U6,AF6>AE6,AF6< AE6)),1," "))

I guess what I could do is make AH6 equal to 1 if AG6 =1 and that would make a formula that looks like

=IF(AG6=1, 1, "")

 

[Marcelo Branco]

If we could relate an user-entry in some cell with the change of the value in AG6 by the formula, maybe we can solve the problem.

Say that if the user enter a new value in F6 (or H6, U6...) , by your formula it would change the value of AG6. Then, i think, we could use the Worsheet_Change event triggered by the change in F6 (that would be the Target) and check if AG6 = 1...

Otherwise, honestly, i dont know how to solve it (maybe creating a class but this is beyond my knowledge)

M.