event macro to only run once

[still learning]

Hi

I use the following event macro to trigger another macro

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("I:I")) Is Nothing Then
    If Target.Value <> "" Then
eventcopy
End If
End If
End Sub

Is there a way to only have it work only once each time the worksheet is open.
OR should I make a button on the ribbon that disables it after it runs and enables it before I save and close the worksheet

mike

 

[Kamolga]

You can put it on sheet activation

Private Sub Worksheet_Activate()
Dim dcell As Range
For Each dcell In Range("I:I")
    If dcell.Value <> "" Then
        eventcopy
        Exit Sub
    End If
Next dcell
End Sub

I assume you want eventcopy to be run once if a cell in I:I is not empty. If you want it to be run for each cell that is not empty, delete 'exit sub' from the code

 

[still learning]

Hi Kamolga
putting the macro in a sheet activation runs the macro "eventcopy" once every time i activate the sheet. I don't want it to run when the sheet is opened
I want to have it run once when I:I changes. If It changes again while the sheet is still open, I don't want the macro to run. I was thinking of having an icon on the ribbon that will toggle the event macro to disable and able. I would rather have it in the event macro instead, if its possible,

I have a sheet activate macro that puts the cursor on the next empty cell in A when the sheet opens.
I then enter data across the line to I
I then drop down and enter data on the next line also to I >> some of the data is the same as what I entered already on the line above. I made a macro that will copy what is the same and then move to the next empty cell on the new line to enter more data. The last cell to enter data in on the new line is I. I don't need the event macro to run again.
I then save and close the sheet. The next I open it to enter data, I need the event macro to run once if i enter some thing in I

mike

 

[mumps]

You could place code in your sheet activate macro which finds the row number of the next empty cell in column A and place that row number in a helper cell that you know will not be used somewhere on your sheet, in Range("AA1") for example. Then your Worksheet_Change macro can check if the row in which you are entering data in column I is equal the row number in Range("AA1") and if it is not equal, the eventcopy will not run.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
    If Target.Value <> "" And Target.Row = Range("AA1").Value Then
        eventcopy
    End If
End Sub

 

[Logit]

.
Track an entry elsewhere on the sheet. If that cell is empty ... run the macro. If the cell is not empty ... exit sub.

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("I:I")) Is Nothing Then
    If Target.Value <> "" And Range("A1").Value = "" Then
        MsgBox "Hi"
        Range("A1").Value = 1 '<-- cell location can be anywhere and value can be anything.
    Else
        Exit Sub
    End If
End If
End Sub

 

[Kamolga]

I would then work with a public boolean (variable that is true or false and can be called in any event/module) that I put to false on workbook open event or worksheet activate (with worksheet activate as below, if you leave the sheet and come back, it will run once again)

Public Eventdone As Boolean

Private Sub Worksheet_Activate()
Evendone = False
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("I:I")) Is Nothing Then
    If (Target.Value <> "" And Eventdone = False) Then
        Eventdone = True
        eventcopy
    End If
End If
End Sub

 

[still learning]

thank you for the macros,
I'm going to see which one is the easiest to make work (with my limited macro skills)
I be back during the week with questions.


mike

 

[Kamolga]

Mispelling

Private Sub Worksheet_Activate()
Even[COLOR=#ff0000]t[/COLOR]done = False
End Sub