action on selecting special cells
 

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!!!

--
Message posted from http://www.ExcelForum.com

action on selecting special cells
 

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 " wrote in
message ...
Hi,

I have this sheet which turns the calculations OFF when opened..... and
i was trying to write a code which will turn on the calculations if
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 in
column A, it turns on the calculation... but i cannot figure out a way
to change back the calculation to manual when any other cell is
selected....


any help is greatly appreciated!!!!


---
Message posted from http://www.ExcelForum.com/

action on selecting special cells
 

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....

--
Message posted from http://www.ExcelForum.com

action on selecting special cells
 

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

Doug Glancy
"paritoshmehta " wrote in
message ...
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 the
calculation to automatic if the user goes to another excel
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 the
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 use
alt+tab, it doesnt work..... does anyone have a better solution.....


---
Message posted from http://www.ExcelForum.com/