Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 15
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 15
Default 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


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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

  #5   Report Post  
Banned
 
Posts: 3
Default

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


  #6   Report Post  
Junior Member
 
Posts: 1
Default

Quote:
Originally Posted by David Godinger[_2_] View Post
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 ^^
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
stop automatic recalulation when error occurs DagL Excel Programming 1 October 11th 07 11:52 AM
indicate activecell range thread Excel Programming 0 June 4th 07 05:55 PM
ActiveCell in a Range? Filo Excel Discussion (Misc queries) 3 May 22nd 07 09:52 PM
Strange problems with setting hidden property of a range when sheet protected with UserInterfaceOnly [email protected] Excel Programming 2 June 6th 06 04:15 PM
Name of range containing ActiveCell? Ed Excel Programming 13 January 4th 05 01:55 AM


All times are GMT +1. The time now is 10:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"