Desktop Shortcut

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Thanks Hans,

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

Mike

Reply | Quote