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.

Detect VBA InputBox Cancel - Two Solutions

We often use InputBox to get inputs from users. But do you know how to detect if a user cancels an InputBox. If you don’t detect it then it may cause some errors. Because if you have lines of codes after the InputBox, and if you don’t detect the cancel and handle it accordingly, then the program will execute the lines of codes after the InputBox method. And this can cause various errors. So you should always detect InputBox cancel and handle it accordingly when you use this method.

So let’s look at how we can detect when a user cancels an InputBox. Let’s consider this sample subroutine.

Sub AskUserName()

     Dim Response As String

     Response = InputBox("Enter the user name:", "Username")

     Debug.Print "Hello " & Response & "!"

End Sub

If we run this, the program will ask to enter the username.

Get the username via InputBox

Then when we click “OK”, the program will print this in the immediate window.

Print username in the immediate window

However if we click the “Cancel” button, the program still prints in the immediate window.

Even user click cancel button the program still print in the immediate window

But if the user clicks the “Cancel” button, the program should not print anything in the immediate window. So how do we modify the subroutine to do that. When a user clicks the Cancel button, the InputBox method returns an empty string. So we can use that returned value to detect the cancellation action.

Sub AskUserName()

     Dim Response As String

     Response = InputBox("Enter the user name:", "Username")

     If Len(Response) = 0 Then
          Exit Sub
     Else
          Debug.Print "Hello " & Response & "!"
     End If

End Sub

So in the above code first we check the length of the string. If it is 0 then we exit the subroutine using the Exit statement. However there is a small problem in this method. If a user clicks the OK button without entering anything then the program still sees it as a cancellation. Because the InputBox method returns an empty string as the user didn’t enter anything. So if you want to act differently, for the user clicks cancel than to when the user clicks ok without entering anything, then you can’t use the above method. If you want to treat the user in different ways for those two actions, then you should use your own userform and handle the cancellation as desired. This second method explains how to do that.

First go to the VBA editor and insert a userform.

Open VBA editor and click insert
Insert Userform

Then add label, textbox and two command buttons to the form and format them to your preference.

Also see
How to add labels to VBA Userforms
How to Add Textboxes to VBA Userforms
How to Format Label Text in VBA Forms (To Give Professional Look)

Userform with OK and cancel buttons

In this method you have more freedom. You can apply any colors you want and also add additional buttons if needed. Next, insert a module.

Insert a module

Then add this code to the module.

Sub Main()

     UserForm1.Show

End Sub

When you run the “Main” Sub procedure, it will show the form. We can add the following code to the OK button of the form.

Private Sub cmdOK_Click()

     Debug.Print "Hello " & txtUsername.Value & "!"

End Sub

So this will print the username when you click the OK button. Finally we can add below code to the cancel button.

Private Sub cmdCancel_Click()

     Unload Me

End Sub

Now the form will close when the user clicks the cancel button.

Lock Macro Execution (Using Password)

In the last post, we learnt how to lock a VBA project with a password. So if a user wants to see the code then he/she has to input the correct password. But we realized, although the codes are locked, users can run the macros from the developer tab. But there may be times you want to also lock the macros from being executed as well. Then users will need to provide the password before running the macro. So in this post you can learn how to lock the macro execution using a password.

Let’s consider this simple macro

Sub WriteNumbers()

     Dim i As Integer

     For i = 1 To 10000
          Worksheets("Sheet1").Range("A" & i).Value = i
     Next i

End Sub

What this macro does is it writes the numbers from 1 to 10000 in column A of the Sheet1. Now let’s look at how we can modify the above macro to protect it from being executed. First we need to declare an additional variable.

Dim Res As String
Dim i As Integer

Then we can use an InputBox as follows.

Res = InputBox("Please enter the password!", "Password")

Now the program will ask for the password when the user tries to run the macro.

InputBox

Once the user enters the password, the program needs to verify whether it is correct or not. We can use an IF Statement for this. Also we can use the StrComp function to compare the user input with the password. Assume our password is “atyo45#4Yt”. Then we can develop the validating section as follows.

If StrComp(Res, "atyo45#4Yt", vbTextCompare) = 0 Then
     For i = 1 To 10000
          Worksheets("Sheet1").Range("A" & i).Value = i
     Next i
Else
     MsgBox "Sorry! Incorrect password"
     Exit Sub
End If

