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?

 

[Sakshi0206]

One more doubt, sorry if i am irritating you.

I am trying to select few cells to apply a format like:

Range("C12:DQ20,C24:DQ27,C31:DQ41,C44:DQ44,C48:DQ61,C64:DQ64,C67:DQ73,.............,C119:DQ123").Select

But the actual range is bigger if i try to put it in one line it shows some error so i tried doing:

Range( _
"C12:DQ20,C24:DQ27,C31:DQ41,........................................................................................................................................................,C94:DQ94, _
C97:DQ97,........................................................................................................................................................................., C100:DQ103, _
C106:DQ110,C113:DQ115,C119:DQ123" _
).Select

But it is also not working. Can you help.

 

[Sakshi0206]

no it is still there. Somehow the worksheet macro is not accepting 2 criteria. I will try doing it in a different way. As soon as i will be able to sort it i will let you know.

 

[FormR]

One more doubt

no it is still there.

How about we concentrate on one thing at a time. The compile error seems to be the most pressing issue, can you share an example workbook that is displaying this issue?

 

[Sakshi0206]

Sorry i cant share the workbook. Actually i asked the range error as in CurrencyPL the sample i copied here has a limited range and in actual the range is bigger. So i am feeling the error is because i have wrongly entered the range making the macro non working. If its fine then there must be some other error in the macro. i am trying to correct this macro first then only the Call will work.

 

[FormR]

They are working fine if i run them by going to macro menu.

i am trying to correct this macro first then only the Call will work.

I am getting a compile error saying sub or function not defined for Call CurrencyPL macro mentioned above.

I hope you can understand why this is getting confusing. But it is unlikey that whatever errors you have in the other macros are causing the compile error that you have stated is appearing in change event macro.

For the large range issue try something like:

Dim r As Range
Set r = Range("C12:DQ20,C24:DQ27,C31:DQ41,C44:DQ44")
Set r = Union(r, Range("C48:DQ61,C64:DQ64,C67:DQ73,C119:DQ123"))
Set r = Union(r, Range("C90:DQ99,C100:DQ199,C200:DQ299,C300:DQ399"))
'etc
'etc
r.Select

 

[Sakshi0206]

Thanks for all your help. Sorry for all the confusion. Actually i was trying different macros and i guess i was not able to explain it. I created the Macro CurrencyPL with few selected cells to see if its working or not. The macro i pasted in the thread. It was working fine when i run it using macro menu. So i expanded the range to cover all the cells i need and then created the worksheet macro. But there it was showing a compile error on CurrencyPL macro. So i figured that this is due to the range i have added. I will try the method you have stated. Thanks for your help. I will let you know if it works after changing the Range.

 

[Sakshi0206]

It is working now thanks a lot now the worksheet macro is recalculating the file and then running the CurrencyPL macro too. Thanks a lot.