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

still learning

Well-known Member
Joined
Jan 15, 2010
Messages
618
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,390
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,301
Office Version
  1. 365
Platform
  1. Windows
Bit late to the party, but another option is
VBA Code:
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,390
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,320
Messages
5,600,946
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top