Too many calculations preventing Private Sub from executing macro

[tx12345]

Hi

Here is a nice little bit of code, that, when you change the value of a cell using a validation scroll down list, it runs a macro:

Please Login or Register  to view this content.

It works great on one very simple file I have with maybe less than 200 calculations on the page, where once I select from the scroll down list and change the value in the cell it triggers the macro without incident. But the *other* file has too many calcs I guess.

I discovered that I can get the macro above to work on the file with the heavier concentration of calculations IF I turn off the automatic calculations, but then they have to be *manually* turned off, which defeats the purpose of the convenience of the scroll down list.

Does anybody know how to get past this little bug?

Many thanks as always

tx

[Gary L Brown]

This will turn off automatic calculation while yourmacro runs...

'/============================================/
Private Sub Worksheet_Change(ByVal Target As Range)
'declare variable for remembering current calculation status of workbook
Dim xlCalc As XlCalculation

'save current calcuation status of workbook to variable
xlCalc = Application.Calculation

'change the current calculation status of workbook to MANUAL
Application.Calculation = xlCalculationManual

'your macro
If Target.Address = "$C$6" Then
Application.Run "yourmacro"
End If

'change calculation status of workbook back to original status
Application.Calculation = xlCalc

End Sub
'/============================================/
HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"tx12345" wrote:

>
> Hi
>
> Here is a nice little bit of code, that, when you change the value of a
> cell using a validation scroll down list, it runs a macro:
>
>
> Code:
> --------------------
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Address = "$C$6" Then
> Application.Run "yourmacro"
> End If
> End Sub
>
> --------------------
>
>
> It works great on one very simple file I have with maybe less than 200
> calculations on the page, where once I select from the scroll down list
> and change the value in the cell it triggers the macro without incident.
> But the *other* file has too many calcs I guess.
>
> I discovered that I can get the macro above to work on the file with
> the heavier concentration of calculations IF I turn off the automatic
> calculations, but then they have to be *manually* turned off, which
> defeats the purpose of the convenience of the scroll down list.
>
> Does anybody know how to get past this little bug?
>
> Many thanks as always
>
> tx
>
>
> --
> tx12345
> ------------------------------------------------------------------------
> tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776
> View this thread: http://www.excelforum.com/showthread...hreadid=522281
>
>

[tx12345]

Hi Gary

Thanks for the reply.

I gave yopur code a try, but it seems as if when there are X number of calculations (or more) in an excel worksheet, you simply cannot run a macro when you select from a scroll down list. The only way to get it to work (the Private sub variety like being discussed) is to force the macro to work by clicking into the cell, changing the value and then hitting enter. Not very elegant but it works.

Unless of course there is another solution.


Thanks again

tx