Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Show Userform Automatically When Opening Excel File

In Excel VBA applications, we often need to automatically show userforms when opening the Excel files. Suppose we have a simple VBA userform in our Excel file like this.

VBA Userform

This form is used to enter the Order information. Users can fill all the fields and click the “Enter” button. Then all the information will be written automatically to the next empty row of the data sheet. Also if needed, we can hide the data sheet from the users to protect its data. Then the question is how do we show this form to the users? There are few ways to show the userform to the users. We can create a simple macro to show the form. Then users can run that macro from the developer tab to see the form. But this method would not be user friendly. Because the “Developer” tab is not even visible by default in Excel. Our next option is to create a simple button in a worksheet and assign that macro to the button. Then the user can click the button to open the form. But my preferred method is showing the form automatically when opening the file. Then no extra action is needed from the user. This is how to configure it.

First, Open the VBA editor. You can use shortcut keys Alt + F11 to open the VBA Editor.

Open VBA editor

VBA Project explorer window will show you all the available modules. Double click on the ThisWorkbook module.

Double click on ThisWorkbook

There are two dropdowns above the code window. Select “Workbook” from the first dropdown.

Select Workbook

When you select the “Workbook” second dropdown will automatically change to “Open”. Also Workbook_Open procedure will be automatically added to the code window.

Workbook_Open procedure is added

Now we can write the code to show the form inside this procedure. We can simply do that using the below line.

UserForm1.Show

Final Workbook_Open procedure

Here UserForm1 is the name of the form. You should replace it with the name of your form. Now form will show up whenever the user opens the file.

How To Record Macros In Personal Macro Workbook

Macros are very useful if you want to increase the productivity of your excel files. If you do repetitive tasks in Excel then you can automate them using macros. This is how it works. First you record all the actions when doing the process for one time. Then you can run that recorded macro as many times as you want. Also if you have good knowledge on VBA then you can take those recorded macros to a higher level. In this lesson I will explain to you one more step to save your time when using macros. Assume you want to use a particular macro again and again in different workbooks. Normally if you record a macro, that macro will only be available for that workbook. But if you change the macro storing location to Personal Macro Workbook then those macros will be available whenever you launch Excel application. Thus you can use those macros in any workbook you open. From this lesson you can learn how to record macro in Personal Macro Workbook.

First, go to the “Developer” tab.

Go to developer tab

Don’t have the “Developer” tab? Learn how to enable it.
How to show the Developer tab

Select “Record Macro” in the “Code” group.

Select record macro

This will open the “Record Macro” dialog box.

Record Macro dialog box

Give a suitable name to the macro. Then select “Personal Macro Workbook” from the “Store macro in” dropdown.

Select Personal Macro Workbook from the dropdown

For now let’s leave the Shortcut key and Description blank as they are optional. Next click “OK” to record the macro. Now you can perform the actions you want to record. Once you finish the actions, click on the “Stop Recording” to stop the macro recording.

Stop Recording

Finally you need to save the recorded macros. You can save the recorded macros in the Personal Macro Workbook when closing the Excel application. When you terminate the Excel application it will display a message like this. You can click “Save” to save the macros.

Save macros to Personal macro workbook

How To Switch Rows And Columns In Excel

Excel stores your data using a grid of cells. Rows are named with numbers and columns are named with letters. Sometimes you may want to transpose your data. For example, assume you have a worksheet like this.

Excel worksheet before transpose

Then sometimes you would want to switch rows and columns like this.

Excel sheet after switching rows and columns

There are few different ways to do this. In this post I will show you how to do this using the Paste Special method.

First select the range of data you want to transpose. Remember to include row and column labels if there are any.

Select the data range

Then copy that data. You can press Ctrl + C from the keyboard or you can select “Copy” from the Home tab.

Select Copy from the Home tab

Or else you can right click on one of selected cells. Then select “Copy” from the shortcut menu.

Select Copy from shortcut menu

You can use one of the above three methods to copy the data. Next you need to use the “Paste Special” method. Select the first cell where you want to paste your data. Then click on the dropdown button next to “Paste” in the “Home” tab.

Click the dropdown button next to Paste

Then Excel will show you various paste options available. Select the “Transpose” option from the list.

Select Transpose from paste options

Or else you can right click on the first cell where you want to paste the data. Then select the “Transpose” option from the shortcut menu.

Select transpose from the shortcut menu

And this option is also available under the Paste special.

Paste special options

Also see:

How to transpose columns and rows using VBA

Contact Form

Name

Email *

Message *