Macro pauses when is not the Active window???

[mrjixies]

Hi All!

I hope someone can please help me? I've got a very simple vba macro but it pauses when the window/sheet is not the active sheet??? Once I return to the Sheet where the macro is running it starts again??? I need to run this macro in the background so I can work on other excel workbooks. Is there a windows 10 security feature or something I need to turn on or off? Or is it the vba code. I can't find the answer on google.

I use 2 command buttons with the following code:

Option ExplicitPublic dTime As Date


Sub ValueStore()
    Dim dTime As Date
    Dim nextRow As Long
    'Calculate the row number once
    nextRow = Cells(Rows.Count, "B").End(xlUp).Row + 1
    Range("B" & nextRow).Value = Range("A1").Value
    Call StartTimer
End Sub




Sub StartTimer()
    dTime = Now + TimeValue("00:00:01")
    Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub


Sub StopTimer()
    On Error Resume Next
    Application.OnTime dTime, "ValueStore", Schedule:=False
End Sub

Thanks in advance for any help!!!

 

[Chameleon64]

Does it work if you explicitly refer to the workbook and worksheet names in front of the CELL and RANGE commands?

 

[mrjixies]

Thanks for your help Chameleon64!

I tried to refer to the workbook and worksheet names. I hardly know anything about vba but I googled it and tried the following code.

Option ExplicitPublic dTime As Date


Sub ValueStore()
    Dim dTime As Date
    Dim nextRow As Long
    'Calculate the row number once
    nextRow = Cells(Rows.Count, "B").End(xlUp).Row + 1
    Range[COLOR=#ff0000][B]("[testbook.xlsm]Sheet2!B"[/B][/COLOR] & nextRow).Value = Range[COLOR=#ff0000][B]("[testbook.xlsm]Sheet2!A1")[/B][/COLOR].Value
    Call StartTimer
End Sub




Sub StartTimer()
    dTime = Now + TimeValue("00:00:01")
    Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub


Sub StopTimer()
    On Error Resume Next
    Application.OnTime dTime, "ValueStore", Schedule:=False
End Sub

If I try to refer to the cell above the 2 altered codes. I get a runtime error 13?

Option ExplicitPublic dTime As Date


Sub ValueStore()
    Dim dTime As Date
    Dim nextRow As Long
    'Calculate the row number once
    nextRow = Cells(Rows.Count, "[COLOR=#ff0000][B][testbook.xlsm]Sheet2!B[/B][/COLOR]").End(xlUp).Row + 1 [B][I]'Altering this code gives a runtime error 13[/I][/B]
    Range("[testbook.xlsm]Sheet2!B" & nextRow).Value = Range("[testbook.xlsm]Sheet2!A1").Value
    Call StartTimer
End Sub




Sub StartTimer()
    dTime = Now + TimeValue("00:00:01")
    Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub


Sub StopTimer()
    On Error Resume Next
    Application.OnTime dTime, "ValueStore", Schedule:=False
End Sub

If try to run the macro with only the 2 altered codes it still pauses when I go to another sheet or workbook?

 

[Chameleon64]

The code in red should tell your macro which workbook and worksheet to work on:

Option Explicit
Public dTime As Date


Sub ValueStore()
    Dim dTime As Date
    Dim nextRow As Long

    [COLOR=#ff0000]Dim wb as Workbook: Set wb = ThisWorkbook
    Dim ws as Worksheet: Set ws = Sheets("Sheet2")[/COLOR]

    'Calculate the row number once
    nextRow = [COLOR=#ff0000]wb.ws.[/COLOR]Cells(Rows.Count, "B").End(xlUp).Row + 1
     [COLOR=#ff0000]wb.ws.[/COLOR]Range("B" & nextRow).Value = [COLOR=#ff0000]wb.ws.[/COLOR]Range("A1").Value
    Call StartTimer
End Sub




Sub StartTimer()
    dTime = Now + TimeValue("00:00:01")
    Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub


Sub StopTimer()
    On Error Resume Next
    Application.OnTime dTime, "ValueStore", Schedule:=False
End Sub

 

[mrjixies]

Thank you so much for that answer!!! I Definitely owe you a beer!!! Only I got an error while running it "error 438 object doesn't support this property or method" So I googled some more and I got the following code.

Option ExplicitPublic dTime As Date

[COLOR=#008000]'Deleted the Dim wb
'Deleted the Dim ws[/COLOR]


Sub ValueStore()
    Dim dTime As Date
    Dim nextRow As Long




    'Calculate the row number once
    nextRow = [COLOR=#ff0000]ThisWorkbook.Sheets("Sheet2")[/COLOR].Cells(Rows.Count, "B").End(xlUp).Row + 1
     [COLOR=#ff0000]ThisWorkbook.Sheets("Sheet2")[/COLOR].Range("B" & nextRow).Value = [COLOR=#ff0000]ThisWorkbook.Sheets("Sheet2")[/COLOR].Range("A1").Value
    Call StartTimer
End Sub








Sub StartTimer()
    dTime = Now + TimeValue("00:00:01")
    Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub




Sub StopTimer()
    On Error Resume Next
    Application.OnTime dTime, "ValueStore", Schedule:=False
End Sub

Now it's working fine when I go the another sheet or another workbook. Except when I type something in a cell or different workbook it pauses again until I go out of that cell then it continues???