Как удалить userform в vba excel

Delete a UserForm and add a New UserForm in Excel VBA

What I would like to do is Delete a Userform and replace it with an updated Userform using VBA for Excel.

I exported the original Userform, made my changes, and now would like to replace the original with the new Userform. Both Userforms have the same names. I’m hoping, by keeping the UserForm names the same the button that activates the Userform won’t be damaged.

I have am going to have the VBA loop through all the workbooks and do this

VBA Close UserForm

By Madhuri Thakur

Excel VBA Close UserForm

Earlier we had discussed how to create a UserForm in VBA. Now to get input from the user there was a button for submit, which stored then the value given by the user in an excel sheet. But what after giving the value the form is still there. Or what if the user does not want to give any data and close the userform. We had not discussed how we will close the userform for the user. Similar to submit button to submit the data we had another button which was for cancel which is used to hide the userform. The cancel button also has its own code to hide the userform from the user. Now there are two methods by which we can hide a userform. They are as follows:

1. The first method is when we use the Unload Me method. This method unloads the UserForm and it disappears from the display.
2. Another method is when we use Useform.Hide method. This method hides the UserForm from the display.

It is recommended to use the second method to close the UserForm as the first method will completely unload the UserForm and any data entered by the user will be completely lost. But when we use the second method of hiding the UserForm, then the last entry done on the userform by the user is still present.

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

How to Close UserForm in Excel VBA?

We will learn about how to close UserForm in Excel VBA with its difference and by a few examples but first, let us learn the method of closing a userform in VBA.

There are two methods to close userform in VBA:

1. Unload Me
2. Hide

Excel VBA Close UserForm – Example #1

First, let us try the method of closing a UserForm using the Unload Me option. We had a userform in our previous article which looks like the one below.

For the submit button we had our code, which stored the data entered by the user in excel. Now double click on the cancel button which will open the code for the same as follows,

Code:

Cancel Button was the second command button we provided to the userform. Now write the code statement as Unload me in the section as shown in the image below.

Code:

We can see that Me is an Object of Unload statement. Now let us run the userform by pressing the F5 key to display it.

Press Cancel button to see that the userform disappears and it takes us back to the original userform of the project window.

Excel VBA Close UserForm – Example #2

Now let us try the second method which userform.hide method in a similar fashion as above, first, let us open the userform from the userform in the project window.

Now double click the cancel button which will open the view code for the cancel command button.

We already have Unload Me statement present in the cancel command button, clear the code and replace it with the Userform.Hide statement.

Code:

Now let us again run the userform by pressing F5 key and display it on the screen.

When we press the cancel button we are again moved to the project window of the userform. Now we all might be wondering that what is the difference between the two methods, as both the methods close the userform for us perfectly then why is the second method most recommended method of both. We will find out exactly why in the next two examples.

Excel VBA Close UserForm – Example #3

Now let us move to the first method Unload Me statement for the cancel button and provide some data to the userform. Double click on the cancel button from the userform to open the view code for the command button and replace the code with the Unload Me statement as follows.

Code:

Now run the userform again by pressing the F5 key and make it display on the screen.

Let us provide some data to the userform as follows, now don’t press the submit button, press the Cancel button.

Again run the userform by pressing F5 key.

Now we can see that we have lost the data we have provided to the userform since we did not submit it, it was not stored in the worksheet and we closed the userform using the Unload Me statement which removed the data which was already filled.

Excel VBA Close UserForm – Example #4

Now let us do the same exercise but by using Userform.Hide statement,

In the cancel command button replace the code for it using the userform.hide method as shown below.

Code:

Now we will again run the userform by pressing the F5 key and provide it with some data as follows.

Now let us press the cancel button, and again run the userform which will give us the following result.

The data is not even lost when we pressed the cancel button if we press submit it will store the data in excel as follows.

Now the data is stored the userform is empty now and we can close the userform using the cancel button,

Things to Remember

• Once the data entry has been done by the user we need to close the userform so it is as important as the submit button.
• Close Userform is in the command button of the userform in VBA, In general, it is named as Cancel or Close.
• There are two separate methods of closing a userform but they eventually serve the same purpose as each other.
• The Userform.Hide method is most recommended as even if the userform is closed by the user before the data entry, the data is not lost and the entry made by the user is again displayed when a user opens the userform.

Recommended Articles

This is a guide to VBA Close UserForm. Here we discuss how to close UserForm in Excel using Unload Me statement and Userform. Hide method in VBA along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

VBA Close UserForm with Unload Me

Close a UserForm with VBA by using the Unload Me VBA statement. You can also unload userforms with different names by replacing “Me” with the name of your form.

I’ll show you how to close userforms with VBA in this brief tutorial from the VBA Tutorials Blog. At the bottom, you’ll see a macro showing how to use a loop to close all your userforms at once.

Example — VBA Close UserForm

Make powerful macros with our free VBA Developer Kit

