Excel VBA: Show Userform in Full Screen (4 Easy Ways)

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.


Download Practice Workbook

Download this practice workbook below.


4 Easy Ways to Show Userform in Full Screen Using VBA in Excel

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.

Show Userform in Full Screen


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.

Using xlMaximized to Show Userform in Full Screen

  • 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.
  • And this is the box for using Userform, you can add different shapes, buttons, checkboxes, etc, and link them with the sheet.

Using xlMaximized to Show Userform in Full Screen

  • Then double click on that box.
  • And 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 in 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.

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

🔎 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 which 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.
  • And 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.
  • And 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.

Adjusting Height and Width to Show Userform in Full Screen

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

Using With Statement to Show Userform in Full Screen

  • 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

Using With Statement to Show Userform in Full Screen

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

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.
  • And 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.
  • And 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 in 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.

Using With Statement to Show Userform in Full Screen

  • 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

Using With Statement to Show Userform in Full Screen

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

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 appreciable

Rubayed Razib Suprov
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo