Hide workbook/re-size workbook while user form is running.

[Dhamo]

Hi,


I have an xlsm file consisting of an user form. When I open the workbook, the user form will be shown and backside the workbook is displaying by default. (The user form height : 294, width:397.)

Is there any code to hide the workbook? If not, is there any option to re-size the excel workbook and make the user form to hide the workbook.

 

[SirJB7]

Hi, Dhamo!

Tried with this statement?

-----

Me.Hide

-----

You can display it again with this other statement:

-----

Userform1.Show

-----

Regards!

 

[Luke M]

If you want the XL file to be hidden while the UserForm is visible, setup like so:

[pre]

Private Sub Workbook_Open()
Application.Visible = False
UserForm1.Show
End Sub[/pre]
Just remember that however you close the UserForm, you should include code to reshow the workbook like so:

Application.Visible = True

 

[Dhamo]

Thanks SirJB7 and Luke.!


@Luke, Your trick is worked. Thanks. But How to set application.visible=true when user form is closed. I couldn't find any option like "serform1_close".

 

[sgmpatnaik]

@Dhamo


Hi


Please write the code in the UserForm Close Button as


Private Sub CommandButton1_Click()

UserForm1.Hide

Application.Visible = True

End Sub


Thanks


SP

 

[Dhamo]

Thanks sgmpatnaik.!


Initially I do not have close/quit button, I will close the user form by clicking on 'X' mark placed at the top right or CTRL+W (Shortcut key).


@Luke M, just curious to know, if there any possibility to re-size the workbook.?

 

[NARAYANK991]

Hi Dhamo ,


You can have the following :

[pre]

Private Sub Workbook_Open()
With Application
.WindowState = xlNormal
.Width = 397
.Height = 294
End With
UserForm1.Show
End Sub
The restoration of the screen size to the maximized state can be done by the following

Private Sub UserForm_Terminate()
Application.WindowState = xlMaximized
End Sub

[/pre]
Narayan

 

[sgmpatnaik]

@Dhamo


Hi


Then you can use the code as


Private Sub Userform_Terminate()

UserForm1.Hide

Application.Visible = True

End Sub


Thanks


SP

 

[mahaveer]

hey dhamo,

download this file from the following link and enjoy.

http://www.2shared.com/document/gEUr1n5C/form_hides_the_workbook.html


i hope it is useful for you.

Regards

Mahaveer Somani