Run Macros If The Cell is Changed

[kislicins]

Hello
I have very limited knowledge of VB and I hope that someone can help me. I have a macros that I need to get running everytime the data in the cell I4 changes. Could anyone help me with this?

 

[VoG]

Right click the sheet tab, select View Code. Copy and paste in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Or Target.Address(False, False) <> "I4" Then Exit Sub
' Call your macros here
' e.g. Call Macro1
End Sub

Close the code window and try entering a value in I4.

 

[kislicins]

I think it works but my other Macros is now repeating itself without an end. Any ideas what should I do? Basicly, the other macros is hiding unused rows. All the process is activated through PATools (another macros written by some company) that is performing mail merge into the workbook. What I need to do is whenever the mail merge is inserting a new value in cell "I4" my macros would be activated and would hide all rows that I don't need. Any ideas? Thanks a lot!

 

[lenze]

You need to turn Events off before calling your macro and then turn them back on after exiting your macro!

Private Sub Worksheet_Change(ByVal Target As Range) 
If Target.Count > 1 Then Exit Sub
If Target.Address <> "I4" Then Exit Sub 
Application.EnableEvents=False
' Call your macros here 
' e.g. Call Macro1
Application.EnableEvents=True 
End Sub

lenze

 

[Boller]

The codes posted only cover the situation where I4 alone is selected and a new value is input.

To cover all I4 input situations (drag/drop, more than one cell changed at a time, etc.) :-

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, [I4]) Is Nothing Then
'Call your macros here
' e.g. Call Macro1
End If
Application.EnableEvents = True
End Sub

 

[kislicins]

Thanks very much everyone, but it's still not working the way it suppose to. Maybe there's an error somewhere? Here are both Macros that I have:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, [I4]) Is Nothing Then
Call Macro2
End If
Application.EnableEvents = True
End Sub

and

Sub Macro2()
Rows("1:150").Select
Selection.EntireRow.Hidden = False
'Ad Cash'
If Range("$A$13") = "" Then
Range("$A$13").EntireRow.Hidden = True
Range("$A$14").EntireRow.Hidden = True
Else
Range("$A$13").EntireRow.Hidden = False
Range("$A$14").EntireRow.Hidden = False
End If
'Pension Scheme'
If Range("$A$17") = "" Then
Range("$A$17").EntireRow.Hidden = True
Range("$A$18").EntireRow.Hidden = True
Else
Range("$A$17").EntireRow.Hidden = False
Range("$A$18").EntireRow.Hidden = False
End If
'Car'
If Range("$A$21") = "" Then
Range("$A$21").EntireRow.Hidden = True
Range("$A$22").EntireRow.Hidden = True
Else
Range("$A$21").EntireRow.Hidden = False
Range("$A$22").EntireRow.Hidden = False
End If
'General Benefits'
If Range("$B$47") = "" Then
Range("$B$47").EntireRow.Hidden = True
Else
Range("$B$47").EntireRow.Hidden = False
End If
If Range("$B$48") = "" Then
Range("$B$48").EntireRow.Hidden = True
Else
Range("$B$48").EntireRow.Hidden = False
End If
If Range("$B$49") = "" Then
Range("$B$49").EntireRow.Hidden = True
Else
Range("$B$49").EntireRow.Hidden = False
End If
If Range("$B$50") = "" Then
Range("$B$50").EntireRow.Hidden = True
Else
Range("$B$50").EntireRow.Hidden = False
End If
If Range("$B$59") = "" Then
Range("$B$59").EntireRow.Hidden = True
Else
Range("$B$59").EntireRow.Hidden = False
End If
If Range("$B$61") = "" Then
Range("$B$61").EntireRow.Hidden = True
Else
Range("$B$61").EntireRow.Hidden = False
End If
'Additional Payment'
If Range("$A$78") = "" Then
Range("$A$78").EntireRow.Hidden = True
Else
Range("$A$78").EntireRow.Hidden = False
End If
If Range("$A$78") = "" Then
Range("$A$79").EntireRow.Hidden = True
Else
Range("$A$79").EntireRow.Hidden = False
End If
If Range("$B$80") = "" Then
Range("$B$80").EntireRow.Hidden = True
Else
Range("$B$80").EntireRow.Hidden = False
End If
If Range("$B$81") = "" Then
Range("$B$81").EntireRow.Hidden = True
Else
Range("$B$81").EntireRow.Hidden = False
End If
If Range("$B$82") = "" Then
Range("$B$82").EntireRow.Hidden = True
Else
Range("$B$82").EntireRow.Hidden = False
End If
If Range("$B$83") = "" Then
Range("$B$83").EntireRow.Hidden = True
Else
Range("$B$83").EntireRow.Hidden = False
End If
If Range("$B$84") = "" Then
Range("$B$84").EntireRow.Hidden = True
Else
Range("$B$84").EntireRow.Hidden = False
End If
If Range("$B$85") = "" Then
Range("$B$85").EntireRow.Hidden = True
Else
Range("$B$85").EntireRow.Hidden = False
End If
If Range("$B$86") = "" Then
Range("$B$86").EntireRow.Hidden = True
Else
Range("$B$86").EntireRow.Hidden = False
End If
If Range("$B$87") = "" Then
Range("$B$87").EntireRow.Hidden = True
Else
Range("$B$87").EntireRow.Hidden = False
End If
If Range("$B$88") = "" Then
Range("$B$88").EntireRow.Hidden = True
Else
Range("$B$88").EntireRow.Hidden = False
End If
If Range("$A$90") = "" Then
Range("$A$89").EntireRow.Hidden = True
Else
Range("$A$89").EntireRow.Hidden = False
End If
'Holiday'
If Range("$A$90") = "" Then
Range("$A$90").EntireRow.Hidden = True
Range("$B$91").EntireRow.Hidden = True
Else
Range("$A$90").EntireRow.Hidden = False
Range("$B$91").EntireRow.Hidden = False
End If
If Range("$B$92") = "" Then
Range("$B$92").EntireRow.Hidden = True
Else
Range("$B$92").EntireRow.Hidden = False
End If
If Range("$B$92") = "" Then
Range("$B$93").EntireRow.Hidden = True
Else
Range("$B$93").EntireRow.Hidden = False
End If
If Range("$B$92") = "" Then
Range("$B$94").EntireRow.Hidden = True
Else
Range("$B$94").EntireRow.Hidden = False
End If
If Range("$A$96") = "" Then
Range("$B$95").EntireRow.Hidden = True
Else
Range("$B$95").EntireRow.Hidden = False
End If
'Pension Scheme'
If Range("$A$96") = "" Then
Range("$A$96").EntireRow.Hidden = True
Range("$B$97").EntireRow.Hidden = True
Else
Range("$A$96").EntireRow.Hidden = False
Range("$B$97").EntireRow.Hidden = False
End If
If Range("$B$98") = "" Then
Range("$B$98").EntireRow.Hidden = True
Else
Range("$B$98").EntireRow.Hidden = False
End If
If Range("$A$100") = "" Then
Range("$B$99").EntireRow.Hidden = True
Else
Range("$B$99").EntireRow.Hidden = False
End If

