Run Macros If The Cell is Changed

kislicins

New Member
Joined
Jul 23, 2007
Messages
22
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?
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Right click the sheet tab, select View Code. Copy and paste in:

Code:
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

New Member
Joined
Jul 23, 2007
Messages
22
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

Legend
Joined
Feb 18, 2002
Messages
13,690
You need to turn Events off before calling your macro and then turn them back on after exiting your macro!
Code:
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

Banned
Joined
Apr 11, 2006
Messages
2,328
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.) :-

Code:
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

New Member
Joined
Jul 23, 2007
Messages
22
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

Banned
Joined
Apr 11, 2006
Messages
2,328
Perhaps EnableEvents has been set to False.

Run the following and try again :-

Code:
Sub Enable_Events()
Application.EnableEvents = True 
End Sub
 

Forum statistics

Threads
1,181,648
Messages
5,931,213
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top