Run Macro whenever this cell changes

[roheba]

When I run Macro1, in my worksheet, it performs a goal-seek process then stops. I want to repeat this macro one time whenever a change is made to Cell C14. I think this is a worksheet_change event. I have tried repeatedly, but unfortunately, my knowledge and skill-set are inadequate to write the VB code necessary to make this happen. Please help.

 

[Shahzad Taimoor]

it is a worksheet change event, try and include the following in the sheet code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$14" Then
Call Macro1
End If
End Sub

i hope this will solve your problem

 

[roheba]

Thank you for your suggested solution.

It seems so simple, but... "try and include the following in the sheet code"

Please tell me where and how to add this to the worksheet.

 

[roheba]

My active sheet is Sheet1.

Before I make the modifications indicated below, please accept that my Macro1 functions quite well.

In an effort to implement the suggested Worksheet_Change code, I went to the VB code page for Sheet1 and changed from (General) and Declarations to Worksheet and Change. Then I entered the above code.

Back on Sheet1, I changed the contents of Cell C14. I immediately receive a dialogue box indicating there is a VB runtime error "1004", Reference isn't valid. When I press the Debug button I'm shown Macro1 with the line defining the parameters for the GoalSeek highlighted in yellow.

This is the line of code that stops the process.: Range("C44").GoalSeek Goal:=0, ChangingCell:=Range("C29")

Apparently the GoalSeek process is incompatible with the Worksheet_Change code. Any suggestions?

 

[Shahzad Taimoor]

I am not sure what you have in Macro1.

Try running the Macro1 independently or you may execute the Macro step by step by press "F8" for each step and check where the problem is. Perhaps if the Macro1 runs perfectly, depending and executing the Macro1 each time on a value change in C14 should not be a problem.