Macro to detect if computer is locked

Re: can I use a macro to detect

Need more information - is it your computer or a remote computer on the same network? Does your machine need to be locked (this can be done with vb script)? or Does your computer need to be unlocked? Is it at a certain time - is it something a scheduled task could be involved with? Really need more information as to what you want to do.
You certainly can detect if your computer is locked with .Net but unsure about VBA

Re: can I use a macro to detect

Someone else may have a better solution however I can offer you this. I have a VB.net application that essentially runs in the background and records when a user locks a computer and timestamps the lock and then records when the computer is unlocked and timestamps the unlock. This application writes the lock/unlock status to a text file (which could be on the computer the program is running on or a UNC path). From the VBA macro you could read the text file (which is essentially a log of locking/unlocking a computer) and use this to determine if the computer is locked. That is the only solution I can offer you however someone else may have a better solution. In terms of a pure VBA solution I do not have one. If you would like the VB.net solution I can either give you the source code or just the executable that runs on the machine you want to check for lock status (or both if you want). All you would need to do is incorporate into your VBA macro a routine to read a text file and loop through the text file to the last line - the last line of the text file will be the computer lock status. Let me know if you would like the code/executable or someone else may have a pure VBA solution.

Re: can I use a macro to detect

BTW - You know you can still access a file on a computer even if it is locked? If I had a better idea of what you were doing perhaps the answer is not as complicated as is sounds????

Re: can I use a macro to detect

How about this as a solution - why not send a message to the user and use a timer control - if the user with limited rights does not respond to the message within a certain time frame then initiate a restart to allow authorised users to take control of the workbook - the assumption is this - if the restricted user does not respond within a reasonable time then assume the user has the computer locked or is away from their machine. As I said in my previous post I know of no API calls in VBA to detect locked status of a workbook - I can only achieve what you want specifically through vb.net.
PS: As well you can determine if the file is locked or not - if the file is not locked then no one has the file open - I would test that condition first before sending a message (or perhaps you do test for file locked status??? anyway - however it is something I would do first because if the file is not locked then there is no point going through the normal process you describe.

Re: can I use a macro to detect

I HAVE THE SOLUTION!!!!!!!!!!!!!!!

thanks for all of the help!

Rick

Private Declare Function SwitchDesktop Lib "user32"(ByVal hDesktop As Long) As Long
Private Declare Function OpenDesktop Lib "user32" Alias "OpenDesktopA" (ByVal lpszDesktop As String, ByVal dwFlags As Long, ByVal fInherit As Long, ByVal dwDesiredAccess As Long) As Long
Private Declare Function CloseDesktop Lib "user32" (ByVal hDesktop As Long) As Long
Private Const DESKTOP_SWITCHDESKTOP As Long = &H100




 Sub Test
Dim p_lngHwnd As Long
Dim p_lngRtn As Long
Dim p_lngErr As Long


 p_lngHwnd = OpenDesktop(lpszDesktop:="Default", dwFlags:=0, fInherit:=False, dwDesiredAccess:=DESKTOP_SWITCHDESKTOP)


If p_lngHwnd = 0 Then
 System = "Error"
Else
 p_lngRtn = SwitchDesktop(hDesktop:=p_lngHwnd)
 p_lngErr = Err.LastDllError


If p_lngRtn = 0 Then
If p_lngErr = 0 Then
 System = "Locked"
Else
 System = "Error"
End If
Else
 System = "Unlocked"
End If


 p_lngHwnd = CloseDesktop(p_lngHwnd)
End If
End Sub

Re: Macro to detect if computer is locked

Rick,

Please read the Forum Rules.

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.
I've added the tags for you this time only. Be sure to use them in future posts.

[COLOR="navy"]How to use code tags[/COLOR]

[noparse]

[/noparse]
[COLOR="navy"]your code goes between these tags[/COLOR]
[noparse]

[/noparse]

----------------------------------------------------------

BTW

rjh1 seems to be the same user as rickjhorne in the previous posts in this thread. This combined with thanks for help shows this to be same member.
Why are you posting under different user names?

Please note the change to the thread title and in future threads take care to more precisely define the title (they are used in forum searches so accuracy means a lot).

Hi,

I tried this code with 64 bit excel and it doesn't run. It gives me below error code.

"The code in this project must be updated for use on 64-bit systems.
Please review and update Declare statements and then mark them with the PtrSafe attribute."

Also the following code is highlighted in Red while pasted on a fresh module.

Private Declare Function SwitchDesktop Lib "user32"(ByVal hDesktop As Long) As Long
Private Declare Function OpenDesktop Lib "user32" Alias "OpenDesktopA" (ByVal lpszDesktop As String, ByVal dwFlags As Long, ByVal fInherit As Long, ByVal dwDesiredAccess As Long) As Long
Private Declare Function CloseDesktop Lib "user32" (ByVal hDesktop As Long) As Long

Note: I am running on Windows 10 64-bit operating system

Are these computers on the same LAN or in different areas of the network?

[SIZE=11px]@Shazra626 just add PTRsafe to each declaration

Ex. [/SIZE][SIZE=11px]Private Declare Function SwitchDesktop Lib "user32"(ByVal hDesktop As Long) As Long[/SIZE]
[SIZE=11px]becomes [/SIZE][SIZE=11px]Private Declare Ptrsafe Function SwitchDesktop Lib "user32"(ByVal hDesktop As Long) As Long[/SIZE]
[SIZE=11px]and it should be fine[/SIZE]