'Tax Efficient Share Plan'
If Range("$A$100") = "" Then
Range("$A$100").EntireRow.Hidden = True
Range("$B$101").EntireRow.Hidden = True
Else
Range("$A$100").EntireRow.Hidden = False
Range("$B$101").EntireRow.Hidden = False
End If
If Range("$B$102") = "" Then
Range("$B$102").EntireRow.Hidden = True
Else
Range("$B$102").EntireRow.Hidden = False
End If
If Range("$B$103") = "" Then
Range("$B$103").EntireRow.Hidden = True
Else
Range("$B$103").EntireRow.Hidden = False
End If
If Range("$A$105") = "" Then
Range("$B$104").EntireRow.Hidden = True
Else
Range("$B$104").EntireRow.Hidden = False
End If
'Car'
If Range("$A$118") = "" Then
Range("$A$118").EntireRow.Hidden = True
Range("$B$119").EntireRow.Hidden = True
Else
Range("$A$118").EntireRow.Hidden = False
Range("$B$119").EntireRow.Hidden = False
End If
If Range("$A$120") = "" Then
Range("$B$120").EntireRow.Hidden = True
Else
Range("$B$120").EntireRow.Hidden = False
End If
If Range("$A$121") = "" Then
Range("$B$121").EntireRow.Hidden = True
Else
Range("$B$121").EntireRow.Hidden = False
End If
If Range("$A$122") = "" Then
Range("$B$122").EntireRow.Hidden = True
Else
Range("$B$122").EntireRow.Hidden = False
End If
If Range("$A$123") = "" Then
Range("$B$123").EntireRow.Hidden = True
Else
Range("$B$123").EntireRow.Hidden = False
End If

Application.ScreenUpdating = True

End Sub

As I already mentioned I am rubbish with VB and I have started using it only yesterday. Any help will be very appreciated! It looks like Macro2 is not kicking in but works all right if activated manually.

P.S. just in case - I have no idea if my macros are written in the right place. First one is under VBA Project (Your Total Reward) > Microsoft Excel Objects> HideRows (Total Reward Statement) and the second one is under Modules > Module 1

 

[Boller]

Perhaps EnableEvents has been set to False.

Run the following and try again :-

Sub Enable_Events()
Application.EnableEvents = True 
End Sub