Conflicting VBA Coding

[Alberta Rose]

I have code to open a certain spreadsheet when opening the file:

Private Sub Workbook_Open()
Worksheets("Main").Active
MsgBox "This workbook will auto-close after 30 minutes of inactivity"
Call SetTime
End Sub

But I also have code the start the timer to close on inactivity:

Private Sub Workbook_Open()

On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)

On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True

End Sub

I am getting an ambigous error. I think it's because I have the Private Sub
Workbook_Open() in each.

Can anyone tell me what to do with this?

Laurie

 

[Dave Peterson]

What does the "SetTime" procedure do?

If it starts the timing for your SaveAndClose stuff, then you don't need the
second workbook_Open procedure.

 

[Alberta Rose]

Hi Dave.

the SetTime procedure was in the code I found to make the "Main" sheet the
one to open first.

When I removed the second Workbook open procedure, I got an error message at
the SetTime line, when I removed that (figuring it was conflicting with the
idle timing procedure) I then got an error on Worksheets ("Main").Active.
ARGHHHHHH. All I want it to do is to open the "Main" sheet when the file is
first opened, then start timing from there for the 30 minutes idle time.

Suggestions?
Laurie

 

[Dave Peterson]

Share the code in both procedures.

It'll make the modifications easier.

 

[Alberta Rose]

Hi again. The codes for both of the procedures were in my original post. I
have replaced the first (on open) code with:



Private Sub Workbook_Open()
Worksheets("Main").Activate
MsgBox "This workbook will auto-close after 30 minutes of inactivity"

AND I REMOVED THE SECOND PRIVATE SUB_WORKBOOK LIKE YOU SUGGESTED - SO FAR I
DON'T HAVE ANY ERROR MESSAGES SHOWING UP, BUT AM CONCERNED THAT THE FILE IS
CLOSING EVEN IF THERE IS NO IDLE TIME.

On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)

On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True

End Sub

 

[Dave Peterson]

Where does the Num_Minutes variable get set?
Where does the RunWhen variable get set?





If the line that's causing the error is:
Worksheets("Main").Activate
Then I'd bet you don't have a visible worksheet named Main in that workbook.

Hi again. The codes for both of the procedures were in my original post. I
have replaced the first (on open) code with:

Private Sub Workbook_Open()
Worksheets("Main").Activate
MsgBox "This workbook will auto-close after 30 minutes of inactivity"

AND I REMOVED THE SECOND PRIVATE SUB_WORKBOOK LIKE YOU SUGGESTED - SO FAR I
DON'T HAVE ANY ERROR MESSAGES SHOWING UP, BUT AM CONCERNED THAT THE FILE IS
CLOSING EVEN IF THERE IS NO IDLE TIME.

On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)

On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True

End Sub

 

[Alberta Rose]

The coding is in a module and is:


Public RunWhen As Double
Public Const NUM_MINUTES = 30

Public Sub SaveAndClose()
ThisWorkbook.Close savechanges:=True
End Sub

 

[Dave Peterson]

I don't understand the problem.

If you're just afraid that it's not working correctly, then do some testing but
change the runwhen to:

RunWhen = Now + TimeSerial(0, 0, NUM_MINUTES)
(that should be every 30 seconds)

If it's too long, change the interval to something smaller (10 seconds) and
start changing selections/values.

The coding is in a module and is:

Public RunWhen As Double
Public Const NUM_MINUTES = 30

Public Sub SaveAndClose()
ThisWorkbook.Close savechanges:=True
End Sub