StrComp function compare the user input and the password (atyo45#4Yt). Then if the user input and password are matching then “If StrComp(Res, "atyo45#4Yt", vbTextCompare) = 0” becomes true. So the program goes to the For Next loop and writes the numbers. If the user input and password are not matching then the program shows the below message.

Password Incorrect message

This is the completed code.

Sub WriteNumbers()

     Dim Res As String
     Dim i As Integer

     Res = InputBox("Please enter the password!", "Password")

     If StrComp(Res, "atyo45#4Yt", vbTextCompare) = 0 Then
          For i = 1 To 10000
               Worksheets("Sheet1").Range("A" & i).Value = i
          Next i
     Else
          MsgBox "Sorry! Incorrect password"
          Exit Sub
     End If

End Sub

However there is one small problem in this macro. Typically, if a user clicks the cancel button of the input box or closes the input box, the program should not do anything. It should stop the execution without even showing any message. But in this macro,if a user cancels the Input box or closes it, still the program shows the password incorrect message. This is because when the user cancels or closes the input box it returns an empty string. So to avoid this what we can do is we can first check whether the variable “Res” holds an empty string. If it holds an empty string program will not perform any other actions. Here is the improved macro.

Sub WriteNumbers()

     Dim Res As String
     Dim i As Integer

     Res = InputBox("Please enter the password!", "Password")

     If Res = "" Then
          'Do nothing
     ElseIf StrComp(Res, "atyo45#4Yt", vbTextCompare) = 0 Then
          For i = 1 To 10000
               Worksheets("Sheet1").Range("A" & i).Value = i
          Next i
     Else
          MsgBox "Sorry! Incorrect password"
          Exit Sub
     End If

End Sub

However note that, when a user clicks the “OK” button without entering password (i.e. When the user input an empty string as password) the program doesn’t show the password incorrect message. Instead the program behaves similar to when a user clicks the cancel button.

How to Lock a VBA Macro

So far we developed various macros according to numerous requirements. So today I’m going to show you how to lock them with passwords. Because once you developed a macro you may want to lock it as it is your intellectual property. Also you may want to lock the project to protect it from being wrecked by other users.

So now let’s see how we can lock the project. Follow these easy steps. First open the Excel file containing the macro. Then click somewhere in the worksheet. Next press Alt + F11 on your keyboard. This will open the VBA editor.

Open VBA editor

Alternatively, you can go to the “Developer” tab and click on “Visual Basic” to open the VBA editor as well.

Go to the “Developer” tab

Click on the Visual Basic

Note that the “Developer” tab is not displayed by default. This post explains how to add it to the ribbon.

How to show the Developer tab

As you can see in the first image, I have a very simple macro in the module1. To lock the project first click on the “Tools” menu.

Click on the Tools menu

Then select “VBAProject Properties...”

Select VBAProject Properties

“Project Properties” dialog box will open. Go to the “Protection” tab and check the “Lock project for viewing”. Then enter the password in both textboxes and click OK.

Project Properties dialog box

Save the file, close it and reopen. Now when you go to the VBA editor and try to expand the project from project explorer it will ask you the password to unlock.

VBA project is protected

So if a user doesn't have a password then he/she can't view the code. But they can still run the macros from the developer tab. Thus anyone can run the macro without messing it up.

However if you want you can also protect the macro execution with a password too.

Lock Macro Execution (Using Password)

Get the Day Name of a Date Independent of Regional Settings

In the last post we developed a macro to get the weekday name of a date. But then we had to use the format of the dates according to the regional settings. So in this lesson I will show you how to develop a more advanced solution, to get the day name of a date independent of regional settings. For that I will use two more functions called “Split” and “DateSerial” in addition to the “Format" function. Let’s consider this sample excel sheet. As specified in the top row of column A, dates should be added in the given format (ex - mm/dd/yyyy). However you can easily change the code to suit with the format you need.

Add the dates in the given format

Dates have been added to column A in "mm/dd/yyyy" format. Now our macro should write the day name in front of each date.

So let’s start the macro by declaring the variables.

Dim WS As Worksheet
Dim i As Long
Dim iYear As Integer
Dim iMonth As Integer
Dim iDay As Integer
Dim ColA_Text As String
Dim CurrentDate As Date
Dim DayName As String

Assign the activesheet to the WS variable.

Set WS = ActiveSheet

We have dates in multiple rows. Therefore we need a For Next statement to iterate through each row. As we have dates from row 2 to row 10 we can use the For Next statement as follows.

For i = 2 To 10

Next i

When looping through each row assign the date of column A to ColA_Text variable.

ColA_Text = WS.Range("A" & i).Text

Now this variable holds the date as string. So we can extract day, month and year separately using the split function.

iDay = Split(ColA_Text, "/")(1)
iMonth = Split(ColA_Text, "/")(0)
iYear = Split(ColA_Text, "/")(2)

Want to learn about split function. Read this post.

Split Function

Now we have day, month and year in 3 separate variables. So we can use the DateSerial function to get the date correctly independent on the region user lives.

CurrentDate = DateSerial(iYear, iMonth, iDay)

Next, use the “Format” function to get the day name like we did in the previous lesson.

DayName = Format(CurrentDate, "dddd")

Finally we can write the day name to column B of the worksheet.

WS.Range("B" & i).Value = DayName

Below is the completed code.

Sub GetDayName()

     Dim WS As Worksheet
     Dim i As Long
     Dim iYear As Integer
     Dim iMonth As Integer
     Dim iDay As Integer
     Dim ColA_Text As String
     Dim CurrentDate As Date
     Dim DayName As String

     Set WS = ActiveSheet
     For i = 2 To 10
          ColA_Text = WS.Range("A" & i).Text
          iDay = Split(ColA_Text, "/")(1)
          iMonth = Split(ColA_Text, "/")(0)
          iYear = Split(ColA_Text, "/")(2)
          CurrentDate = DateSerial(iYear, iMonth, iDay)
          DayName = Format(CurrentDate, "dddd")
          WS.Range("B" & i).Value = DayName
     Next i

End Sub

And this is the output of the macro.

Macro output the day name independent of region settings

In the above example we used “mm/dd/yyyy” as the date format. But as I said you can easily change the code to suit other formats. Because you need to change only these three lines to suit with the format.

iDay = Split(ColA_Text, "/")(1)
iMonth = Split(ColA_Text, "/")(0)
iYear = Split(ColA_Text, "/")(2)

For example if dates are written in “dd/mm/yyyy” format you can modify the above three lines as follows.

iDay = Split(ColA_Text, "/")(0)
iMonth = Split(ColA_Text, "/")(1)
iYear = Split(ColA_Text, "/")(2)


Get the Day Name of a Date Using VBA

In this lesson you can learn how to get the weekday name of a date. Here is an example worksheet. Column A of this sheet has a list of dates. We are going to get the day name of each date into column B using a macro.

Sample worksheet containing dates

First let’s give our macro a name. I will name the macro as “GetDayName”.

Sub GetDayName()

End Sub

We need a few variables for this job.

Dim WS As Worksheet
Dim i As Long
Dim CurrentDate As Date
Dim DayName As String

Next I will assign Activesheet to the WS variable.

Set WS = ActiveSheet

We have dates from row 2 to row 10. So we need a For Next statement to iterate through each row.

For i = 2 To 10

Next i

Then when the program loops through each row we can assign the date to CurrentDate variable like this.

CurrentDate = WS.Range("A" & i).Value

Next, the day name can be obtained using Format function.

DayName = Format(CurrentDate, "dddd")

Finally we can write the result to column B

WS.Range("B" & i).Value = DayName

Below is the completed macro.

Sub GetDayName()

     Dim WS As Worksheet
     Dim i As Long
     Dim CurrentDate As Date
     Dim DayName As String

     Set WS = ActiveSheet
     For i = 2 To 10
          CurrentDate = WS.Range("A" & i).Value
          DayName = Format(CurrentDate, "dddd")
          WS.Range("B" & i).Value = DayName
     Next i

End Sub

And this is the result produced by the macro.

Macro returned the day name for each date

In this example I entered the dates in dd/mm/yyyy format. Also I have used the same in regional settings. But if I send this file to a user whose regional date format is “mm/dd/yyyy” then this program will output incorrect results. So if you want to use this macro, then you should format the dates in column A similar to your regional settings.

Calculate Duration Between Two Times of Two Adjacent Days

In the last post we learnt how to calculate the duration between two times of the same day. Today let’s look at how to calculate duration between two times of two adjacent days. Let’s consider this sample data. This is a start and end time of work for some employees.

Times are on same day or adjacent days

If you look at the start and end times of the work, you will notice that some employees have ended the work on the same day as started and others have ended the work on the next day. So if we use the code of our previous post it will only give correct results for cases where employees started and ended the work on the same day. Now let’s look at how to develop a solution which suits both of these types.

As usual, first we need to declare a few variables.

Dim WS As Worksheet
Dim StartTime As Date
Dim EndTime As Date
Dim i As Integer
Dim DurationInHours As Double

Let’s assume we have the data in the sheet called “Data”. Then we can assign that sheet to the WS variable as follows.

Set WS = Worksheets("Data")

Next we need a For Next statement to iterate through each row. As we have employee names from 2nd row to 10th row we can write the For Next statement like this.

For i = 2 To 10

Next i

Now when the program loops through each row we can assign values for StartTime and EndTime variables.

StartTime = WS.Range("B" & i).Value
EndTime = WS.Range("C" & i).Value

Then duration can be calculated using the DateDiff function as follows.

DurationInHours = DateDiff("n", StartTime, EndTime) / 60

But now the problem is that the DateDiff function calculates the time difference assuming both times belong to the same day. If the end time is on the next day, then the program returns a negative value. Therefore we can use the sign of the returned value to identify whether end time is on the same day or not. And once we detect an end time which is on the next day, we can correct the returned value easily by adding 24 to it. Here is the reason for adding exactly 24. Even when the end time is on the next day, DateDiff calculates the value assuming the end time is on the morning of the same day. So we can easily fix it by finding the duration in the opposite direction by adding 24 to the returned result.

Also if ending time is on the same day, then we can use the value returned from the DateDiff function as it is.

If DurationInHours < 0 Then
     DurationInHours = DurationInHours + 24
End If
WS.Range("D" & i).Value = DurationInHours

You can see that we have altered the DurationInHours variable only if it is negative. So here is the full code.

Sub CalculateHoursWorked()

     Dim WS As Worksheet
     Dim StartTime As Date
     Dim EndTime As Date
     Dim i As Integer
     Dim DurationInHours As Double
     Set WS = Worksheets("Data")
     For i = 2 To 10
          StartTime = WS.Range("B" & i).Value
          EndTime = WS.Range("C" & i).Value
          DurationInHours = DateDiff("n", StartTime, EndTime) / 60
          If DurationInHours < 0 Then
               DurationInHours = DurationInHours + 24
          End If
          WS.Range("D" & i).Value = DurationInHours
     Next i

End Sub

Now the program calculates the duration correctly for both types.

Duration calculated correctly for same and adjacent days

Calculate Time Difference Using VBA

Sometimes we need to calculate time differences in VBA applications. It is often required when we develop solutions related to employment. Here is an example.

Employee names and start and end time of their work

This sheet contains names of employees and the start and end time of their work. Then we need to calculate the hours worked for each employee. So in this lesson you can learn how to calculate the duration of two times using a macro.

First we need to declare a few variables.

Dim WS As Worksheet
Dim StartTime As Date
Dim EndTime As Date
Dim i As Integer

If the name of the sheet is Sheet1, we can assign that sheet to the WS variable like this.

Set WS = Worksheets("Sheet1")

In this example we have only nine names. We can use a For Next loop to loop through each row.

For i = 2 To 10

Next i

Now in each iteration assign the start and end time to variables StartTime and EndTime respectively.

StartTime = WS.Range("B" & i).Value
EndTime = WS.Range("C" & i).Value

Once the start and end times are assigned, use the VBA function called “DateDiff” to calculate the time difference.

DateDiff("n", StartTime, EndTime)

This function has five arguments. But the last two are optional and not needed for this type of calculation. First argument is the interval. We can give the interval as year, month, day, hour, minute etc. In here we used “n” and it refers to the minutes. So we will get the duration in minutes. Then we can divide the result by 60 to get the values in hours. So the final code should look like this.

WS.Range("D" & i).Value = DateDiff("n", StartTime, EndTime) / 60

Below is the complete macro.

Sub CalculateHoursWorked()

     Dim WS As Worksheet
     Dim StartTime As Date
     Dim EndTime As Date
     Dim i As Integer
     Set WS = Worksheets("Sheet1")
     For i = 2 To 10
          StartTime = WS.Range("B" & i).Value
          EndTime = WS.Range("C" & i).Value
          WS.Range("D" & i).Value = DateDiff("n", StartTime, EndTime) / 60
     Next i

End Sub

This is the result of the above code.

Duration between start and end time

You may think why I didn’t use the hour as interval to directly calculate the number of hours like below.

WS.Range("D" & i).Value = DateDiff("h", StartTime, EndTime)

Instead of

WS.Range("D" & i).Value = DateDiff("n", StartTime, EndTime) / 60

But then the problem is DateDiff function returns only the whole number part disregarding the decimal part. So if we use “h” as the period then we will get below result which is incorrect.

DateDiff function returns only whole number part when hour is used as the interval

And here is the next challenge. What if some employees start work in the evening or night and end it in the next day. Then our program generates incorrect results like this.

When start and end times belongs to two adjacent days

So in my next post I will show you how to calculate the time difference of two adjacent days like that.

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.

Msgbox VBA function in action

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, "")
Message box created with WScript.Shell

"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")
Message box created using Windows scripting shell with 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)
Message box with vbAbortRetryIgnore button and title

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)
Message box with vbYesNoCancel button and vbExclamation icon

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.


Data entry form

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

Excel database

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.

Error message

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

One line 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.

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 *