This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one — we’ll send a copy, along with our Big Book of Excel VBA Macros, to your email address below.

Tutorial — VBA Close UserForm

Close a UserForm from UserForm Event

The macro above only works when you call it from an event procedure on that UserForm — like a command button. That’s because only the UserForm knows that “Me” is the UserForm.

In other words, if you type Unload Me in a regular module, it will fail with an error saying “Invalid use of Me keyword.” Remember, the module doesn’t know what UserForm you want to unload!

Let’s walk through an example showing where it’s appropriate to use “Unload Me” to close your form. Pretend you have a userform that looks like this:

You may recognize this UserForm from my Mouse To Macro mouse recording Excel Add-in. On this form, I’ve named the Cancel button cbCancel .

When you click the cbCancel button, the form is closed, because I double clicked the cbCancel button during development and pasted the example macro for the click event.

Your macro can be a lot more complicated than this. Feel free to run whatever commands you want before you put the Unload Me statement. “Unload Me” closes your form and removes everything associated with it from memory.

Close UserForm from another Module

This isn’t the first time you’ve seen me use the Unload statement. I used it to demonstrate my VBA Excel splash screen, but if you recall, I didn’t use the “Me” keyword. I could have, but I didn’t.

Let’s say you have a modeless UserForm (ShowModal property set to False), titled UserForm1 . When you have a modeless UserForm, you can click around in Excel and run other macros while your UserForm is open. You can interact with things other than your UserForm. This isn’t the default way to show a UserForm.

Anyway, pretend you have this modeless UserForm floating around. You have several other macros that run while the UserForm is just chilling there. To close your UserForm from one of these macros, you would pair “Unload” with the name of your UserForm, like this:

Because the UserForm was named UserForm1 , you enter that instead of the Me keyword.

Close All UserForms with VBA

If you want to close all your UserForms, you could create a loop that loops through each form and unloads each form — one by one. Here’s an example:

Application Ideas — VBA Close UserForm

I’m going to leave you with an application idea to motivate, inspire and challenge you. A long time ago, I created a modeless UserForm that stayed on the screen until the user pressed a certain key. Once that key was pressed, the form was closed.

Can you do that? Leave a comment below with your macros demonstrating how to do it!

If you haven’t already done so, join our VBA Insiders using the form below. After that, share this article on Twitter so your friends can learn.

Ready to do more with VBA?
We put together a giant PDF with over 300 pre-built macros and we want you to have it for free. Enter your email address below and we’ll send you a copy along with our VBA Developer Kit, loaded with VBA tips, tricks and shortcuts.

Before we go, I want to let you know we designed a suite of VBA Cheat Sheets to make it easier for you to write better macros. We included over 200 tips and 140 macro examples so they have everything you need to know to become a better VBA programmer.

VBA — Как переименовать или удалить модуль или форму

При первой вставке модуля в VBA ему автоматически будет присвоено имя «Модуль1», А последующий модуль станет Module2, Module3 и т. Д. Аналогичным образом, когда вы вставляете пользовательскую форму, она будет называться UserForm1, UserForm2 и т. Д.

Переименование модуля

Чтобы переименовать модуль или форму, вам необходимо включить окно свойств в вашем VBE.

Если окна свойств не отображаются, нажмите F4 или перейдите в строку меню и выберите Вид, Характеристики Окно.

1. Щелкните модуль, который хотите переименовать, в Обозреватель проекта.
2. Выберите свойство name в Окно свойств (модуль будет иметь только это свойство; форма имеет несколько свойств)

3. Удалите имя модуля (в данном случае Module1) и введите имя, которое вы хотите назвать своим модулем.

4. Нажмите Enter, чтобы переименовать модуль.

Таким же образом вы переименовываете свои формы.

Удаление модуля

Иногда вам может потребоваться удалить модуль или форму, которые вам больше не нужны.

Щелкните правой кнопкой мыши модуль или форму, которые вы хотите удалить, чтобы отобразить контекстное меню, вызываемое правой кнопкой мыши.

Нажмите Удалять (в этом случае Модуль2)

Нажать на Файл меню, а затем нажмите Удалите (Module2).

Появится окно с предупреждением, в котором вас спросят, хотите ли вы экспортировать модуль или форму, прежде чем удалять их. Экспорт модуля или формы позволяет сохранить его как отдельный файл для использования в другом проекте Excel в другое время.

Чаще всего, когда вы удаляете модуль или форму, это происходит потому, что он вам не нужен, поэтому нажмите Нет.

Именование модулей и форм

Рекомендуется переименовывать модули и формы, чтобы дать им более понятные имена. Например, если UserForm1 будет формой для ввода сведений о счете, мы можем переименовать ее в frmInvoices. Точно так же, если Module1 будет использоваться для хранения некоторых общих функций, которые будут повторно использоваться в вашем коде, вы можете переименовать его в basGeneral или modGeneral (в зависимости от того, какие соглашения об именах вы хотите использовать).