Minimise the workbook and show a Userform

sts023

Board Regular
Joined
Sep 1, 2008
Messages
106
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
Code:
Private Sub Workbook_Open()
  Call Startup
End Sub

then in Startup
Code:
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....
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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>
 
Upvote 0
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?
 
Upvote 0
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
Code:
Private Sub Workbook_Open()
  Call Startup
End Sub

then in Startup
Code:
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.

Code:
Public Sub Startup()
  Application.WindowState = xlMinimized
  frmControl.Show vbModeless
  MsgBox frmControl.Tag
End Sub 'Startup
 
Upvote 0
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".
 
Upvote 0
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 ?
 
Upvote 0
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.
 
Upvote 0
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 ?
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top