run an event change macro only once each time the spreadsheet is opened

[still learning]

Hi
I'm wondering if I can have an event change macro only run ones until the spread sheet is closed, then when it's opened again the event change macro will again run, once
as an example, here is an event change macro

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Not Intersect(Target, Range("A5:A30")) Is Nothing Then
 Target.Offset(0, 2).Select
End If
End sub

I open the spreadsheet and enter data in A5,
the cursor moves automatically 2 cells to the right.
If I then enter data in A6, I don't want the event change to work.
The next time I open the spreadsheet, I enter data in A6, the same thing happens, the macro will only run once

mike

 

[dmt32]

Hi,
If you want to allow each time the workbook is opened, any one cell in the specified range that is changed to run your code just once until you close & re-open the workbook, then try following & see if does what you want

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, cell As Range
    Set rng = Range("A5:A30")
    If Not Intersect(Target, rng) Is Nothing Then
    For Each cell In rng.Cells
'cell in range already updated today
        If Val(cell.ID) = xlOff Then Exit Sub
    Next
'your change code
     With Target
        .Offset(0, 2).Select
'turn further changes off until workbook next opened
        .ID = xlOff
     End With
    End If
End Sub

Dave

 

[still learning]

Thanks Dave,
That's what I was looking for


mike

 

[Fluff]

Bit late to the party, but another option is

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim i As Long
   Static RunOnce As Boolean
   
   If RunOnce Then Exit Sub
   If Not Intersect(Target, Range("A5:A30")) Is Nothing Then
      Target.Offset(0, 2).Select
      RunOnce = True
   End If
End Sub

 

[dmt32]

Thanks Dave,
That's what I was looking for


mike

welcome

After I posted realised probably no need to check the entire range so can avoid the loop

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Range("A1")
    If Not Intersect(Target, Range("A5:A30")) Is Nothing Then
     If Val(rng.ID) <> xlOff Then
'your change code
        Target.Offset(0, 2).Select
'turn further changes off until workbook next opened
        rng.ID = xlOff
    End If
    End If
End Sub

Dave