Run Time Error '1004':

[JAMIR]

Hi,

I have added runing clock in my workbook.

Global clockOn As Boolean
Sub runClock()
Range("GatePass!L5").Value = Now()
If clockOn = True Then
Application.OnTime Now + TimeValue("00:00:01"), "runClock"
End If
End Sub
Sub startClock()
clockOn = True
runClock
End Sub
Sub stopClock()
clockOn = False
End Sub

My Clock is runing very fine only ., but only that workbook open. If i open 2nd excel file it gives
Run Time Error '1004':
Method range of object _Global failed.

I can not open 2nd excel file while those workbook open. If End clock stop working or if Debug it gives
Range("GatePass!L5").Value = Now()

What should i do?

Zameer Shaikh

 

[NARAYANK991]

Hi Zameer ,

Using a fully qualified reference seems to work. Try this code :

Global clockOn As Boolean
 
Sub runClock()
    Range("[Hours.xls]GatePass!L5").Value = Now()
    If clockOn = True Then
      Application.OnTime Now + TimeValue("00:00:01"), "Hours.xls!runClock"
    End If
End Sub
 
Sub startClock()
    clockOn = True
    runClock
End Sub
 
Sub stopClock()
    clockOn = False
End Sub

Narayan

 

[JAMIR]

Hello Nayaran Sir,

I used above code, but it gives Compile error.
Constant, fixed length string, array, user-defined types and Declare statements not allowed as public member of object module..

My Workbook name is SIMS.xlsm

Regards,

Zameer Shaikh

 

[JAMIR]

Hello Narayan Sir,

Sorry I have add above code in the Gatepass sheet!. I am week in VBA that y it happend.

Thats y its give error and i did not change Wookbook name as SIMS on Hours.


But there is once little problem, i can able to open 2nd excel file. but if close SIMS.xlsm during open two file, SIMS.xlsm file open automatically. for example SIMS.xlsm & Hours.xls files open and if i want to close SIMS.xlsm file it open automatically. And if i close Hours.xls file then SIMS.xlsm closed.

Afterall it works fine. Thanks a lot sir. You rock sir.

Warmally Regards,

ZAMEER SHAIKH

 

[SirJB7]

Hi, JAMIR!
Put the code into a standard module and not into a object (either workbook or worksheet) class module.
Regards!

 

[NARAYANK991]

But there is once little problem, i can able to open 2nd excel file. but if close SIMS.xlsm during open two file, SIMS.xlsm file open automatically. for example SIMS.xlsm & Hours.xls files open and if i want to close SIMS.xlsm file it open automatically. And if i close Hours.xls file then SIMS.xlsm closed.

Hi Zameer ,

Can you explain the problem in a little more detail ? Or can you upload your file which has the timer code ?

Narayan

 

[SirJB7]

Hi, JAMIR!
Just in case...
This:

Put the code into a standard module and not into a object (either workbook or worksheet) class module.

was for this:

I used above code, but it gives Compile error.
Constant, fixed length string, array, user-defined types and Declare statements not allowed as public member of object module..

Regards!