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?

 

[bbot23]

Hmm, well thanks for the effort! I'll re-post the question in a new thread with the new problem.

 

[Ruddles]

Aye, the best would be if ONLY the result changes in the cell with the formula.

All you need is a mechanism for detecting when only AG6 changes. For this you'll need to spare a cell somewhere on your worksheet to record the previous value of AG6 so that when the sheet recalculates, it can check whether AG6 was changed. If we use AZ1 for this purpose:-

[FONT=Courier New]Option Explicit[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]Private Sub Worksheet_Calculate()[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]  If Range("AZ1").Value <> Range("AG6").Value Then [COLOR=green]' AG6 has changed
[/COLOR]    Range("AZ1") = Range("AG6").Value              [COLOR=green]' store its new value in AZ1
[/COLOR]    If Range("AG6") = 1 Then SomeMacro
  End If
[/FONT]
[FONT=Courier New]End Sub[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]Sub SomeMacro()
MsgBox ("Hi!")
End Sub
[/FONT]

If AZ1 isn't convenient, you can use any other cell you want.

 

[Marcelo Branco]

Ruddles,

Well done! This is a good and practical solution.

I think that using the Precedents property we can achieve a solution too.

Something like this.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("AG6").Precedents) Is Nothing Then
        If Range("AG6") = 1 Then
            SomeMacro
        End If
    End If
    
End Sub
 
Sub SomeMacro()
   MsgBox ("Hi!")
End Sub

M.

 

[Ruddles]

I think that using the Precedents property we can achieve a solution too.

Never heard of it! Google here I come...

 

[Marcelo Branco]

Never heard of it! Google here I come...

Till yesterday i also had never heard of it.

Thinking about how to solve this thread... i Googled and....

http://msdn.microsoft.com/en-us/library/bb208963(v=office.12).aspx

http://msdn.microsoft.com/en-us/library/aa175240(v=office.11).aspx


M.