Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Desktop Shortcut

    Hello,

    I have a workbook which performs several actions after the Application.Visible property is set to false. Several dozen people are using this and a common problem is that they close it and for various reasons, double click the shortcut to reopen the file.

    If the file is still open, the "locked for editing" message appears.

    What would be the best way to solve this? Is there a way to disable or hide the shortcut referring to this file from within Visual Basic until the file is closed?

    I am using Excel 2000/Win98SE

    Thanks,
    Michael Read

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Desktop Shortcut

    You could use the Close event to tell them you are still busy and can't close yet, then cancel the close.
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Desktop Shortcut

    Thanks for the reply Legare,

    In my workbook, the Application.Visible=False is in the first part of the Close event. In other words, the workbook needs to be closed because of various things such as creating text files and other actions which occur as part of the close event. I just want to prevent them from attempting to reopen it until the code in the Close event is complete and the workbook is actually closed.

    Sorry, I should have been more specific in my original post!

    Thanks,
    Mike

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Desktop Shortcut

    I am not quite sure if I understand your problem, but can't you just disable the shortcut for a short time (the time needed for doing several actions) and then enable it again (or reset it)?

    Sub DisableShortcut()
    CommandBars("MyShortcut").Enabled = False
    End Sub

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Desktop Shortcut

    I don't think he is talking about a shortcut on a menu bar. I think he is talking about a user double clicking on a file shortcut on the desktop.
    Legare Coleman

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Desktop Shortcut

    My knowledge of the English language is running short here (sorry for that), but I only know Shortcut keys and Shortcut Menus. Do you mean something different of these two possibilities? I don't understand what you mean by a file shortcut on the desktop: is it a control (e.g. a button) on the spreadsheet with a macro attached? In this case, there may be a possibility to make it invisible for a short time (or is this not the case?), or disable it? Or disable the shortcut key?

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Desktop Shortcut

    In English Windows, if I right click on a file on the desktop on in Explorer, I get a pop up menu that includes a command "Create Shortcut". If I click on that command, I get a new Icon with a small arrow in the lower left corner and the name say "Shortcut to ...". It is actually a file with a file extension of .lnk. I think that is what he is talking about.
    Legare Coleman

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Desktop Shortcut

    Thanks Legare. I think I got it. I saw that Michael has got an answer on the VB/VBA newsgroup on Woody's Lounge. In the past I have used a VB-program to check if another application was running, using API calls, but I think it checks if e.g. Excel is running, not a particular workbook with macros. I'll try to find it back. I remember that it made use of the GetModuleUsage API routine. I think APIs can do the job if a application-related Window is open (visible).

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Desktop Shortcut

    Michael,

    I don't know how your code is organized, but here you have some code that is checking when an application is running, using the caption text of the application's window. I haven't checked what is happening if the window is made invisible. The code is using API calls, so you can add it to your VBA code; e.g. after you click the shortcut, check first if the application is running and take action according to the outcome of this.

    Declare Function GetActiveWindow Lib "user32" () As Long
    Declare Function GetNextWindow Lib "user32" Alias "GetNextQueueWindow" _
    (ByVal hwnd As Long, ByVal wFlag As Long) As Long
    Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
    (ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
    Declare Function GetWindowTextLength Lib "user32" Alias "GetWindowTextLengthA" (ByVal hwnd As Long) As Long

    Public Const GW_HWNDNEXT = 2

    Function Application_Is_Running(app_text As String) As Boolean
    Dim Window_Text As String
    Dim First_Handle As Long
    Dim Current_Handle As Long
    Dim Text_length As Long
    First_Handle = GetActiveWindow()
    Current_Handle = First_Handle
    Do
    Current_Handle = GetNextWindow(Current_Handle, GW_HWNDNEXT)
    If Current_Handle = First_Handle Then
    Application_Is_Running = False
    Exit Function
    End If
    Text_length = GetWindowTextLength(Current_Handle)
    If Text_length Then
    Window_Text = String(Text_length + 1, 32)
    Text_length = GetWindowText(Current_Handle, Window_Text, Len(Window_Text))
    If InStr(Window_Text, app_text) Then
    Application_Is_Running = True
    Exit Function
    End If
    End If
    Loop
    End Function

    Sub MainProg()
    MsgBox Application_Is_Running("Excel Board - Woody's Lounge")
    End Sub

  10. #10
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Desktop Shortcut

    Thanks Hans,

    I haven't tried it yet, but it gets me going in the right direction.

    Mike

Posting Permissions

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