Restrict Calculate Event by Cell

Re: Exit Cell Event

Have you thought of using the

[vba]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
[/vba]

event and just specify the union range B33, D33, F33, I33 as the target ?

Re: Exit Cell Event

Actually on second thoughts that probably won't work... Target would be the cell selected as opposed to cell exited

Re: Exit Cell Event

use the Private Sub Worksheet_SelectionChange(ByVal Target As Range) of the worksheet, and define target as your ranges

or, use this, its worked for me in the past, you can use beforeclick instead of beforedoubleclick probably:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


If Not Intersect(Target, Range("I5:J1000")) Is Nothing Then


 ' if user selects in the target range, do stuff here


 Else


 ' if target was not select, exit sub


 Exit Sub


End If


Exit Sub


End Sub

Re: Exit Cell Event

Yes but as per my previous post, the OP wants this to fire

Quote

having the check done only when the specific cells are exited.

The selection change event fires on cell selection

Re: Exit Cell Event

Oh, I missed that you wanted it when you exited the cell as well.

try this: in the selection change sub, have a public variable that records whatever cell was selected (varCellname = activecell.name or it might be .address) then test to see what the variable is set to either on before click or you could probably check it at the beginning of the selection change sub, do your calcs, and then reset the variable to the one selected... itll keep the last selected cell basically

Re: Exit Cell Event

haha will almost beat ya to it

Re: Exit Cell Event

But you may be able to adapt this

This Procedure will fire automatically when a user exits cell A1 (can be any cell). Note the Dimensioning (Dim) of the Variable "rTriggerCell" is at the Procedure Level.

Courtesy of Dave posted here
[vba]

Dim rTriggerCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Triggers an action upon user exiting cell A1

On Error Resume Next
Application.EnableEvents = False
'Entered into Trigger cell
If Target.Address = "$A$1" Then
Set rTriggerCell = Target
Application.EnableEvents = True
On Error GoTo 0
Exit Sub
End If

If Not rTriggerCell Is Nothing Then 'They are leaving A1
MsgBox "You just left cell A1", vbInformation, "OzGrid.com"
Set rTriggerCell = Nothing
End If
Application.EnableEvents = True
On Error GoTo 0
End Sub[/vba]

Re: Exit Cell Event

Quote from AndrewJ

haha will almost beat ya to it

LOL : D

Re: Exit Cell Event

OK.. will check back in the mornin' 1.30 am here in the UK..

Re: Exit Cell Event

bout to leave work, but here's how I was thinking it might look:

edit: yep just tested it and itll do what you want

Option Explicit
Public varCelladd As String
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     
    If varCelladd = "$A$1" Then
        
         ' do your option button stuff here 
         MsgBox "yay"
         
    Else
         
         ' if anything other than a1 was selected do something here
         
    End If
     
    varCelladd = ActiveCell.Address
    
    Cells(2, 10).Value = varCelladd
     
End Sub

by the way, you can take out the parts where it puts the value of varCelladd into cell(2,10) and the msgbox, thats just to show you how it works

Re: Exit Cell Event

you're very welcome

by the way, try -

If varCelladd.value = 0 Then 


blahblah


end if

for any cell with 0 in it

Re: Exit Cell Event

This is a good candidate for a custom class that throws a custom event.

This simple class throws one event. ExitCell. It ignores multiple selections and will use the activecell if more than one cell is currently selected. It only deals with the activesheet. To use the downloadable example, simply select Sheet1 and then select some cells...

Also, since the events in custom object are thrown first, you could easily
cancel the selection change by returning Cancel = True in the ExitCell event.

ExitCellClassWithCustomEvent.zip

In a class module named "ExitCellEvent"

Option Explicit


Private WithEvents ws As Worksheet
Private pPreviousTarget As Range
Event ExitRange(Target As Range)


Private Sub Class_Initialize()
    Set pPreviousTarget = ActiveCell
    Set ws = ActiveSheet
End Sub


Private Sub ws_SelectionChange(ByVal Target As Range)
    RaiseEvent ExitRange(pPreviousTarget)
    Set pPreviousTarget = ActiveCell
End Sub

In any worksheet class:

Option Explicit


Private WithEvents ECE As ExitCellEvent


Private Sub Worksheet_Activate()
    Set ECE = New ExitCellEvent
End Sub


Private Sub ECE_ExitRange(Target As Range)
    MsgBox "You exited range " & Target.Address(0, 0)
End Sub


Private Sub Worksheet_Deactivate()
    Set ECE = Nothing
End Sub

Re: Exit Cell Event

Tom... Great simple example of the value that classes can bring to the party. Love it

Re: Exit Cell Event

Haven't followed this entire thread. Would something like this work?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CellName$
CellName = ActiveCell.Address
' be sure to use the dollar-signs when referencing the cell names below
If CellName = "$B$1" or CellName = "$A$1" Then 
MsgBox "test" ' your code here
end if
End Sub

Quote from max_lux

I am running the following code to check if a value = 0 is in specific cells. Because this code on all the cells runs each time any cell in the workbook is calculated, the screen flashes. I would like to stop this screen flashing by having the check done only when the specific cells are exited.

Thanks, in advance!

Code

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    If ActiveSheet.Name = "INPUT" Then
        Application.ScreenUpdating = False
        If Sheets("INPUT").Range("B33").Value = 0 Then
            Sheets("INPUT").obIoh.Value = False
        End If
        If Sheets("INPUT").Range("D33").Value = 0 Then
            Sheets("INPUT").obIIoh.Value = False
        End If
        If Sheets("INPUT").Range("F33").Value = 0 Then
            Sheets("INPUT").obIIIoh.Value = False
        End If
        If Sheets("INPUT").Range("I33").Value = 0 Then
            Sheets("INPUT").obIVoh.Value = False
        End If
        Application.ScreenUpdating = True
    Else
        Exit Sub
    End If
End Sub

Display More

Re: Exit Cell Event

Quote

OK.. will check back in the mornin' 1.30 am here in the UK

Wussy

Re: Exit Cell Event

Quote from MrkFrrl

Haven't followed this entire thread. Would something like this work?

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CellName$
CellName = ActiveCell.Address
' be sure to use the dollar-signs when referencing the cell names below
If CellName = "$B$1" or CellName = "$A$1" Then 
MsgBox "test" ' your code here
end if
End Sub

No... try it and see That will fire on cell selection, not cell exit.

Quote from Dave Hawley

Wussy

LOL.... hey the signature's changed... I was right