Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109105 > unrolled thread

ActiveCell VBA with Recalulation of Range Under UserInterfaceOnly

Back to article view | Back to microsoft.public.excel.programming

Contents

  ActiveCell VBA with Recalulation of Range Under UserInterfaceOnly David Godinger <daveg7@gmail.com> - 2016-07-25 10:45 -0700
    Re: ActiveCell VBA with Recalulation of Range Under UserInterfaceOnly GS <gs@v.invalid> - 2016-07-25 14:21 -0400
      Re: ActiveCell VBA with Recalulation of Range Under UserInterfaceOnly David Godinger <daveg7@gmail.com> - 2016-07-25 19:07 -0700
        Re: ActiveCell VBA with Recalulation of Range Under UserInterfaceOnly GS <gs@v.invalid> - 2016-07-25 22:44 -0400
    Re: ActiveCell VBA with Recalulation of Range Under UserInterfaceOnly ltomama0945 <ltomama0945@gmail.com> - 2016-07-27 02:50 +0100
    Re: ActiveCell VBA with Recalulation of Range Under UserInterfaceOnly edelkochen <edelkochen@gmail.com> - 2016-07-27 04:07 +0100

#109105 — ActiveCell VBA with Recalulation of Range Under UserInterfaceOnly

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").Calculate
	    	Range("rolesSuggested").Calculate              
    End If

End Sub

[toc] | [next] | [standalone]

#109106

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

[toc] | [prev] | [next] | [standalone]

#109107

Thanks!

I want to make sure: 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

[toc] | [prev] | [next] | [standalone]

#109108

> 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

[toc] | [prev] | [next] | [standalone]

#109109

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




-- 
ltomama0945

[toc] | [prev] | [next] | [standalone]

#109110

'David Godinger[_2_ Wrote: 
> ;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").Calculate
> Range("rolesSuggested").Calculate End If End Sub uppppppppppppppppppppppppppppp lên nao ^^




-- 
edelkochen

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web