Often the Userform we generate is quite big and contains a lot of information in a small place. Having all of them in a crammed place can hamper the clarity of the Userform. If you are curious to know how you can show Userform in full screen in Excel using VBA, then this article may come in handy for you. In this article, we discuss how you can show Userform on full screen in Excel using VBA with an elaborate explanation.
How to Show Userform in Full Screen Using VBA in Excel (4 Easy Ways)
We are going to use the below dataset to demonstrate how you can show Userform in full screen using Excel VBA. The file must be saved in xlsm format instead of the regular xlsx format.
1. Using xlMaximized
We are going to use the below VBA code which is going to show the Userform in full screen without any hassle.
Steps
- To begin, click on the Developer tab and then select Visual Basic.
- Once you have opened the Visual Basic window, click on Insert.
- In the next step, select Userform from the drop-down menu.
- A rectangular window box will appear after that.
- On the Properties pane, you can rename and caption that window.
- This is the box for using Userform, you can add different shapes, buttons, checkboxes, etc., and link them with the sheet.
- Then double click on that box.
- You will notice that there is an editor window. In that editor window, paste the below code.
Private Sub UserForm_Activate()
Application.WindowState = xlMaximized
With Application
Me.Top = .Top
Me.Left = .Left
Me.Height = .Height
Me.Width = .Width
End With
End Sub
- As soon as you have entered the code, click the Save icon and then click twice on the Userform.
- It will take you to the main Userform box again.
- Now for illustration purposes, add some elements to the Userform.
- We added some Text Boxes and some Command Buttons, with an input box.
- Then click on the Run button shown in the image.
- Clicking the Run command, we can see the Userform.
- After the Userform is executed, we can see that the Userform is now occupying the full portion of the screen. As shown in the image below.
🔎 VBA Code Breakdown
- First, we choose the Userform as the object and the Activate as the procedure.
- In the next line, WindowState will denote the window condition of the application that is running right now. Setting the condition of this command line as xlMaximized will maximize the application’s window.
- In the next line, we select the application window.
- Then we input 4 separate lines, all of which will make the window go maximize in the top, down, left, and right directions.
- Finally, end the sub-procedure of the code.
2. Adjusting Height and Width
We are going to use the below VBA code which is going to show the Userform in full screen without any hassle.
Steps
- To begin with, click on the Developer tab and then select Visual Basic.
- Once you have opened the Visual Basic window, click on Insert.
- In the next step, select Userform from the drop-down menu.
- A rectangular window box will appear after that.
- On the Properties pane, you can rename and caption that window.
- This is the box for using Userform, you can add different shapes, buttons, checkboxes, etc., and link them with the sheet.
- Then double click on that box.
- You will notice that there is an editor window. In that editor window, paste the below code.
Private Sub UserForm_Activate()
Me.Height = Application.Height
Me.Width = Application.Width
Me.Left = Application.Left
Me.Top = Application.Top
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_Initialize()
Application.WindowState = xlMaximized
End Sub
- As soon as you have entered the code, click the Save icon and then click twice on the Userform.
- It will take you to the main Userform box again.
- Now for illustration purposes, add some elements in the Userform window, we added some Text Boxes and some Command Buttons, with an input box.
- Then click on the Run button shown in the image.
- Clicking the Run command, we can see the Userform.
- After the Userform is executed, we can see that the Userform is now occupying the full portion of the screen. As shown in the image below.
🔎 VBA Code Breakdown
- First, we choose the Userform as the object and the Activate as the procedure.
- In the next 4 lines, will take the height, width left, and right dimensions of the window and store them as the Height, Me.Width, Me.Left, Me.Top.
- We then end the sub-procedure of the code.
- First, we choose the Userform as the object and the Initialize as the procedure.
- Then the next line will maximize the stored value of the window in all directions which will result in full-screen maximization of the window, all of which will make the window go maximize in the top, down, left, and right directions.
- Finally, we end the sub-procedure of the code.
3. Using With Statement
The VBA code shown below will transform the small Userform window into a full-screen window.
Steps
- To begin, click on the Developer tab and then select Visual Basic.
- Once you have opened the Visual Basic window, click on Insert.
- In the next step, select Userform from the drop-down menu.
- A rectangular window box will appear after that.
- On the Properties pane, you can rename and caption that window.
- This is the box for using Userform, you can add different shapes, buttons, checkboxes, etc., and link them with the sheet.
- Then double click on that box.
- You will notice that there is an editor window. In that editor window, paste the below code.
Private Sub UserForm_Initialize()
   With Fullscreen_form_3
       .Height = Application.Height
       .Width = Application.Width
   End With
