Show Message Box (Msgbox) For a Few Seconds

MsgBox function is a frequently used function in VBA. VBA applications use this function to communicate with the user. For example if a user entered incorrect input to the program then the program can notify it using msgbox function. Also this function is widely used to show the completion message to the users as well.

Below image shows a “Data saved!” message which was generated by msgbox function. In this sample application when the user fills the data and clicks the “Enter” button, program store them in the Excel database, clear the form and then display the “data saved!” message.

This is the code for the above example message box.

MsgBox "Data saved!", vbInformation, ""

Various types of message box styles are available in VBA such as vbOKOnly, vbYesNo, vbCritical etc. In the above example I have used vbInformation. Once the message box is displayed, the user needs to click on a button to close it. So assume a user needs to enter hundreds of records through a form. Then if we use the above technique, the user will need to close the message box hundreds of times. We can increase the efficiency of the data entry process if we omit this kind of additional work. So can we display a notification to a user in VBA which doesn’t require any action from the user? Yes there is a way. So in this lesson you can learn how to create a notification which closes automatically after a predefined number of seconds after the display.

We are going to do this using Windows Scripting Host. Windows scripting host is a language independent scripting engine. Now let’s look at how to do this. First we need to declare a few variables.

Dim Duration As Integer
Dim Message As Variant

Next we can set the duration.

Duration = 1

You should assign the message showing duration in seconds. In this example the message is visible to the user only for 1 second. Now we can use the Windows scripting shell like this.

Message = CreateObject("WScript.Shell").PopUp("Data saved", Duration, "")

Below is the complete code to show the message only for 1 second

Dim Duration As Integer
Dim Message As Variant

Duration = 1
Message = CreateObject("WScript.Shell").PopUp("Data saved", Duration, "")

"Data saved" Message will be displayed for one second and closed automatically. In the above example, the message doesn’t have any title. Because we have entered "" as the third parameter. Here is an example with the title.

Message = CreateObject("WScript.Shell").PopUp("Data saved", Duration, "Your title")

Also in the above example, the message has only the “OK” button. But we can add the buttons according to our requirements. These are the types of buttons available.

vbOKOnly
vbOKCancel
vbYesNo
vbYesNoCancel
vbRetryCancel
vbAbortRetryIgnore

Following example shows how you can use vbAbortRetryIgnore.

Message = CreateObject("WScript.Shell").PopUp("Data saved", Duration, "Your title", vbAbortRetryIgnore)

You can add other buttons in a similar way. Few icons are also available to format the message box. They are as follows.

vbExclamation
vbInformation
vbCritical
vbQuestion

Following example uses vbYesNoCancel as the button and vbExclamation as icon.

Message = CreateObject("WScript.Shell").PopUp("Message text", Duration, "Your title", vbYesNoCancel + vbExclamation)

Add Data to a Protected Sheet Automatically Using VBA

Protecting Excel sheets is a great way to secure the entered data. This can avoid users accidentally changing the data. But if you create advanced data entry applications in Excel, using forms then you will need to know how to enter data to password protected sheets automatically. Because sometimes we protect the sheets and let the users enter the data only through forms. For example assume we have a form like this.

Once users enter the data through the form, we can save it to a well organized excel sheet like this.

Learn how to transfer data from a form to an excel sheet

Then to secure the entered data, we can protect the data sheet with a password. But once the sheet is protected, we can not use the technique explained in the above lesson to transfer data from a form to an Excel sheet. Then Excel will display a message like this.

And if you click the Debug button, a line will be highlighted.

Code has thrown the error when it tries to write data to the sheet. Because data can not be entered to a protected sheet. So how do we overcome this? We can use a simple trick to accomplish this. We can tell the program to unprotect the sheet before writing the data to the sheet. Then after writing the data, program can protect the sheet again to safeguard the data. Here is an example.

Dim WS_Data As Worksheet
Dim Lastrow As Long

Set WS_Data = Worksheets("Data")

Lastrow = WS_Data.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

'Unprotect sheet
WS_Data.Unprotect Password:="password_here"

Enter data to Data sheet
WS_Data.Range("A" & Lastrow + 1) = txtOrderNumber.Value
WS_Data.Range("B" & Lastrow + 1) = txtSize.Value
WS_Data.Range("C" & Lastrow + 1) = txtSerialNumber.Value
WS_Data.Range("D" & Lastrow + 1) = txtProjectNumber.Value
WS_Data.Range("E" & Lastrow + 1) = txtType.Value
WS_Data.Range("F" & Lastrow + 1) = txtDate.Value
WS_Data.Range("G" & Lastrow + 1) = txtSurveyor.Value

'Protect sheet
WS_Data.Protect Password:="password_here"

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.

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.

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

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

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

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

UserForm1.Show

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.

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

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

This will open the “Record Macro” dialog box.

Give a suitable name to the macro. Then select “Personal Macro Workbook” from the “Store macro in” 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.

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.

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.

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

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.

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

Or else you can right click on one of selected cells. Then select “Copy” from the 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.

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

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.

And this option is also available under the Paste special.

Also see:

How to transpose columns and rows using VBA