Minimise the workbook and show a Userform

[sts023]

I'm trying to get Excel to show a Userform (frmControl) at startup, but with Excel minimised.
Having Excel minimised is not a mandatory requirement, but aesthetically it would be nice!

frmControl is a sort of function selector, and a code representing the function eventually requested by the User is placed in the Userforms ".Tag".

I've put this code in ThisWorkbook

Private Sub Workbook_Open()
  Call Startup
End Sub

then in Startup

Public Sub Startup()
  Application.WindowState = xlMinimized
  frmControl.Show
  MsgBox frmControl.Tag
End Sub 'Startup

This just shows the Userform, with the Workbook showing behind it.

If I add "vbModeless" to the Show command, Excel and the Userform are both minimised, and the MsgBox in the minimised Excel is showing the default Tag value.

Am I attempting the impossible, or is it just beyond my puny skills?

I may also run in to problems later, because if I (and by "I" I hopefully mean "you") solve this problem, later on I may need to maximise Excel again, because for example one of the functions on frmControl is to show a specific Worksheet, on which will be a button which when clicked will return to the initial state, i.e. Excel minimised and frmControl showing.

Any help / pointers / suggestions will be thankfully received....

 

[Jaafar Tribak]

All the detail you are asking for has been previously supplied. Your code is functionally identical to that which others have suggested, and which is failing.
I am running Excel as part of Microsoft Office Professional Plus 2010 under Windows 7.

Failing means what exactly ? Is the problem that when you run the code i posted, both the application window AND the userform get minimized ? If you can please confirm that because your post#4 in which you mention radio buttons etc has confused me.


In the meantime, have you tried hiding the excel application instead of minimizing it ?

Something like this maybe:

Sub Startup()
    Application.Visible = False
    frmControl.Show vbModeless
    MsgBox frmControl.Tag
End Sub

And then unhide the application in your userform module :

Private Sub UserForm_Terminate()
    Application.Visible = True
End Sub

Does that make a difference ?

 

[Jaafar Tribak]

See if this API-workaround works for you for minimizing the application window while still displaying the userform and the MsgBox.

Option Explicit

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
    Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr) As Long
    Declare PtrSafe Function IsWindow Lib "user32" (ByVal hwnd As LongPtr) As Long
    Declare PtrSafe Function WindowFromAccessibleObject Lib "oleacc" (ByVal pacc As IAccessible, phwnd As LongPtr) As Long
    Dim hwnd As LongPtr
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
    Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
    Declare Function IsWindow Lib "user32" (ByVal hwnd As Long) As Long
    Declare Function WindowFromAccessibleObject Lib "oleacc" (ByVal pacc As IAccessible, phwnd As Long) As Long
    Dim hwnd As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If


Sub Startup()
    Application.WindowState = xlMinimized
    WindowFromAccessibleObject frmControl, hwnd
    SetTimer Application.hwnd, 0, 0, AddressOf ShowUserForm
    MsgBox frmControl.Tag
End Sub

Sub ShowUserForm()
    On Error Resume Next
        KillTimer Application.hwnd, 0
    On Error GoTo 0
    frmControl.Show vbModeless
    SetTimer Application.hwnd, 0, 0, AddressOf MaximizeExcel
End Sub

Sub MaximizeExcel()
    On Error Resume Next
    If CBool(IsWindow(hwnd)) = False Then
        KillTimer Application.hwnd, 0
        Application.WindowState = xlMaximized
    End If
End Sub

 

[sts023]

Having gone back to basics, I now need to ask what conditions are likely to impair a Workbook being minimised by VBA, then a Userform being displayed?

I have created a new Workbook. It has one Userform, which has one Command Button.
The following code is in the Workbook

Option Explicit
Private Sub Workbook_Open()
  Call Startup
End Sub

The following code is in Module1
Option Explicit

Public Sub Startup()
  Application.WindowState = xlMinimized
  UserForm1.Show vbModeless
End Sub 'Startup

The following code is behind the button

Private Sub CommandButton1_Click()
  Me.Hide
End Sub

When I save the workbook and close Excel, then double click on "Userform Test.xlsm" in Explorer, the Workbook opens minimised, and the Userform is displayed, waiting for the Command button to be clicked.
The Userform is not focused, but is visible, so it seems that all the earlier replies were accurate, but I must have something else going on which muddies the water.
In the real project Workbook several sheets have Protected cells on them.
Also, during start-up there is quite a lot of computation going on in the background before the first Userform is presented to the User.
Does anyone know if there are any known bugs which interfere with application minimisation or a Userform's modeless behaviour?

 

[Jaafar Tribak]

When I save the workbook and close Excel, then double click on "Userform Test.xlsm" in Explorer, the Workbook opens minimised, and the Userform is displayed, waiting for the Command button to be clicked.
The Userform is not focused, but is visible, so it seems that all the earlier replies were accurate, but I must have something else going on which muddies the water.
In the real project Workbook several sheets have Protected cells on them.
Also, during start-up there is quite a lot of computation going on in the background before the first Userform is presented to the User.
Does anyone know if there are any known bugs which interfere with application minimisation or a Userform's modeless behaviour?

I am not aware of any particular bugs and i have this gut feeling that some code somewhere in your project is what is interfering.
It is going to be difficult to find the culprit without seeing the whole code.

If you can upload a workbook example that reproduces the problem you are describing somewhere like in Box.net or other file sharing site and put a link to it here so we can download the workbook and see it ?

In the meantime, would introducing a brief delay as follows make a difference ?

Public Sub Startup()
  Application.WindowState = xlMinimized
  Application.OnTime Now, "ShowFormNow"
End Sub 'Startup


Public Sub ShowFormNow()
  UserForm1.Show vbModeless
  MsgBox UserForm1.Tag
End Sub

 

[sts023]

I've already tried creating a delay. It doesn't work.

For reasons of security and confidentiality i cannot upload the Workbook.

I suspect the problem is caused by other code / settings, so I'll progressively modify the skeleton to match the settings and functionality of the problem Workbook, and when I find the problem I'll re-post to publicise the solution.

Sincere thanks to all who tried to help - thank you for giving freely of your time and experience...