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....

 

[Logit]

.
Paste this macro in a Routine Module called from ThisWorkbook module ... the Workbook_Open event :

Option Explicit


Sub FormOnly()
    Application.WindowState = xlMinimized
    UserForm1.Show
End Sub

Download workbook : https://www.amazon.com/clouddrive/share/WUvG3t8XHf7nJbbr7bwQVYX2D6poVZqL0bVIVxX0usH

 

[smozgur]

Hi Steve,

In the form's properties window in VBE, set StartUpPosition property to be 2-CenterScreen .

And also show the user form by using the modal parameter as False.

<code>
Public Sub Startup()
Application.WindowState = xlMinimized
frmControl.Show False
End Sub
</code>

 

[sts023]

Logit & smozgur....

Your suggested code is what I started out with, and the problem is that although the Form is shown, because it
a) sets up a default radio button, and
b) is modeless
processing continues as if the User had made the default selection.

Interestingly, the default radio button setting causes a further Form to be displayed, and as this is modal it is visible and waits for a response (and Excel is minimised).

But when the first Form is set to modal, the application is still maximised.

Both Forms have identical relevant attributes - the first one is only set to modeless by the invoking VBA Show statement.

It looked like the Application.WindowState = xlMinimized took a while to get actioned.
So I put a one second delay between the WindowState and the modeless Form Show, and guess what – it still minimised Excel but didn’t show the first Form but DID show the second one!

So, showing a modeless Form allows the application to be minimised, but doesn’t wait for the Form to be actioned, but when a subsequent modal Form is invoked it is presented on screen with Excel minimised.

Any bright ideas?

 

[Jaafar Tribak]

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....

The following shows the userform and only minimizes the application window at least it did when I tested it.

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

 

[sts023]

Jaafar....

Same problem. Looking at the manuals (yes, I've even been reduced to that pitiful state of desperation) the parameter "vbModeless" acts in the same way as "False".

 

[Jaafar Tribak]

Jaafar....

Same problem. Looking at the manuals (yes, I've even been reduced to that pitiful state of desperation) the parameter "vbModeless" acts in the same way as "False".

Sorry, I think I am a bit confused .. Can you explain what problem are you having and what your goal is ?

 

[sts023]

If you read the original post the problem is stated there.
I want to open a workbook and get the workbook minimised whilst showing a Userform.
This is all in the original post.

 

[Jaafar Tribak]

If you read the original post the problem is stated there.
I want to open a workbook and get the workbook minimised whilst showing a Userform.
This is all in the original post.

Is the code I posted not working for you ? If not what happens when you run the code ?

And which excel edition are you using ?

Edit:
And do you need the userform to be modal or modeless ?

 

[sts023]

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.