close distinct workbook
 

Hi All,
I have a workbook that has a timer to close itself. the problem is that it
closes all other open workbooks!!!
Here is the code:
on 'Thisworkbook' : Call StartTimer
here is the code inside a module:

Public Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long) As Long

Public TimerID As Long
Public TimerSeconds As Single

Sub StartTimer()
If Sheets("Menu").Range("B39").Value < 0 Then
TimerSeconds = Sheets("Menu").Range("B39").Value * 60
Else
TimerSeconds = 1000 ' how often to "pop" the timer.
End If
TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
End Sub

Sub EndTimer()
On Error Resume Next
KillTimer 0&, TimerID
Workbooks("theone.xls").Close savechanges:=False
End Sub

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
ByVal nIDEvent As Long, ByVal dwTimer As Long)
CurrWB = ThisWorkbook.Name
Call EndTimer
End Sub


Any suggestions?
Thanks a lot in advance

close distinct workbook
 

It is probably failing, and thus shutting Excel down. If you have Option
Explicit the non-dimensioned variable CurrWB will cause this.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Lp12" wrote in message
...
Hi All,
I have a workbook that has a timer to close itself. the problem is that it
closes all other open workbooks!!!
Here is the code:
on 'Thisworkbook' : Call StartTimer
here is the code inside a module:

Public Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long) As Long

Public TimerID As Long
Public TimerSeconds As Single

Sub StartTimer()
If Sheets("Menu").Range("B39").Value < 0 Then
TimerSeconds = Sheets("Menu").Range("B39").Value * 60
Else
TimerSeconds = 1000 ' how often to "pop" the timer.
End If
TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
End Sub

Sub EndTimer()
On Error Resume Next
KillTimer 0&, TimerID
Workbooks("theone.xls").Close savechanges:=False
End Sub

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
ByVal nIDEvent As Long, ByVal dwTimer As Long)
CurrWB = ThisWorkbook.Name
Call EndTimer
End Sub


Any suggestions?
Thanks a lot in advance

close distinct workbook
 

Thanks a lot Bob....
I didn't quite get your response.. I've deleted the CurrWB object and gave
an hardcoded workbook 'theone.xls' .
Here is the updated code:

Public Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long) As Long

Public TimerID As Long
Public TimerSeconds As Single

Sub StartTimer()
If Sheets("Menu").Range("B39").Value < 0 Then
TimerSeconds = Sheets("Menu").Range("B39").Value * 60
Else
TimerSeconds = 1000 ' how often to "pop" the timer.
End If
TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
End Sub

Sub EndTimer()
On Error Resume Next
KillTimer 0&, TimerID
Workbooks("theone.xls").Close savechanges:=False
End Sub

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
ByVal nIDEvent As Long, ByVal dwTimer As Long)
Call EndTimer
Workbooks("theone.xls").Close savechanges:=False
End Sub


Thanks a lot,
Assaf

"Bob Phillips" wrote:

It is probably failing, and thus shutting Excel down. If you have Option
Explicit the non-dimensioned variable CurrWB will cause this.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Lp12" wrote in message
...
Hi All,
I have a workbook that has a timer to close itself. the problem is that it
closes all other open workbooks!!!
Here is the code:
on 'Thisworkbook' : Call StartTimer
here is the code inside a module:

Public Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long) As Long

Public TimerID As Long
Public TimerSeconds As Single

Sub StartTimer()
If Sheets("Menu").Range("B39").Value < 0 Then
TimerSeconds = Sheets("Menu").Range("B39").Value * 60
Else
TimerSeconds = 1000 ' how often to "pop" the timer.
End If
TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
End Sub

Sub EndTimer()
On Error Resume Next
KillTimer 0&, TimerID
Workbooks("theone.xls").Close savechanges:=False
End Sub

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
ByVal nIDEvent As Long, ByVal dwTimer As Long)
CurrWB = ThisWorkbook.Name
Call EndTimer
End Sub


Any suggestions?
Thanks a lot in advance