0

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?

NigelH
  • 89
  • 8
  • Possible duplicate of [Minimize workbook/sheet but keep form opened](https://stackoverflow.com/questions/40679441/minimize-workbook-sheet-but-keep-form-opened) – HackSlash Sep 30 '19 at 17:19
  • You're hiding the workbook's child window... you want to hide the `Application`'s main window. – Mathieu Guindon Sep 30 '19 at 17:21
  • The ideal way to do this is as an Add-in, rather than a Workbook. – David Zemens Sep 30 '19 at 17:35
  • @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 Sep 30 '19 at 19:08
  • @DavidZemens Yes I would like to learn how to make this an Add-in, please can you offer any learning links? I tried doing a quick convert the file to.xlam and add to the list of Add-ins but I guess I need to design my code better for this purpose as I was getting new errors, – NigelH Sep 30 '19 at 19:12
  • 1
    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 Sep 30 '19 at 19:27
  • If `Application.Visible = False` hides "all open instances of Excel", then you don't have "multiple instances of Excel", just multiple windows running under the same host process – Mathieu Guindon Sep 30 '19 at 19:50
  • @MathieuGuindon Forgive by poor use use of intances, I should have said workbooks. Your help prompted me to try ```Thisbook.Application.Vsible=Flse``` and that worked, thanks. – NigelH Sep 30 '19 at 19:54
  • @DavidZemens I'll try again with the Add-in following your suggestions thanks. – NigelH Sep 30 '19 at 19:55

1 Answers1

0

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
André Almeida
  • 1