Run Macro Only Once

[rob51852]

Hi,

I have a macro that calls other macros when cell F2 contains the word closed. It uses a Change event and the data in Sheet1 gets overwritten each second.

I only want it to run once but it is running each time data is overwritten. How do I stop this?

Thanks

If ThisWorkbook.Worksheets("Sheet1").Range("F2") = "Closed" Then
Application.EnableEvents = False
Call CopyToStore
Call ClearData
Application.EnableEvents = True

Sub CopyToStore()

With ThisWorkbook.Worksheets("Data")
.Range("A3:L" & .Cells(Rows.Count, "A").End(xlUp).Row).Copy Destination:=ThisWorkbook.Worksheets("Store").Range("A" & .Rows.Count).End(xlUp).Offset(1)
End With

With ThisWorkbook.Worksheets("Store")
.Range("B:B").NumberFormat = "dd/mm/yyyy hh:mm:ss.000"
End With

End Sub

Sub ClearData()
With ThisWorkbook.Worksheets("Data")
.Range("A3:L100000").ClearContents
End With
End Sub

 

[dmt32]

One idea maybe if you want to stop the macro repeating permanently, would be to place a space in front of the word Closed after it has been first executed.

With ThisWorkbook.Worksheets("Sheet1").Range("F2")
    If .Value = "Closed" Then
        .Value = " Closed"
        Application.EnableEvents = False
        Call CopyToStore
        Call ClearData
    End If
End With
Application.EnableEvents = True

You could also change the Font colour to show condition has changed.

Dave

 

[rob51852]

Thanks Dave. Unfortunately the data in Sheet1 is overwritten each second by third party software so the macro continues to run multiple times.

 

[dmt32]

Thanks Dave. Unfortunately the data in Sheet1 is overwritten each second by third party software so the macro continues to run multiple times.

trick one - you may be able to make use the Range.ID property to solve this but its not permanent as values are lost when workbook closed

With ThisWorkbook.Worksheets("Sheet1").Range("F2")
    If .Value = "Closed" And Val(.ID) <> xlOff Then
        .ID = xlOff
        Application.EnableEvents = False
        Call CopyToStore
        Call ClearData
    End If
End With
Application.EnableEvents = True

Dave

 

[rob51852]

Thanks Dave. I've done a quick test and that seems to do the trick. I'll test properly over the weekend. Thanks again.

 

[dmt32]

Thanks Dave. I've done a quick test and that seems to do the trick. I'll test properly over the weekend. Thanks again.

welcome

Dave

 

[rob51852]

Hi Dave,

This works great, thanks.

Is there a way of rearming the macro when the last cell of the Store sheet is no longer the same as the value in N3 of Sheet1? When this scenario occurs F2 will no longer contain "Closed".

Something like:

If ThisWorkbook.Worksheets("Sheet1").Range("N3") <> ThisWorkbook.Worksheets("Store").Range("A" & WkSht.Rows.Count).End(xlUp)

Forgive me, I'm learning the VBA ropes and don't know if this is possible or how to integrate these conditions.

Thanks

 

[dmt32]

Is there a way of rearming the macro when the last cell of the Store sheet is no longer the same as the value in N3 of Sheet1? When this scenario occurs F2 will no longer contain "Closed".

Not fully sure if this it is what you want but see if goes in right direction

Dim lastcell As Range
    Dim wsStore As Worksheet
    
    Set wsStore = ThisWorkbook.Worksheets("Store")
    
    Set lastcell = wsStore.Cells(wsStore.Rows.Count, 1).End(xlUp)
    
    With ThisWorkbook.Worksheets("Sheet1").Range("F2")
're-set F2 when last cell of the Store sheet is no longer the same as the value in N3
        If .Offset(1, 8).Value <> lastcell.Value Then
            .Value = "Open": .ID = xlOn
        ElseIf .Value = "Closed" And Val(.ID) <> xlOff Then
            .ID = xlOff
            Application.EnableEvents = False
            Call CopyToStore
            Call ClearData
        End If
    End With
    
    Application.EnableEvents = True

Dave

 

[rob51852]

This is great again Dave. Thanks for all your help.

 

[dmt32]

This is great again Dave. Thanks for all your help.

welcome - gad suggestion helped

Dave