Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Conflicting VBA Coding
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 54
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 54
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 54
Default 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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 54
Default 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

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Conflicting Calculations Kay Excel Discussion (Misc queries) 1 February 16th 10 10:41 PM
Conflicting vlookups Bruister via OfficeKB.com Excel Worksheet Functions 1 October 17th 09 04:57 PM
Conflicting VBA's pdberger Excel Programming 2 March 3rd 08 08:52 PM
Conflicting Code? Frank Kabel Excel Programming 1 July 21st 04 05:00 PM
Conflicting VB Code sowetoddid[_37_] Excel Programming 9 June 11th 04 08:19 PM


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

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement