Run UserForm without showing the Excel Application

In this blog, we are going to learn Excel & VBA tips to run UserForm without showing the Excel application.

This is going to be very interesting hence, read the complete blog and apply the techniques in your VBA project to make it more effective and distraction free.

Watch our YouTube Tutorial.

Let’s start with problem area first and then we will go with solutions step-by-step.

Problem and Requirement

Suppose, I am trying to run the Inventory Management System developed in Excel and VBA. Once, the file will get opened then first of all, Excel application will run and then it will ask to enable the macro.

While running the Inventory Management System UserForm, we can see Excel application is also running behind the UserForm.

Here, we just want to hide the Excel Application when we run Inventory Management UserForm. It means, if we double click on Inventory Management System in folder then it would run the UserForm directly and hide the Excel Application in background so that user can’t see the Excel. Once, user will click on close button in UserForm then it will close the UserForm and show the Excel Application.

Steps to Show UserForm only

To perform this activity, we need to make some changes in Trust Center of VBA. So let’s go to the Developer Tab and then Click Macro Security in Code Group.

Here, we need to change the Macro settings.

Currently, you can see that ‘Disable All Macros with Notification’ is set. It means Excel will always prompt to enable the Macro and in that case, Excel application will always be visible until Macro will not run.

So, here in Macro Settings, you can select the Last Option ‘Enable All Macros (not recommended; potentially dangerous code run)’.

If you select this setting, then Excel will never ask to enable the Macro and it will run it automatically while opening the Excel application.

Recommendation:

Here, we would recommend you to select this option only, if you are aware that all the macros available in your machine are safe to run and that will not harm your system and data. If you think that there could be some macro files which can’t be trusted, then avoid this setting.

So considering security issue, we are going to keep the second option ‘Disable All Macros with Notification’ selected in our machine. If you do see any issue, then you can go with ‘Enable All Macros (not recommended; potentially dangerous code run)’.

The next alternate and safe option to run the Macro without getting pop-up to enable is to add a folder in ‘Trusted Locations’ and keep the Macro file there.

In Trusted Location, you can add a folder and Excel will treat all the files available in that folder as trusted and safe to run. Once you add the folder, you just need to keep your Macro files in that folder and run the same from that location only.

So let’s quickly add a Folder location here after clicking on ‘Add New Location…’ button.

I am adding ‘D:\TheDataLabs\Trusted Application\’ here and will move the Excel file here.

Now we are done with Macro Security settings basis our requirement. The next step is to write some VBA codes on Workbook Open and UserForm QueryClose event in VBA.

VBA Code to Run the Form and Hide Excel Application

Let’s move to VBA window and start writing the required codes.

To open the VBA window, click on Developer Tab and under Code Group, click on Visual Basic Button. Alternatively, you can also press short cut ALT + F11 to open the VBA window.

Once, we click on Visual Basic Button then we will jump to Visual Basic for Application window.

Now, we need to write the code on Workbook open event so that when workbook will be opened then all the codes will get fired automatically.

Write the below code in workbook open event.

Private Sub Workbook_Open()
    Application.Visible = False
    frmInventory.Show
End Sub

This code will help us in hiding the Excel application and show the UserForm only.

Now, let’s move UserForm and write the VBA codes to unhide the make the Excel application visible and close the form.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Application.Visible = True
    Unload Me
End Sub

If user will click on close button in form, then these code will unhide the Excel application that is running in background and then close the UserForm.

Now, we are done with all settings and VBA code required to run the UserForm without showing the Excel Application.

Let’s save this file and close it for testing purpose.

Now, run the same file after double clicking on icon in folder (the folder which has not bee added as trusted).

Here, you can notice that Excel is still asking to enable the Macro and the reason is we have selected Macro Settings as ‘Disable All Macros with Notification’ and also, we are not opening this file from Trusted Folder which we added in Macro security.

So, let’s close this file again and copy the file and paste in into Trusted Folder.

Now, run the file from this folder only.

Here, you can see that only UserForm is showing and Excel is running in background only.

If you want to see whether Excel is running in Background or not, then you can open the Task Manager and see the same in Process Tab.

Let’s close the Task Manager and come to UserForm. Let’s click on Close button available in UserForm.

Now, Excel is visible and UserForm has been closed.

So, this is all about how to run the UserForm without showing the Excel file. Hope, you find this blog useful.

If you have any queries or feedback then please post your comment here. Thanks!

Watch YouTube tutorial – How to run UserForm in Full Screen mode

Please download the file with Full Screen code. Password is thedatalabs