Too many calculations preventing Private Sub from executing macro
 

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

Too many calculations preventing Private Sub from executing macro
 

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

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

Too many calculations preventing Private Sub from executing macro
 

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


--
tx12345
------------------------------------------------------------------------
tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776
View this thread: http://www.excelforum.com/showthread...hreadid=522281