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.

 

[weslyoc]

Well I have seemed to have fixed my own problem.

The problem was in the following line of code:

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

The text in RED is actually the userform caption and not the title... derp

Everything works great now, I have a VBScript that opens my excel file and never shows excel, the user form will initially display on top of all the windows, but as soon as you interact with it, it reverts to the normal state.

 

[weslyoc]

I may have spoken too soon. In putting the program through some testing I discovered a big problem with the way the vbscript opens the file.

When this vbscript runs:
---------------------------------
Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Visible = False
objExcel.Workbooks.Open("K:\PFT\Project Number Generator - Final.xlsm")
----------------------------------------------------------
The file is opened in a new instance, which is fine because I don't want any of the Application.Visible = False code to affect other files that may be open at the time of running the script.

If I open the file by clicking on the file and then someone else tries to open the same file (shared on a server) they get the locked for editing warning. This is good! I only want one user at a time.

Now when the file is opened with the vbscript, and then another user tries to open that same file, they do not get the locked for editing warning. they can open the file and use it. Only when they go to save it do they get the warning about this file being read only.

Question: Is there any way to have the vbscript open the file in the exact same way as clicking on the file would so they the file becomes locked for editing to other users?

 

[WassimN]

weslyoc,

I do not see anything that looks like userform.show. This is the usual way to display a userform.

You mention VBSCript, and I think you mean VBA but I may be missing something.

 

[WassimN]

weslyoc,

Sorry I forgot to mention that objExcel.Application.Visisble = False might also be a concern.

 

[Jaafar Tribak]

I may have spoken too soon. In putting the program through some testing I discovered a big problem with the way the vbscript opens the file.

When this vbscript runs:
---------------------------------
Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Visible = False
objExcel.Workbooks.Open("K:\PFT\Project Number Generator - Final.xlsm")
----------------------------------------------------------
The file is opened in a new instance, which is fine because I don't want any of the Application.Visible = False code to affect other files that may be open at the time of running the script.

If I open the file by clicking on the file and then someone else tries to open the same file (shared on a server) they get the locked for editing warning. This is good! I only want one user at a time.

Now when the file is opened with the vbscript, and then another user tries to open that same file, they do not get the locked for editing warning. they can open the file and use it. Only when they go to save it do they get the warning about this file being read only.

Question: Is there any way to have the vbscript open the file in the exact same way as clicking on the file would so they the file becomes locked for editing to other users?

You could check if the workbook is already open before the code tries to open it .

Add this little function to your vb module :

Function IsFileOpen(filename As String) As Boolean
    Dim filenum As Integer, errnum As Integer
 
    On Error Resume Next
        filenum = FreeFile()
        Open filename For Input Lock Read As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=filenum"]#filenum[/URL] 
        Close filenum
        errnum = Err
    On Error GoTo 0
 
    Select Case errnum
        Case 0
         IsFileOpen = False
        Case 70
            IsFileOpen = True
        Case Else
            Error errnum
    End Select
 
End Function

And then amend your existing code as follows :

    If Not IsFileOpen("K:\PFT\Project Number Generator - Final.xlsm") Then
        Set objExcel = CreateObject("Excel.Application")
        objExcel.Application.Visible = False
        objExcel.Workbooks.Open ("K:\PFT\Project Number Generator - Final.xlsm")
    Else
        MsgBox "File In Use."
        Exit Sub
    End If

 

[weslyoc]

weslyoc,

I do not see anything that looks like userform.show. This is the usual way to display a userform.

You mention VBSCript, and I think you mean VBA but I may be missing something.

"UF1.Show vbModeless" is what you are looking for.

Yes, a ,vbs file is a vbscript file. Create a text file, add some code then save as .vbs. Are you familiar with this? This is what allows me to open the file with eh application hidden and never see the "flash" of the excel application. This is not part of excel, but its the only way to get around the ever annoying "flash".

weslyoc,

Sorry I forgot to mention that objExcel.Application.Visisble = False might also be a concern.

The issues with showing the form "on top" have been resolved already. Thanks anyways though.

 

[weslyoc]

You could check if the workbook is already open before the code tries to open it .

Add this little function to your vb module :

Function IsFileOpen(filename As String) As Boolean
    Dim filenum As Integer, errnum As Integer
 
    On Error Resume Next
        filenum = FreeFile()
        Open filename For Input Lock Read As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=filenum"]#filenum[/URL] 
        Close filenum
        errnum = Err
    On Error GoTo 0
 
    Select Case errnum
        Case 0
         IsFileOpen = False
        Case 70
            IsFileOpen = True
        Case Else
            Error errnum
    End Select
 
End Function

And then amend your existing code as follows :

    If Not IsFileOpen("K:\PFT\Project Number Generator - Final.xlsm") Then
        Set objExcel = CreateObject("Excel.Application")
        objExcel.Application.Visible = False
        objExcel.Workbooks.Open ("K:\PFT\Project Number Generator - Final.xlsm")
    Else
        MsgBox "File In Use."
        Exit Sub
    End If

Just to be clear here, I am using a .vbs file to open my excel file. So what i refer to as vbscript is not vba code within excel.
I will give this a try...

 

[Jaafar Tribak]

Just to be clear here, I am using a .vbs file to open my excel file. So what i refer to as vbscript is not vba code within excel.
I will give this a try...

You can use this VBS :

Const ForAppending = 8

If Not IsFileOpen("K:\PFT\Project Number Generator - Final.xlsm") Then
   Set objExcel = CreateObject("Excel.Application")
   objExcel.Application.Visible = FALSE
   objExcel.Workbooks.Open ("K:\PFT\Project Number Generator - Final.xlsm")
Else
   MsgBox "File In Use."
End If


Function IsFileOpen(filename)
    Set oFS = CreateObject( "Scripting.FileSystemObject" )
    On Error Resume Next
    Set oFile = oFS.OpenTextFile(filename, ForAppending, True )
    errnum = Err
    Select Case errnum
       Case 0
          IsFileOpen = False
       Case 70
          IsFileOpen = True
       Case Else
           Error errnum
    End Select
    Set oFS = Nothing
    Set oFile = Nothing
End Function


WScript.Quit

 

[weslyoc]

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!