action on selecting special cells

[paritoshmehta]

Hi,

I have this sheet which turns the calculations OFF when opened..... an
i was trying to write a code which will turn on the calculations i
something in the "a" column is entered.....

I was trying to use this code :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub

this works fine... i.e. as soon as something is entered in any cell i
column A, it turns on the calculation... but i cannot figure out a wa
to change back the calculation to manual when any other cell i
selected....


any help is greatly appreciated!!!

 

[Doug Glancy]

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Application
If Intersect(Target, Me.Range("a:a")) Is Nothing Then
.Calculation = xlCalculationManual
Else
.Calculation = xlAutomatic
.MaxChange = 0.001
End If
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub

hth,

Doug Glancy

 

[paritoshmehta]

this worked absolutely fine for me.....

thanks a million for your help!!!

just one more question.....

on the same sheet, i thought of writing a code that will change th
calculation to automatic if the user goes to another exce
workbook..... so i included the following code (in This workbook):

Private Sub Workbook_Deactivate()
'converting to Automatic calculation
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub

.....and when a user comes back to the worksheet again, it turns th
calculation to OFF using the following code:

Private Sub Workbook_Activate()
'converting to manual calculation
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub

this works fine if i use the mouse to select another file, but if i us
alt+tab, it doesnt work..... does anyone have a better solution....

 

[Doug Glancy]

That seems to work for me using XL2k, so afraid I don't have any
suggestions.

Doug Glancy