Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Macro to detect if computer is locked

  1. #1
    Join Date
    23rd December 2011
    Posts
    4

    Macro to detect if computer is locked

    can I use a macro to detect if my computer is locked?

    I have a very complex document that is shared across several computers, I need to detect if one of the computers is locked...

    Rick
    Last edited by AAE; January 28th, 2012 at 21:25. Reason: revise thread title

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th March 2011
    Location
    Australia
    Posts
    1,198

    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
    Regards
    Anthony

    ​​​You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.




  3. #3
    Join Date
    23rd December 2011
    Posts
    4

    Re: can I use a macro to detect

    it is on the same network, I just need to detect if the computer is locked or not...

    Rick

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    8th March 2011
    Location
    Australia
    Posts
    1,198

    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.
    Regards
    Anthony

    ​​​You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.




  5. #5
    Join Date
    8th March 2011
    Location
    Australia
    Posts
    1,198

    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????
    Regards
    Anthony

    ​​​You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.




  6. #6
    Join Date
    23rd December 2011
    Posts
    4

    Re: can I use a macro to detect

    Smuzoen, thanks for the offer but I cant use any add on programs (IT restrictions) Here is a more in depth description of what I am doing and how I plan to do it:
    My program is for order tracking.
    On first run of the day it will automatically go out on the net and download the latest list of orders & status from the company that orders for us and then formats and sorts rearranges the data for my workbook and updates all order status'.
    BTW I have limited the rights to the users of this workbook, not everyone has the ability to fully utilize all functions, and also with all of the security that I placed in this workbook I can not “SHARE” but made it so it will run fine for most users as READ ONLY. My problem comes in when a user that does not have rights to extreme limited rights opens the workbook first, (at this point there copy is not READ ONLY and then an authorized user then loads the workbook as READ ONLY... I did develop a "Chat program" in excel to send messages between users, I also have the ability to restart the workbook of the user that opened the workbook first.
    this is now where it gets tricky.... I can force a restart with no problems, but I want to notify the user first before the authorized takes control...
    If the authorized user sends a message to the first user and there computer is locked the stops and waits till that computer is unlocked first... I need it to detect first if the computer is locked, if so then just close there copy of the workbook and then restarts the requesting users copy so they now will have full rights to use the workbook... some thing similar happens too if the first user is a authorized user, the routine will first save the workbook before shutting it down... also ONLY authorized users have the ability to chat and take control of this workbook.

    hope this is clearer for you...

    Rick

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    8th March 2011
    Location
    Australia
    Posts
    1,198

    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.
    Regards
    Anthony

    ​​​You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.




  8. #8
    Join Date
    23rd December 2011
    Posts
    4

    Re: can I use a macro to detect

    Smuzoen: when I tried using the timer it did not seem to time when the computer is locked, that is why I am trying to detect if the computer is locked... maybe I will try again when I get to work on next Tuesday.

    Thanks, Rick

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    11th April 2011
    Posts
    1

    Re: can I use a macro to detect

    I HAVE THE SOLUTION!!!!!!!!!!!!!!! :-)

    thanks for all of the help!



    Rick
    VB:
    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 
    
    
    Last edited by AAE; January 28th, 2012 at 21:27. Reason: add code tags, remove formatting tags from code

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    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.

    How to use code tags

    [code]
    your code goes between these tags
    [/code]

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

    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).
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Pivot Macro To Automatically Detect Data Range
    By TomTomThePipersSon in forum EXCEL HELP
    Replies: 7
    Last Post: May 28th, 2008, 21:09
  2. Detect Service Pack Macro
    By John Nevill in forum EXCEL HELP
    Replies: 1
    Last Post: November 28th, 2007, 21:50
  3. Detect auto refresh in VB and run a macro
    By GregHughes in forum EXCEL HELP
    Replies: 5
    Last Post: May 22nd, 2006, 14:14
  4. Macro to Detect matching cell values
    By churchskiz in forum EXCEL HELP
    Replies: 2
    Last Post: November 8th, 2005, 04:42

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno