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]

So all is good now?

 

[Sakshi0206]

No the file is still not recalculating and i am still getting the compile error. it was not the extra End if i guess. There is still some problem in this macro

 

[FormR]

The same compile error?

Are you sure that these macros exist? And are you sure they are all in a standard code module (Insert > Module from the VBE) and no where else?

    Call macro1
    Call CurrencyPL
    Call CurrencyCFS
    Call CurrencyBS
    Call CurrencyLoans
    Call CurrencyLoans1

You may need to upload the file to a file sharing site (like dropbox) and share the link here.

 

[Sakshi0206]

yes i have created them in a module. They are working fine if i run them by going to macro menu. Is it possible that i have limited to specific sheets that they are not working in the worksheet macro? and what about the macro1. Why the file is not recalculating?

 

[FormR]

yes i have created them in a module. They are working fine if i run them by going to macro menu. Is it possible that i have limited to specific sheets that they are not working in the worksheet macro?

I think we need to see the workbook.

and what about the macro1. Why the file is not recalculating?

You haven't posted the code for macro1 - but if the change event isn't even compiling then macro1 isn't getting called.

 

[FormR]

UPDATE:

You need to remove the Private part from your sub names.

This:

Sub CurrencyPL()

Instead of:

Private Sub CurrencyPL()

 

[Sakshi0206]

Macro1 is simple calculate. And its still not calculating and is still showing the compile error even after i removed Private from the macros.

 

[FormR]

Macro1 is simple calculate. And its still not calculating and is still showing the compile error even after i removed Private from the macros.

It is impossible to comment on Macro1 because you have not posted the code!

On the compile error - remove any sensitive data and put the workbook on a file sharing site (like dropbox) and share the link here.

 

[Sakshi0206]

The Macro1 is:

Sub Macro1()
'
' Macro1 Macro
'


'
Calculate
End Sub


I tried it again i am getting few errors in the Call CurrencyPL i think i will try solving it first. If you find any fault in the macro do let me know.

Thanks

 

[FormR]

So the compile error is sorted now?

I suggest you get your other macros sorted and re-visit macro1 later.