ExcelBanter - ActiveCell VBA with Recalulation of Range Under UserInterfaceOnly

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - ActiveCell VBA with Recalulation of Range Under UserInterfaceOnly (https://www.excelbanter.com/excel-programming/451609-activecell-vba-recalulation-range-under-userinterfaceonly.html)

David Godinger[_2_]

ActiveCell VBA with Recalulation of Range Under UserInterfaceOnly

Im using UserInterfaceOnly, so that I can run macros and still keep my sheet protected.

I tried to use ActiveCell to calculate only certain ranges, without stopping protection for the rest of the worksheet. I need to recalculate only those ranges, but leave the rest of the sheet, unchanged, and on manual calculation.

I tested ActiveCell and made sure that it works for, as example, putting up a message box, but I cant get it do what I want.

Heres the VBA:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.

Set KeyCells = Range("AJ6:DT105")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
Range("last4SessionsCalculations").Calculate
Range("rolesSuggested").Calculate
End If

End Sub

GS[_6_]

ActiveCell VBA with Recalulation of Range Under UserInterfaceOnly

This is working for me...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect([AJ6:DT105], Target) Is Nothing Then
Range("last4SessionsCalculations").Calculate
Range("rolesSuggested").Calculate
End If
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

David Godinger[_2_]

ActiveCell VBA with Recalulation of Range Under UserInterfaceOnly

Thanks!

I want to make su Does it work even if the sheet always stays on Manual Calc and all cells are protected?

On Monday, July 25, 2016 at 11:21:57 AM UTC-7, GS wrote:
This is working for me...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect([AJ6:DT105], Target) Is Nothing Then
Range("last4SessionsCalculations").Calculate
Range("rolesSuggested").Calculate
End If
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

GS[_6_]

ActiveCell VBA with Recalulation of Range Under UserInterfaceOnly

Does it work even if the sheet always stays on Manual Calc and all
cells are protected?

My test sheet is protected, and calc is manual...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

ltomama0945

I imagine that, like me, you say that you never have enough time and that you just cannot cope with 60 dozen things all at once. How on earth do you get out of that spiral? Many people never sit down and look at how to work smarter, rather than harder and even longer hours

edelkochen

Quote:

Originally Posted by David Godinger[_2_] (Post 1624089)

I’m using UserInterfaceOnly, so that I can run macros and still keep my sheet protected. I tried to use ActiveCell to calculate only certain ranges, without stopping protection for the rest of the worksheet. I need to recalculate only those ranges, but leave the rest of the sheet, unchanged, and on manual calculation. I tested ActiveCell and made sure that it works for, as example, putting up a message box, but I can’t get it do what I want. Here’s the VBA: Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range ' The variable KeyCells contains the cells that will ' cause an alert when they are changed. Set KeyCells = Range("AJ6:DT105") If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then Range("last4SessionsCalculations").Calcu late Range("rolesSuggested").Calculate End If End Sub

uppppppppppppppppppppppppppppp lên nao ^^

All times are GMT +1. The time now is 02:16 PM.