PDA

View Full Version : Solved: VBA



joeyp
09-05-2012, 06:28 AM
Hi all,

What i am trying to do is create a spreadsheet that after 1 minute of inactivity goes to a sheet that is linked to a powerpoint, this powerpoint pops up and in the background the excel sheet goes to the main menu.

then after another minute of inactivity it does the same, a sort of screensaver effect with a powerpoint presentation.

So what I have so far is,

ThisWorkbook


Private Changed As Boolean
Private Sub Workbook_Activate()
Changed = False
Application.OnTime Now + TimeValue("00:00:05"), procedure:="SelectIndex"
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Source As Range)
Changed = True
End Sub

Which works to an extent, i want it to stop counting down if somebody is active, such as swithing sheets but it doesnt, it just counts to 5 and opens up the presentation even if im flickin through sheets.

It also only does it once and then stops after. Which I want it to do it again after inactivity :)
In SelectIndex I have


Sub SelectIndex()
Sheets("Sheet1").Select

End Sub

sheet 1 is the sheet that pops up the powerpoint and this sheet has some code which is:



Private Sub Worksheet_Activate()
ActiveSheet.Shapes("Object 1").Select
Selection.Verb
Sheets("Sheet2").Select

End Sub


Object 1 is the powerpoint and sheet2 is the main menu

Please help :)

Bob Phillips
09-05-2012, 06:54 AM
Private nTime As Date
Const proc As String = "SelectIndex"

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
Call SetTimer
End Sub

Private Sub SetTimer()
If nTime <> 0 Then
MsgBox "yes"
Call Application.OnTime(EarliestTime:=nTime, Procedure:=proc, Schedule:=False)
End If
nTime = Now + TimeValue("00:00:05")
Application.OnTime nTime, Procedure:=proc
End Sub

joeyp
09-05-2012, 07:01 AM
Private nTime As Date
Const proc As String = "SelectIndex"

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
Call SetTimer
End Sub

Private Sub SetTimer()
If nTime <> 0 Then
MsgBox "yes"
Call Application.OnTime(EarliestTime:=nTime, Procedure:=proc, Schedule:=False)
End If
nTime = Now + TimeValue("00:00:05")
Application.OnTime nTime, Procedure:=proc
End Sub



Thank you for the reply! :)

Does all that go into my ThisWorkbook? :) because thats what I just did and it didn't work :)

Bob Phillips
09-05-2012, 07:15 AM
Yes it does. Where is the procedure SelectIndex, and what does it do?

Oh, btw, get rid of the MsgBox in my code, that was just testing.

joeyp
09-05-2012, 07:19 AM
Yes it does. Where is the procedure SelectIndex, and what does it do?

Oh, btw, get rid of the MsgBox in my code, that was just testing.



Sub SelectIndex()
Sheets("Sheet1").Select

End Sub


It basically takes us to sheet1 which is linked with a powerpoint, whenever sheet 1 is selected, a macro is set up to open up the presentation and switch back to sheet 2 so when the presentation is closed you are back to the main menu :)

Oh and its a module :)

joeyp
09-06-2012, 12:59 AM
Yes it does. Where is the procedure SelectIndex, and what does it do?

Oh, btw, get rid of the MsgBox in my code, that was just testing.


Any more ideas?? :)

I think it has something to do with
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
Call SetTimer

Because When you write in the boxes, it doesn't switch to the presentation but when you leave it it does! which is a step in the right direction! but I want it so that not when somebody is writing but when somebody is switching sheets, so just viewing the sheets one by one :) I tried Worksheet_Activate but it doesnt work :)


It also only does it once, then when i do it again i get an error of
"Run time error '1004'
Method 'OnTime' of object '_ Application' Failed "

joeyp
09-06-2012, 02:47 AM
Private nTime As Date
Const proc As String = "SelectIndex"

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Call SetTimer
End Sub

Private Sub SetTimer()
If nTime Then
Call Application.OnTime(EarliestTime:=nTime, Procedure:=proc, Schedule:=False)
End If
nTime = Now + TimeValue("00:00:05")
Application.OnTime nTime, Procedure:=proc
End Sub

Now works, but only if im on the sheet with the presentation is, if i end up on a different sheet
then i get that error! weird!

joeyp
09-06-2012, 03:43 AM
[Solved]
A sneaky "On Error Resume Next"