Open workbook as UserForm but workbook hidden

Question

I have created an excel UserForm which I would like to use as the only visible user interface, i.e. the Workbook not visible (or hidden).
In my Workbook_Open() procedure I set the workbook Windows().Visible property to False before showing the Userform. The form is displayed correctly but it still shows a blank (no worksheet) Excel window on screen. It appears that the workbook has not been opened as the code behind the userform controls give an error 'Calculation' of object '_Application' failed' at statements like Application.Calculation.

I presumed Thisworkbook.Activate would Open the workbook. I need this to work at Windows level hiding the window from the user, rather than Application so as not to affect other workbooks open at the same time.

'-------------------------
Public Sub Workbook_Open()

Set UIwb = ThisWorkbook
Application.ScreenUpdating = False
UIwb.Activate
UIwb.Windows(1).Visible = False
Application.ScreenUpdating = True

'Show Splash Screen (Form)
SplashScreen.Show vbModeless

' Instantiate a new UI Object
Set PT_UI = New ConfigUI
' Clear Global status flags
RPT_STATUS = NO_DATA

End Sub  ' Workbook_Open()
'=========================

Can anyone please suggest how to achieve a windowless user interface with VBA?

Possible duplicate of Minimize workbook/sheet but keep form opened — HackSlash
You're hiding the workbook's child window... you want to hide the Application's main window. — Mathieu Guindon
The ideal way to do this is as an Add-in, rather than a Workbook. — David Zemens
@MathieuGuindon How do I differentiate between this application's window and any other Excel apps already open? If I try Application.Visible = False it hides all open instances of Excel. — NigelH
You should ask a new/separate question describing the errors you're getting when using the XLAM. I'll try to keep an eye out for that Q and answer if/when I can. Going from memory here, you don't need to use any of the code to "hide" or "minimize" the workbook if it's loaded as an Add-in, it should be hidden by default and it will not have a workbook window exposed to the user. — David Zemens

André Almeida André Almeida · Accepted Answer · 2019-09-30T17:32:40

try to put this on Thisworkbook code:

If you lunch the app the workbook will minimize, other users will not see the workbook (because is hidden):

Sub AutoOpen()

If (Environ$("Username") = "windowsUser" Then

ThisWorkbook.ActiveWindow.WindowState = wdWindowStateMinimize

Else
ThisWorkbook.Windows(1).Visible = False

End If
End Sub

And then put this in Userform code - This will run when userform initialize:

Public Declare PtrSafe 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
Public Const HWND_TOPMOST = -1
Public Const HWND_NOTOPMOST = -2
Public Const SWP_NOMOVE = &H2
Public Const SWP_NOSIZE = &H1

Public Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Sub UserForm_Initialize()
Dim bwind As Long

Load ME
ME.Show vbModeless

If ME.Visible = True Then
bwind = FindWindow(vbNullString, ME.Caption)
Else
bwind = 0
End If
SetWindowPos bwind, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE
SetWindowPos bwind, HWND_NOTOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE
End Sub

Open workbook as UserForm but workbook hidden

1 Answers