Shell command in an If?

[Stargazer]

Greetings and Salutations,

For some time now, we've been paining over a way to try and get a workbook to close automatically if it isn;t being used. We thought we had it, and then we found out that instead of learning that the file needs to be closed, people were discovering sneaky ways of making our countdown clock pause or crash.

We've hatched a devious plan, but it's hit a barrier. May I ask for the help of the VBAX brain-pool once again?

Our idea, since lots of people put their workstations on lock when they leave their desk, is to tell Excel "If the workstation is normal, continue. If it's locked, close the file."

This is what we've tried using:-


Sub CloseSheet

If Shell("rundll32.exe user32.dll,LockWorkStation", vbNormalFocus) Then
msgbox ("This would be the close command")
Else
msgbox ("Business as usual")
End IF

End Sub


What this does, is lock the workstation regardless. when we log back in, there is a message box telling us "This would be the close command". Well, great, but we don't want to call the shell command, we just want to check it. Is there something we've done wrong or do shell commands just not function like this?

Many thanks and kind regards,

Rob.

[Jan Karel Pieterse]

You could determine how long the mouse/keybd have not been used:

Private Type LASTINPUTINFO
cbSize As Long
dwTime As Long
End Type
Private Declare Sub GetLastInputInfo Lib "USER32" (ByRef plii As LASTINPUTINFO)

Function IdleTime() As Single
Dim a As LASTINPUTINFO
a.cbSize = LenB(a)
GetLastInputInfo a
IdleTime = (GetTickCount - a.dwTime) / 1000
End Function

[Stargazer]

Hi,

Thanks for that code. We've stuck in a module and tried running it with a little addition that writes the 'IdleTime' to a cell, just so we can see it working.

Admittedly, it looks as though you're about a quadrillion times more savvy with this stuff than I am and, alas, I'm having trouble understanding.

You have peaked our curiosity though. If this is doing what we think it's doing, it's a work of magic and genius.

Would you be okay to confirm, just exactly how we would implement this in a live environment?

Many thanks,

Rob.

[Jan Karel Pieterse]

See attached...

[Stargazer]

Hi,

Thanks for the workbook. I've had it open and tried getting it to do something but have yet to get it to close down.

I'll keep having a play with it and see if it gets us where we're trying to end up.

As a side-note, the countdown clock we're using currently uses Application.OnTime and those crafty end users have discovered that by putting a cell into Edit, it stops counting. I don't think that is something there'll ever be a cure for, but it would certainly be nice.

We've been trying everything to close it remotely as well, even going as far as trying to establish sneaky remote desktop access to the users PC's but WinXP is too well built and the network here too tightly restricted to be tricked by the likes of us.

Ho-hum,

Rob.

[shrivallabha]

"Witch" kind of users you have?

[Jan Karel Pieterse]

I apologize, the file contained an omission (GetTickCount function not being declared). Attached is the corrected version.

[Stargazer]

Hi,

I can confirm that the amended sheet does exactly what you said it would. After x-amount of time, it will close itself.

Unfortunately, like our countdown clock, it is still subject to the shortfalls of the Application.OnTime, such as the user putting the workbook into cell-edit and then going for a three hour meeting meaning that the rest of us are screwed.

So it's a great idea and certainly one that we can advance with for other projects. So for that much, massive thanks for the help.

However, getting the sheet to close when locked, as far as we can tell, is the only way... However, between the start of typing that last sentence and getting to these words now, I've spent two minutes discussing with the guy next to me that even if we did get the shell command to work, we'd still need a timer function to make the sheet check itself and would still be able to be overidden by stupid people who don't/can't follow simple instruction.

Hmph!

Maybe this is a problem that can't be solved. After all, there are other threads out there ina ll the forums about how to acheive this and nobody has a rock solid answer yet...

Cheers,

Rob

[Jan Karel Pieterse]

OK. This version uses the Windows API timer, which is capable of calling any routine in VBA, regardless of the state the Excel Application is in.

Mind you: be careful when using this API timer for other situations than the one demonstrated here!!

[Stargazer]

OooooOOOOooooooo...

Now 'that' is seriously cool!

Sir, I salute thee.

From my colleague and myself, a sincere 'Thank You' for helping us thwart the plans of the end-user-apocalypse.

Kind regards,

Rob.

[Jan Karel Pieterse]

You're welcome!

[Rwing]

You're welcome!
Hi Jan

I know this is an old post but it's worth resurrecting. I'm also trying to create an inactivity timer, and like in this thread, I need it to work when the cell is left in edit mode.

I have an ontime macro in VBA which will work when the cell is not being edited, whilst the API timer, as I understand it, works continuously and can't be isolated to only when the cell is edited, so this runs at a fixed period using a sendkey to move a cell out of edit mode (without the thisworkbook.close) at which time the ontime macro takes over and is responsible for closing excel. The reason being that the API timer alone seems to cause Excel to crash.

The two problems I have are, if I write something to the workbook, click X to close, and then cancel and keep Excel running, the API timer seems to stop working. And secondly, is there no way to isolate the sendkey only to the excel application (so that it works even when the window is minimized or another application is open, without showing the window. I can use appactivate * which doesnt seem to show the window, but I cant get it to reset back to the previous window AND when it goes back, to go back to the correct Excel window (incase many workbooks are open)). Is it also possible to restrict the Idletime function purely to Excel (only the workbook concerned) and have it not be triggered by activity outside that window. I don't fully understand the API mechanism so it would great if you could clarify :).

Many thanks

[Rwing]

I've got it to go back to the correct Excel window via appactivate, but nonetheless is there any way to actually stop the window showing or being brought to the foreground. Only other issues now are the X -> cancel -> disabling it and mouse activity outside that Excel window ^^.

[Jan Karel Pieterse]

I know of no way to detect how long Excel has been inactive given that it may be in cell edit mode.

[Rwing]

I know of no way to detect how long Excel has been inactive given that it may be in cell edit mode.
Hi Jan,

Thanks for your reply. That's not exactly what I mean. What I mean is, is it possible for the API Timer to realise for how long mouse and keyboard activity on a specific window, in this case the Excel workbook itself, has been inactive (even though there may be mouse and keyboard activity elsewhere on the computer). As I understand it, at the moment the specific procedure doesnt detect any activity in the Excel workbook in question, what it does is detect mouse and keyboard activity globally and based on that it implements a key in that workbook , regardless of whether Excel itself is in edit mode or not. So it doesn't matter whether Excel is in edit mode or not.

If that's not possible, the only other thing I would hope to resolve is the click X --> cancel option, which kills the timer completely. Is there absolutely no way to prevent this?

Thanks again.

[Jan Karel Pieterse]

I don't think there is a windows API way to know for how long a certain application window has not been "touched".

I can imagine a way to do it in Excel, using application events, but the drawback is that it will slow down Excel (the code would be responding to every change on every sheet in every cell). It will also not detect formatting changes and other changes that the VBA object model has no events for.

[Jan Karel Pieterse]

See if this version resolves the close problem.

[Rwing]

Hi Jan,

I've noticed that by default, if another workbook is entered and used, edit mode dies in the other workbook, so the VBA timer kicks in anyway. Most of the inactivity in the Excel window with the timer in my case will be caused by activity on other workbooks, so I guess the timer not being able to specifically detect a window's activity is not a very important issue, which I didnt realise before! I will try the amended workbook with my VBA timer, if that works I think it's perfect :).

Thank you again!

[Rwing]

That's weird, the cursor kill on each workbook seems not to be working anymore.. that's weird as that's how it worked yesterday.

[Jan Karel Pieterse]

The version I sent does not get you out of edit mode.