Receive new articles directly in your inbox!! Register your email address

How to build a Full Screen User Form in Excel with Hidden Elements

In this article , I’ll guide you through the process of creating a VBA-powered Excel workbook that hides all interface elements and displays only a UserForm when opened. This UserForm will have two buttons that trigger different macros.

By the end of this tutorial, you will have a professional-looking Excel application that feels more like a custom program than a spreadsheet.

This solution is useful when you want to leverage all the capabilities of excel and at the same time ensure that user activity is limited exclusively to the actions you want to be performed

Hiding the Excel interface on Workbook.Open

Creating and Designing the User Form

Writing the Macros to be triggered by the buttons

Conclusion

Hiding the Excel interface on Workbook.Open

The first step is to hide all Excel interface elements, including the ribbon, formula bar, gridlines, and any sheets.

We’ll do this in the Workbook_Open event, which runs automatically when the workbook is opened.

Private Sub Workbook_Open()

' Hide Excel interface

Application.ScreenUpdating = False

Application.DisplayFullScreen = True

Application.CommandBars("Ribbon").Visible = False

Application.DisplayFormulaBar = False

ActiveWindow.DisplayWorkbookTabs = False

ActiveWindow.DisplayHeadings = False

ActiveWindow.DisplayGridlines = False

' Show the UserForm

UserForm1.Show

Application.ScreenUpdating = True

End Sub

Explanation:

Application.ScreenUpdating = False: This prevents the screen from flashing while changes are being made.
Application.DisplayFullScreen = True: Puts Excel in full-screen mode, hiding the title bar and ribbon.
Application.CommandBars("Ribbon").Visible = False: Hides the Excel ribbon.
Application.DisplayFormulaBar = False: Hides the formula bar.
ActiveWindow.DisplayWorkbookTabs = False: Hides the sheet tabs at the bottom.
ActiveWindow.DisplayHeadings = False: Hides row and column headers.
ActiveWindow.DisplayGridlines = False: Hides the gridlines in the worksheet.
UserForm1.Show: Displays the UserForm.
Application.ScreenUpdating = True: Re-enables screen updating after changes.

Creating and Designing the User Form

Next, we’ll create a UserForm with three buttons. Each button will trigger a different macro. This UserForm will serve as the primary interface for your users.

Code:

Add the UserForm and Name It UserForm1:
- Create three buttons on the form, naming them CommandButton1 and CommandButton2
- Set the captions for the buttons to "Run Macro 1", and "Run Macro 2".

Private Sub CommandButton1_Click()

Call Macro1

End Sub

Private Sub CommandButton2_Click()

Call Macro2

End Sub

CommandButton1_Click, CommandButton2_Click: These are the event handlers that run when each button is clicked.

Call Macro1, Call Macro2: Each button triggers a different macro. You will define these macros in the next section.

Writing the Macros to be triggered by the buttons

Now, let’s define the three macros that will be executed when the corresponding buttons are clicked.

Sub Macro1()

MsgBox "Macro 1 is running!"

End Sub

Sub Macro2()

MsgBox "Macro 2 is running!"

End Sub

Sub Macro1, Sub Macro2: These are the three separate macros that can be customized to perform any task you desire.

MsgBox: Displays a simple message box. In a real application, you would replace this with the actual code for your macros.

Conclusion

With this setup, you've successfully created a seamless Excel application that feels more like a standalone program.

By hiding the Excel interface and using a UserForm as the primary interaction point, you can control exactly how your users interact with your data.

Additionally, the two buttons in the UserForm allow you to easily trigger different macros, adding functionality to your form.

I have called the two buttons Import and Export. When I open the workbook this is what it looks like

Page updated

Google Sites

Report abuse