Calling a Macro From a Cell 2

[jsaliers]

Here's what I need. I have a target cell A2, a compare cell B2, and a macro call cell C2. To start, A2 = B2. Eventually, B2 will be changed. I want to set up C2 to call a macro I already wrote (&quot;test_macro&quot when A2<>B2. I tried the following formula in C2:

=IF(A2<>B2,CALL(&quot;kernel32&quot;,&quot;test_macro&quot;,II))

Not sure if the call function will work, or if the syntax is correct. I couldn't make heads or tails of the help on the CALL_worksheet_function.

Any help will be much appreciated! Thanks in advance.

Jon

 

[SkipVought]

Hi,

If your procedures are set up as functions -- is a function returns a single value based on parameter(s) or not from the worksheet...

=IF(A2<>B2,kernel32,test_macro)

Si if A2<>B2 then function kernel32 will return a value else test_macro will return a value.





Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[jsaliers]

This is the formula in cell C2:

=IF(A2<>B2,test_macro,&quot;Unchanged&quot

Now, if I change cell A2 so A2<>B2, the function test_macro should run. Here is the code for test_macro:

Function test_macro()

Dim targetValue As Integer

'Get target value
targetValue = Range(&quot;A2&quot.Value

'Message Box telling user the new value of target cell
MsgBox (&quot;The new target value is: &quot; & targetValue & &quot;!&quot

'Set Compare cell equal to target cell
Range(&quot;B2&quot.Value = targetValue

End Function

However, when I change A2 so A2<>B2, the function doesn't run, as the message box doesn't appear, and the text in cell C2 changes from &quot;Unchanged&quot; to &quot;#NAME?&quot;.

Any ideas? Thanks in advance.

Jon

 

[SkipVought]

you are not programming the function properly. And what's the point?

Function test_fn(rng as Range)
   test_fn = rng.value
End Function

you are going to return the value of the referenced cell.???

Again, what's the point?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[jsaliers]

The point is not to return the value of the referenced cell. I merely put that into the macro so I would know if the macro ran properly or not.

The point is that I need to be able to call a macro when the value of a cell changes. I don't want to bog it down by constantly looping another macro, or for that matter the same macro, checking to see if the value has changed. I want to find a way to call the macro/function from the worksheet when the value is changed. Note that the value will not be changed by hand, or I could simply use a button to trigger it every time it changed.

I was using a simplified worksheet and macro/function to get the function call down. Then I will add the real macro functionality.

 

[SkipVought]

You CANNOT call a SUB macro from a worksheet.

You CAN call a user-defined macro FUNCTION as per the example I posted that returns a single value in the cell from which it is called.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[jsaliers]

Okay, I must be losing my mind now. This is what I have. A value (default 0) in cell A2, a value (default 0) in cell B2, and a formula in cell C2. There is also a number in cell D2 that shows the number of times there has been a change in cell A2.

I want the formula to run function test_func if A2<>B2. If A2=B2, I want the cell to display the text &quot;UNCHANGED&quot;. Currently, my formula looks like this:

=IF(A2<>B2,test_func,&quot;UNCHANGED&quot

I want my function to set cell B2 to the same value as A2, and increment cell D2. This is what my function looks like right now:

Function test_func()

Dim targetValue As Integer
Dim counter As Integer

'Get target value
targetValue = Range(&quot;A2&quot.Value

'Set Compare cell equal to target cell
Range(&quot;B2&quot.Value = targetValue

'Increment the counter cell
counter = Range(&quot;D2&quot.Value
Range(&quot;D2&quot.Value = counter + 1

End Function

When I change the value of A2 to 1, cell C2 displays the following text:

&quot;#NAME?&quot;

How can I fix this? Thanks in advance!

Jon

 

[jsaliers]

Sorry, the error cell C2 displays is &quot;#VALUE!&quot;

 

[SkipVought]

READ MY &quot;LIPS&quot;

A Function is constructed in the form...

Function test_fn()
....
   test_fn = SomeValue
End Function

[/code]

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

No big deal. Just process the Worksheet_Change event and if the Target includes cells A2 or B2 then do the compare. Call a subroutine when the values don't match.

 

[SkipVought]

Z,

Won't work unless a CHANGE occurrs which will not necessarily happen if the values he's testing are refrenced to cells on another sheet.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

Skip, good point. But he should be able to &quot;walk back the cat&quot; to see where a user can change a cell and trigger the event. Or if the change is made by another macro, that macro can include a call to the routine.

jsaliers, the thing is, there is no syntax available to implement your idea as expressed (with an IF statement in a cell formula), however you can accomplish you purpose by trapping a Worksheet_Change event for the appropriate cell(s).

 

[jsaliers]

Okay, one more time here. I used Skip's idea here. This is the formula in cell C2:

=IF(A2 <> B2,test_func(A2),&quot;Unchanged&quot

This is the code I have:

Function test_func(tv As Range)

    Charlie_Macro
    test_func = tv

End Function

Sub Charlie_Macro()

    MsgBox ("1")

    'Make Declarations
    Dim targetValue As Integer
    Dim counter As Integer

    'Get target value
    targetValue = Range("A2").Value

    'Set Compare cell equal to target cell
    Range("B2").Value = targetValue

    'Increment the counter cell
    counter = Range("D2").Value + 1
    Range("D2").Value = counter

End Sub

When I change cell A2 to 1, the message box pops up, then the value of C2(formula cell) goes to #VALUE!, and that is it. SO, it is entering the function, entering the sub, but after the message box, it dies somewhere. When I just run the sub, it works fine, but when I make a change in the sheet, it doesn't update cells B2(compare cell) and D2(count cell).

 

[SkipVought]

The place that your function craters is

Range("B2").Value = targetValue

It is a NO NO to change a value OUTSIDE of the function.

What you want to do cannot be done with a function.

As Zathras states, it can be dome using the worksheet change event calling the charlie_macro procesure.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[jsaliers]

Thank you both for your help. I obviously am not doing something correctly, because it still is not working. I set up the Worksheet_Change event, and called the Charlie_Macro, but it doesn't trigger when I change the value of A2, or any other cell for that matter. I don't know what the problem is. In case you see what I am doing wrong, here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Call Charlie_Macro

End Sub

Sub Charlie_Macro()

    MsgBox ("1")
    
    'Make Declarations
    Dim targetValue As Integer
    Dim counter As Integer
    
    'Get target value
    targetValue = Range("A2").Value
    
    'Set Compare cell equal to target cell
    Range("B2").Value = targetValue
    
    'Increment the counter cell
    counter = Range("D2").Value + 1
    Range("D2").Value = counter
    
End Sub

Thanks again guys.

Jon

 

[SkipVought]

Put charlie_macro in a Module and NOT in the worksheet object

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[jsaliers]

Alright, now I am a little lost. Both the change event and the macro are in a module called TestFunctions. But by the way your response was worded, it sounded as if the macro is supposed to be in a module, while the event is supposed to be in the worksheet object????

If this is the case, I have not the slightest idea how to put an event into the worksheet object. As a matter of fact, I am not sure I understand what you mean by worksheet object.

Jon

 

[SkipVought]

BTW,

You are going to experience a recursive change with your code.

add this in the change event

Private Sub Worksheet_Change(ByVal Target As Range)
  application.EnableEvents =false
    Call Charlie_Macro
  application.EnableEvents =true
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

oh and I'd add something else, too

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
    Set rng = Application.Intersect(Target, Union(Range("A2"), Range("B2")))
    If Not rng Is Nothing Then Charlie_Macro
  Application.EnableEvents = True
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

Easiest way:

From the worksheet:
1. Right-click on the sheet tab.
2. Choose &quot;View Code&quot;.
3. Observe that Excel automatically put a skeleton SelectionChange event handler macro for you. (Delete it. If you need it later, you can easily put it back.)
4. From the combo box in the upper-right hand area of the window, select &quot;Change&quot;
5. Follow Skip's instructions above.

Alternate way:
1. Alt-F11 to open VBA editor.
2. Double-click &quot;Sheet1&quot; in the project explorer. (Ctrl-R if the project explorer isn't showing.)
3. Select &quot;Worksheet&quot; from the left-hand combo box at the top of the window.
4. Select the event to trap from the right-hand combo box at the top of the window.
5. Follow Skip's instructions.