End Sub
- As soon as you have entered the code, click the Save icon and then click twice on the Userform.
- It will take you to the main Userform box again.
- Now for illustration purposes, add some elements to the Userform.
- We added some Text Boxes and some Command Buttons, with an input box.
- Then click on the Run button shown in the image.
- Clicking the Run command, we can see the Userform.
- After the Userform is executed, we can see that the Userform is now occupying the full portion of the screen. As shown in the image.
🔎 VBA Code Breakdown
- First, we choose the Userform as the object and the Initialize as the procedure.
- We will select the Userform application window in the next line.
- We then saved the height and width of the application in the .Height and .Width
- Finally, we end the sub-procedure of the code.
4. Using Variable
The following VBA code expands the small Userform panel to occupy the full screen.
Steps
- To begin, click on the Developer tab and then select Visual Basic.
- Once you have opened the Visual Basic window, click on Insert.
- In the next step, select Userform from the drop-down menu.
- A rectangular window box will appear after that.
- On the Properties pane, you can rename and caption that window.
- This is the box for using Userform, you can add different shapes, buttons, checkboxes, etc, and link them with the sheet.
- Then double click on that box.
- You will notice that there is an editor window. In that editor window, paste the below code.
Private Sub UserForm_Initialize()
Dim xlws As XlWindowState
xlws = Application.WindowState
Application.WindowState = xlMaximized
With Fullscreen_form_4
   .Top = Application.Top
   .Left = Application.Left
   .Width = Application.Width
   .Height = Application.Height
   .Show
End With
Application.WindowState = xlws
End Sub
- As soon as you have entered the code, click the Save icon and then click twice on the Userform.
- It will take you to the main Userform box again.
- Now for illustration purposes, add some elements to the Userform.
- We added some Text Boxes and some Command Buttons, with an input box.
- Then click on the Run button shown in the image.
- Clicking the Run command, we can see the Userform executing.
- After the Userform is executed, we can see that the Userform is now occupying the full portion of the screen. As shown in the image.
🔎 VBA Code Breakdown
- First, we choose the Userform as the object and the Initialize as the procedure.
- In the next line, we declare xlws as the Variable for the XlWindowState type.
- In the next line, WindowState will denote the window condition of the application which is running right now. And we put xlws as the present window condition.
- Setting the condition of the WindowState command line as xlMaximized will maximize the application’s window.
- In the next line, we select the application window.
- Then we input 4 separate lines, all of which will make the window go maximize in the top, down, left, and right directions.
- Finally, end the sub-procedure of the code.
Read More: How to Create a UserForm: an Overview
Download Practice Workbook
Download this practice workbook below.
Conclusion
To sum it up, the issue of how we can show Userform to full screen is answered here by 4 different VBA Macro code examples. Although the codes are pretty time efficient the VBA Macro method requires prior VBA-related knowledge to understand from scratch.
For this problem, a macro-enabled workbook is attached where you can practice these methods.
Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciated.
Related Articles
- Excel VBA: UserForm Image from Worksheet
- How to Create Toggle Button on Excel VBA UserForm
- How to Use Excel UserForm as Date Picker
- Excel VBA: Print UserForm to Fit on a Page
- How to Use VBA to Get Value from Userform Textbox in Excel
- Excel VBA to Format Textbox Number with UserForm
