ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Conflicting VBA Coding (https://www.excelbanter.com/excel-programming/434358-conflicting-vba-coding.html)

Alberta Rose
Conflicting VBA Coding
 
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
Conflicting VBA Coding
 
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 wrote:

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

Alberta Rose
Conflicting VBA Coding
 
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" wrote:

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

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

Dave Peterson
Conflicting VBA Coding
 
Share the code in both procedures.

It'll make the modifications easier.



Alberta Rose wrote:

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" wrote:

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

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


--

Dave Peterson

Alberta Rose
Conflicting VBA Coding
 
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" wrote:

Share the code in both procedures.

It'll make the modifications easier.



Alberta Rose wrote:

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" wrote:

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

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


--

Dave Peterson

Dave Peterson
Conflicting VBA Coding
 
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.



Alberta Rose wrote:

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" wrote:

Share the code in both procedures.

It'll make the modifications easier.



Alberta Rose wrote:

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" wrote:

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

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


--

Dave Peterson


--

Dave Peterson

Alberta Rose
Conflicting VBA Coding
 
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" wrote:

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.



Alberta Rose wrote:

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" wrote:

Share the code in both procedures.

It'll make the modifications easier.



Alberta Rose wrote:

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" wrote:

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

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


--

Dave Peterson


--

Dave Peterson

Dave Peterson
Conflicting VBA Coding
 
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.

Alberta Rose wrote:

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" wrote:

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.



Alberta Rose wrote:

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" wrote:

Share the code in both procedures.

It'll make the modifications easier.



Alberta Rose wrote:

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" wrote:

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

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


All times are GMT +1. The time now is 08:05 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com