How to Run UserForm without Showing Excel Window

Video Statistics and Information

Video
Captions Word Cloud
Reddit Comments
Captions
[Music] hello everyone welcome back you're watching the retail apps my name is lip so in this tutorial we are going to learn how to open user form without showing the actual application in background so let's understand the problem area first and then we will try to find out the solution with the help of excel settings and visual basic application code so here you can see that in this particular folder we have inventory management system and if you'll open this file first of all this excel application will open and then it will ask us to enable this content once you click on enable content and then you will click on the start then it will run this the inventory management system and while running this form user form you can see that excel application is running you know behind this user form so what we need to do uh we need to find the solution so that whenever we click on this this icon basically inventory management system it will directly open this inventory management user form and the excel application should be hidden in background basically yeah so to do that like just let's click on close icon here this one and then move to the developer so in developer tab under code group we just need to do some settings that is the macro related settings so just click on macro setting here and once you click on macro setting uh the trust center this window will appear on your screen in this trust center we need to do some macro uh macro settings basically we need to change some macro setting so here in the macro settings you can see that by default disable all macros with notification has been selected it means whenever you will run any macro enable file having macro file then it will prompt a message to enable the macro and by default the macro will be disabled so in case if you are not enabling the macro it will not run the macro code that is a visual basic application code okay so the problem is like to hide this excel application we uh we should run the macro first and then we can trigger it too so that the visual basic can code can hide the excel application and show only the user form but in this case that will not be possible because excel will run first and it will ask us to enable the macro so this here uh we just need to make some changes here that is the we need to select this last option that is enable all macros uh not recommended potentially dangerous code can run okay so if you select this uh this setting then excel application will never ask us to enable the macro and it will run all the macro enable files automatically while opening the excel yeah but here i would recommend you to select this option only if you are aware that all the macros files which are available in your machine are safe to run and that will not going to harm your system or any other data if you think that uh there could be some macro in a mac profile which cannot be trusted then please avoid the settings otherwise you know it will create a lot of issue maybe that would be dangerous for your system and maybe that would be uh some virus okay so considering uh considering the security issue i'm just going to select this uh disable macro all macro with notification if you are 100 confident that okay uh you have whatever the macro file which you have that is you know completely trusted basically then you can go with that and it will solve our problem the second alternate problem like while keeping this disabled all macros with notification that is trusted location so in trusted location we just need to add a folder and you we will keep all the macros file in that folder then excel will treat all the macros or all the files which are available in that particular folder are 100 safe and trusted so while running the macro file from that folder it will never ask us to enable the macro and it will directly run the macro code okay so let's uh click on add new uh location here and once you click on add new location it will prompt you to add you know a folder name so i have already created a folder in my shared uh in my local drive so let me copy this copy this let's move to the excel file and i'm just going to add this here paste okay now click on ok so you can see that one folder has been added that is d and the data labs and trusted application that is this this folder okay so let's come to the excel file here and click on ok so in trusted location uh we have added a folder now we are done with the macro settings basis on our requirement so the next step is to write some visual basic application code on workbook open and user form query close event in visual basic application so let's move to the visual basic application window and start writing the required code so to open the vba window you just need to click on the developer tab again and under code group you will find the visual basic button here you just need to click on that to open the visual basic window or alternatively you can also press alt plus f11 as a shortcut key to open the visual basic editor so let's click on that and once you click on that you can see that we are in microsoft visual basic for application inventory management system so here we are not going to learn about the inventory management system and you will not find any code related within an inventory management system we have utilized only the form to show how to run the form without showing the excel application in behind yeah so let's click on this and uh put the password so the password is the data labs so t h e d a and click on ok now uh just expand this the microsoft excel object so now we need to write the code on this workbook so let's come to this workbook code window and from general here we need to select this workbook and by default it will uh you know create a sub procedure that is related with workbook underscore open event so in this sub procedure we need to write the code so the code would be uh application dot visible is equal to false so what we are doing we are hiding this excel application first and then we are going to run this frm inventory dot show so let me explain this form here if you look at this the frame inventory so this inventory we are this form we are going to show here so these two line uh line of code will work and it will help us in hiding the excel application first and it will help us in showing the user form after that so in that way excel application will run in the background only and user will not be able to see the excel application okay now we need to write one more lineup of like code here on frame like frame inventory so just double click on that so let's double click on this uh form and you can see that we are in the code window for this frm in inventory so by default here one procedure has been added that is user form underscore click but we do not need to write the code on click event so let's come here and let's select this query close uh events so query close events now you can see that one sub procedure has been inserted for very close delete the previous one and here we need to write the code so now the logic is like suppose user will click on the close icon so what it will do just because this window is you know showing so before closing this application this window uh the user form we need to unhide this on the excel application and then we will close this uh this form so let's come to the again here and now we need to write the code so first of all we are going to show application dot visible is equal to true so i think we have some spelling mistake okay and now uh we need to unload this uh frm inventory form so unload me okay we are done let's go to the debug and compile so you can see that we don't have any error in the code okay so now we are done with all the settings and visual basic application code which are required to run the user form without showing the actual application so let's uh save this file and move to the excel window here and now let's close this file and again go to the same folder where we have saved this file so remember we are not going to run this file from the trusted location so still the settings is you know disable a macro by default with notification so let's check it i'm just clicking on it so here you can notice that excel is still asking to enable the macro and the reason is we have selected macro settings as it disable all macros with notification also we are not opening this file from this folder like this trusted application folder because we have added this folder as a trusted location and that's the reason it's prompting to enable this macros let's close this file and move to the previous folder that is this one let's copy this application yeah move to the excel file uh sorry folder and in trusted application just uh paste it now this file is in on trusted application folder so let's run this so you can see that uh currently it's showing only this inventory management system the user form and even if you look at this taskbar there is no excel application running so excel application is completely hidden and only the form is showing in case if you will close this button then it will close this form and open or no unhide the excel application if you want to validate whether excel application is running in on a background or not you can go and open the taskbar task manager here just press ctrl alt delete and task manager and under task manager you can see that like one this excel is running okay so let's close this task manager and again let's close this the form as soon as you click on the close form it will unhide the excel application that is running in a background now you can see that it has started showing the actual application and the form is closed now so this is all about how to run the user form without showing the excel file hope you find this tutorial useful if you have not subscribed our youtube channel then please subscribe it also hit the bell icon so that you can get the notification whenever we upload a tutorial on excel visual basic application and power bi i would also request you to like and share and comment thanks for watching have a great day bye
Info
Channel: TheDataLabs
Views: 105,191
Rating: undefined out of 5
Keywords: Run UserForm without showing Excel window, Hiding Excel window, Show UserForm only in Excel, VBA code to hide the Excel window, Macro Security in Excel, Trusted Location in VBA and Excel
Id: eX4Jar4Gz6w
Channel Id: undefined
Length: 12min 44sec (764 seconds)
Published: Sat May 07 2022
Related Videos
Note
Please note that this website is currently a work in progress! Lots of interesting data and statistics to come.