Help with a macro to recalculate the file and then run few macros.

[Sakshi0206]

Hi,

I have a file with few sheets. I have created different macros on all the sheets accept Sheet 1.

Now on sheet 1 i want to create a drop down. I want that every time the value in the drop down is changed the file is recalculated. After the recalculation is completed i want it to run few macros i created on other sheets.

The Macro i am trying on Sheet1 (not modules) is:

Private Sub Worksheet_CurrencyChange(ByVal Target As Range)


If Target.Address = "$C$11" Then
Calculate
Call CurrencyPL
Call CurrencyCFS
Call CurrencyBS
Call CurrencyLoans
Call CurrencyLoans1
End If


End Sub

It is not doing anything. I have tried to create a separate macro to calculate the file and call it here but it is also not working.

Can anyone help me with this?

 

[FormR]

Hi - you can not change the name of event macros.. it should be:

[COLOR=#b22222]Private Sub Worksheet_Change(ByVal Target As Range)
[/COLOR]If Target.Address = "$C$11" Then
    Calculate
    Call CurrencyPL
    Call CurrencyCFS
    Call CurrencyBS
    Call CurrencyLoans
    Call CurrencyLoans1
End If
End Sub

 

[Sakshi0206]

But i already have a macro with that name. I have used it to compare 2 rows. On the same sheet i need another one so can i keep the same name?

 

[Sakshi0206]

I tried creating it in the workbook except worksheet. I am getting a compile error saying sub or function not defined for Call CurrencyPL macro mentioned above. I created the following macro:

Private Sub CurrencyPL()
'
' CurrencyPL Macro
'


'
Application.ScreenUpdating = False
Sheets("PL").Select
Range("C12:DQ45").Select
Range("C12").Activate
If Range("B2").Value = Range("C4").Value Then
Selection.NumberFormat = "[$$-1009]#,##0 ;[Red]([$$-1009]#,##0)"
ElseIf Range("B2").Value = Range("F4").Value Then
Selection.NumberFormat = "[$$-409]#,##0 ;[Red]([$$-409]#,##0)"
ElseIf Range("B2").Value = Range("G4").Value Then
Selection.NumberFormat = "[$£-809]#,##0 ;[Red]([$£-809]#,##0)"
End If
Range("c10").Select
End Sub

 

[FormR]

But i already have a macro with that name. I have used it to compare 2 rows. On the same sheet i need another one so can i keep the same name?

Then you need to combine the two into one sub.

 

[Sakshi0206]

I tried it. It is giving me a compile error.

 

[FormR]

Can you post the other change event code you have?

 

[Sakshi0206]

The Macro is:

Private Sub Worksheet_Change(ByVal Target As Range)


If InStr(Target.Address, "$51") Then
Dim rngAW As Range, rngAN As Range
Set rngAW = Application.Intersect(Target.EntireColumn, Range("C51").EntireRow)
Set rngAN = Application.Intersect(Target.EntireColumn, Range("C54").EntireRow)
If rngAW.Value > rngAN.Value Then
MsgBox "Not enough cash. Maximum loans which can be accepted is " & Application.Intersect(Target.EntireColumn, Range("C54").EntireRow).Value
Target.Value = ""
End If
ElseIf Target.Address = "$C$11" Then
Call Macro1
Call CurrencyPL
Call CurrencyCFS
Call CurrencyBS
Call CurrencyLoans
Call CurrencyLoans1
End If


End If


End Sub

If i try to run it it is saying compile error. Sub or Function not defined for this segment:

Call CurrencyPL
Call CurrencyCFS
Call CurrencyBS
Call CurrencyLoans
Call CurrencyLoans1

It is not recalculating the file too. (Macro1 is to recalculate the file)

 

[FormR]

Where are those macros? They should be in a standard code module, like "module1".

You also have an extra "End If" at the end that is not needed.

 

[Sakshi0206]

Thanks for pointing that out. I have created all macros in Modules. The macros are similar to:

Private Sub CurrencyPL()
'
' CurrencyPL Macro
'


'
Application.ScreenUpdating = False
Sheets("PL").Select
Range("C12:DQ45").Select
Range("C12").Activate
If Range("B2").Value = Range("C4").Value Then
Selection.NumberFormat = "[$$-1009]#,##0 ;[Red]([$$-1009]#,##0)"
ElseIf Range("B2").Value = Range("F4").Value Then
Selection.NumberFormat = "[$$-409]#,##0 ;[Red]([$$-409]#,##0)"
ElseIf Range("B2").Value = Range("G4").Value Then
Selection.NumberFormat = "[$£-809]#,##0 ;[Red]([$£-809]#,##0)"
End If
Range("c10").Select
End Sub