CODEWITHSUNDEEP
vbaexcel
user3496218
user3496218

Reputation: 207

cannot select another workbook when macro enabled workbook open

I have a macro enabled workbook that uses code to set a timer - this is a shared book and often people will be in the book and leave their desk etc. It is set to save & close after 30 minutes of inactivity. That function seems to work fine but the problem I am running into is that when I have other workbooks open I cannot select them, either by clicking on the tile (Windows 7) or by alt+tab, the only way to move around is ctrl+shift. If it was just me using the workbook that would not be an issue - does anybody know why this is happening? Code posted below: 

Private Sub Workbook_Open()
StartTimer
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
StartTimer
End Sub


Const idleTime = 1800 'seconds
Dim Start
Sub StartTimer()
Start = Timer
Do While Timer < Start + idleTime
DoEvents
Loop
Application.DisplayAlerts = False
ActiveWorkbook.Close True
Application.DisplayAlerts = True
End Sub

Upvotes: 0

Views: 352

Answers (1)

djikay
djikay

Reputation: 10638

To start off with, I'm not entirely sure why you're getting that behaviour, but please keep reading. I suspect it has something to do with your busy-wait loop (the one with the DoEvents), but that's just a hunch. Someone else might have a (better) explanation.

Anyway, perhaps using Application.OnTime is a better approach. You can schedule an operation to happen at a certain time or interval and there's an option to cancel and replace a previously scheduled operation. You'll need that facility in the Workbook_SheetChange event handler.

In short, you can do the following:

  1. Add a new routine to save and exit the workbook, let's call it: SaveWorkbookAndExit. This will be called by OnTime later.

  2. Replace the contents of your Workbook_SheetChange event handler as follows:

    Application.OnTime Now + TimeValue("00:30:00"), _
                   "SaveWorkbookAndExit", _
                   Schedule:=False

    This basically schedules the routine SaveWorkbookAndExit to be called in 30 minutes from now. Notice the Schedule:=False -- it signifies that a previously set procedure should be cleared/cancelled, which is very important.

  3. Also add the same code as above to your Workbook_Open event handler. Unlike above though, note that you should set Schedule:=True in here!

And that's it. When the workbook is opened, a new SaveWorkbookAndExit operation is scheduled. Then, every time you go into Workbook_SheetChange, the previously scheduled operation will be cancelled and a new one will be scheduled in its place.

I haven't tested any of this, but I hope you get the idea. Please let me know if you need me to explain something further.

Upvotes: 1

Related Questions