Help - !VERY URGENT! - Macros that should activate another

kislicins

New Member
Joined
Jul 23, 2007
Messages
22
Hi. I know I have posted this already in another topic but it is very urgent as I need to hand my work in by the end of today.

I need to write a macros that will activate another macros whenever the cell "I4" is modified. So far it is not working the way it suppose to. Maybe anyone can spot a mistake? Here are both Macros that I have:

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

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

Honestly to say I am rubbish with VB and I have started using it only yesterday but it is essencial for me to get this one right. Any help will be very appreciated! It looks like Macro2 is not kicking in but works all right if activated manually. But whenever I change "I4" nothing happens.

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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
The first one needs to be in the worksheet module for the sheet you want to be changing:
e.g. VBAProject>Microsoft Excel Objects>Sheet1
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
I've tested your first piece of code and it runs fine. Try changing Call Macro2 to Call TestMacro and put this code in the module where you have Macro2:
Code:
Sub TestMacro()
MsgBox "Made it this far!!!"
End Sub

Now try modifying I4 on the worksheet again and see if you get the message box pop up. If you do, then it is more likely a problem with Macro2 itself.
 

kislicins

New Member
Joined
Jul 23, 2007
Messages
22
I've tested your first piece of code and it runs fine. Try changing Call Macro2 to Call TestMacro and put this code in the module where you have Macro2:
Code:

Sub TestMacro()
MsgBox "Made it this far!!!"
End Sub



Now try modifying I4 on the worksheet again and see if you get the message box pop up. If you do, then it is more likely a problem with Macro2 itself.

Interesting. I tried what you suggested and it didn't work. Then I tried opening a new Book, entered the same code and MsgBox and everything worked perfectly. What can be the problem then?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Perhaps while developing this code you've turned events off?

Goto the VBE, type this in the immediate window (CTRL+G) and hit enter.

Code:
Application.EnableEvents = True
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Hmm, ok, try adding a couple of Message Boxes in the event code like so:

Code:
Private Sub Worksheet_Change(ByVal Target As Range) 
Application.EnableEvents = False 
If Not Intersect(Target, [I4]) Is Nothing Then 
MsgBox "just before calling macro"
Call Macro2
MsgBox "just after calling macro"
End If 
Application.EnableEvents = True 
End Sub

Then add a message box at the first line of Macro2, something like:
Code:
MsgBox “This is macro2”

Then modify I4 again and see which boxes appear, this should give you an indication of what is actually running.
 

edvwvw

Active Member
Joined
Jan 30, 2007
Messages
278
I have been looking at this for a while and getting varying results, this works if you put both sets of code in Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$4" Then
Call Macro2
End If
End Sub

edvwvw
 

kislicins

New Member
Joined
Jul 23, 2007
Messages
22
This is really weard but it started working just fine... Anyway, I've got another problem now and maybe you could help? The whole idea of my project was to make mail merge for 1500+ letters with different context. Everything is working fine now (thanks to everyone who helped) and if I manually change the cell the macro starts perfectly. My mail merge is done through PATools (another VB software that is locked for editing) which is basicly changing the number in I4. My current problem is that the Macro2 (the long one) is not getting activated for some reason after the first letter is merged although the number does change.
 

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