Run macro automatically when a cell value changes

[prabhadixit]

Hi

I have a piece of code that populates values in various cells in a form (Excel Worksheet) based on value of C5 of same worksheet. The code also contains statements to hide a certain row if the values in this row is 0. However, I find that I need to run this macro manually each time. I want the macro to run automatically everytime the value in cell C5 changes. Someone suggested that I use Worksheet_Change with Target cell specified as C5. But now this gives a run time error. This is my code.


Sub Master()
Call Worksheet_Change(C5)
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$5" Then
'Range("C5").Select
'C1 = ActiveCell.Value
' Do
MsgBox ("Am I here")
Rows("22:22").Select
Selection.EntireRow.Hidden = False
MsgBox ("Yea, I am!")
Range("C4").Select
ActiveCell.FormulaR1C1 = "= VLOOKUP(R[1]C,'[COMERCIAL_VII1(1)_test version.xls]dif de comp con ajuste de sueld'!RC[-1]:R[114]C[120],6,FALSE)"
Range("C7").Select
ActiveCell.FormulaR1C1 = "= VLOOKUP(R[-2]C,'[COMERCIAL_VII1(1)_test version.xls]dif de comp con ajuste de sueld'!R[-3]C[-1]:R[114]C[120],11,FALSE)"
Range("C8").Select
ActiveCell.FormulaR1C1 = "= VLOOKUP(R[-3]C,'[COMERCIAL_VII1(1)_test version.xls]dif de comp con ajuste de sueld'!R[-4]C[-1]:R[115]C[121],50,FALSE)"
Range("C6").Select
ActiveCell.FormulaR1C1 = "= VLOOKUP(R[-1]C,'[COMERCIAL_VII1(1)_test version.xls]dif de comp con ajuste de sueld'!R[-2]C[-1]:R[113]C[119],15,FALSE)"
Range("D15").Select
ActiveCell.FormulaR1C1 = "= VLOOKUP(R[-10]C[-1],'[COMERCIAL_VII1(1)_test version.xls]dif de comp con ajuste de sueld'!R[-11]C[-2]:R[104]C[118],14,FALSE)"
Range("D22").Select
j = ActiveCell.Value
MsgBox ("Value first is" & j)
ActiveCell.FormulaR1C1 = "= VLOOKUP(R[-17]C[-1],'[COMERCIAL_VII1(1)_test version.xls]dif de comp con ajuste de sueld'!R[-18]C[-2]:R[97]C[118],37,FALSE)"
j = ActiveCell.Value
MsgBox ("Value second is" & j)
If j = 0 Then
MsgBox ("Im inside the loop!")
Rows("22:22").Select
Selection.EntireRow.Hidden = True
MsgBox ("Im leaving the loop!")
End If
'Range("C5").Select
'C2 = ActiveCell.Value
'Loop While C1 <> C2
End If
End Sub

[Bob Phillips]

Put it in the worksheet code module and it will.

[prabhadixit]

Thanks XLD, but Im very new to VBA, can you tell me how to put it in the worksheet module?
Also do I need a separate Master sub calling the Worksheet_Change sub? If so, what is the following syntax correct to call the
Private sub?
Sub Master ()
Sub Worksheet_Change(C5)
End Sub

Sub Worksheet_Change(ByVal Target as Range)
If Target_Address = "$C$5" then
(my code here)
EndIf

If this is correct, where should the Master Sub be present?

Or can I have all the code inside the Worksheet_Change sub itself?

Sorry about all the questions, but this is my first time with VBA!

thanks

[przemaas_o]

You don't need to use Master sub.
In your VBAProject click worksheet name that you are working, and put your code:


Private Sub Worksheet_Change(ByVal Target as Range)
If Target_Address = "$C$5" then
(code here)
EndIf

[prabhadixit]

That works! Thanks a ton for your help!

[prabhadixit]

Hi,

I had posted earlier today asking how to make a macro run automatically when the cell C5 in my worksheet is changed and I was told to use
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = $C$5 then
code here
EndIF
End SUB

inside my worksheet module. I did that and it worked perfectly, so I marked my thread as solved. But it has stopped working now! The only change I made were to add some code inside the If...Then block above. Can someone help? Thanks!

[RonMcK]

How about editing your msg (above) by adding an upload of a copy of your workbook with the macro in it. We need to see what all you've added in your code so we can see what's going on.

Click the Edit button beneah your original msg. scroll down to Additional Options, go to the Attach Files box and click the Manage Attachments button; then, follow the directions to find (browse for) and upload your file. You can upload it as either an .xls file or you can zip it up first and, then, upload the zip file.

Thanks,

[lucas]

prabhadixit,
Threads merged. If you had posted your followup question in the same thread instead of starting a new thread it would have cut down on the confusion and I would not have had to go to the trouble of merging your threads........

Start new threads only for new questions please. The reason should be obvious in this thread.

[prabhadixit]

Sorry! Im new here and didnt realise that I could continue in a thread that has been marked as Solved. Thanks for merging my threads.

and by the way, my macro very mysteriously started to work again! I have no clue how or why it didnt work for some time in between.