Macro pauses when is not the Active window???

mrjixies

New Member
Joined
Mar 26, 2012
Messages
11
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:

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!!!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Chameleon64

New Member
Joined
May 10, 2018
Messages
40
Does it work if you explicitly refer to the workbook and worksheet names in front of the CELL and RANGE commands?
 

mrjixies

New Member
Joined
Mar 26, 2012
Messages
11
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.

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?

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, "[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

New Member
Joined
May 10, 2018
Messages
40
The code in red should tell your macro which workbook and worksheet to work on:

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

New Member
Joined
Mar 26, 2012
Messages
11
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.

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???
 

Forum statistics

Threads
1,148,291
Messages
5,745,884
Members
423,983
Latest member
blackworx

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