CODEWITHSUNDEEP
excelvba
NigelH
NigelH

Reputation: 109

Open workbook as UserForm but workbook hidden

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?

Upvotes: 0

Views: 878

Answers (1)

André Almeida
André Almeida

Reputation: 1

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

Upvotes: 0

Related Questions