VBScript - Show only Userform - Userfrom does not apear "In Front"

[weslyoc]

I am using a VBScript to launch an excel file with the application hidden so that only the userform appears and the application never shows (not even a flash as is typically seen with Application.Visible = False use).

Everything works fine, the script opens open and hides the application then it open the excel file showing only the form.

The problem is that the form appears behind any window that may be open.

Important info to note: I have modified my registry to force excel (2007) to open each file in a separate instance. This was to resolve the issue of hiding other open sheets when this this particular file was opened and the Application.Visible = False code was run.

Here is my VBScript:

Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Visible = False
objExcel.Workbooks.Open("K:\PFT\Project Number Generator - Final.xlsm")

When the file is opened this sub takes over:
------------------------------------------------------------
Private Sub Workbook_Open()
Application.Visible = False
UF1.Show
End Sub
------------------------------------------------------------
So, UF1 appears, as it should just fine and dandy. It just doesn't have focus and appears behind everything already open.

My initial solution was to implement some code I found from various sources that forces the userform to always be on top of every window. That seemed to work fine except that I had a hard time getting the userform to stop being on top of everything. The idea was to have the userform open on top of everything and as soon as a button was pressed to use the user form the "always on top" state would go away.

Here is the code I had for that:
------------------------------------------------------------
Private Sub Workbook_Open()
Application.Visible = False
UF1.Show vbModeless
End Sub

Private Declare Function SetWindowPos Lib "user32" _
(ByVal hWnd As Long, ByVal hWndInsertAfter As Long, ByVal x As Long, ByVal Y As Long, _
ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long

Private Const HWND_TOPMOST = -1 'bring to top and stay there
Private Const SWP_NOMOVE = &H2 'don't move window
Private Const SWP_NOSIZE = &H1 'don't size window

Private Declare Function GetForegroundWindow Lib "user32" () As Long
Private Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Dim lHwnd As Long

Sub UserForm_Initialize()

lHwnd = FindWindow("ThunderDFrame", "UserForm1")

If lHwnd <> GetForegroundWindow Then
Call SetWindowPos(lHwnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE)
End If
End Sub
----------------------------------------------------------------
What I was testing was having it open the UF1 form "always on top", which worked, but I wanted a button that when pressed, the form would no longer be 'always on top".

Ultimately what this button does will be coded into the main button on the form so that this change in state happens automatically as the form is used. The buttons on UF1 prompt other userforms to appear as data is entered to be used by the main program.

Any ideas as to how i can accomplish this? I have searched everywhere and I cant seem to nail this one down.

 

[Jaafar Tribak]

Thanks for the input. Spent a little time and i think i came up with a solution that works well for me.
Basically when a user opens the file, it writes the username to a cell and saves the file (the save is important).
then when another user tries to open the file, it checks if the file is read only and if it is, a message box appears saying the file is in use by X user, and closes the file. This ensures only one user at a time will open the file.

As a summary:

Open excel file containing a user form and only show user form, never show excel, not even the initial flash.
This is done via a .vbs file that contains some code to open a hidden instance of excel, once the file is open, the vba within the file takes over.

Always show the user form "On Top" (for whatever reason, when using the .vbs script the user form appears behind everything)
This was done using some API functions that initially show the user form on top until the user interacts with the form, at that point it will be on top due to the interaction.

Only allow one user to open file and tell the second user who has the file open.
When the file is opened, the current user is written to a cell, then the file is saved. When the file is opened, it checks for a read only status and provided feedback as to who is using the file while not allowing the second user to access the file.

Cheers all!

Are the other users opening the excel file via the vbscript as well ?

 

[weslyoc]

Are the other users opening the excel file via the vbscript as well ?

Yes they are. I will try and get samples of my final code up today. Figured it may be useful to some.