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.

Personal Macro Workbook in the startup folder must stay open for recording - (How to solve)

When we record macros in Excel, normally they are saved to the active Excel workbook. Then if you close that workbook, recorded macro will no longer be available for other workbooks. But sometimes you may want to use a particular macro in various Excel files. Luckily, Excel has a solution for this. What you have to do is record the macro in the “Personal Macro Workbook”. Then those macros will be available for all the Excel files you open.

But some people are having a problem using this feature. Because when they try to store the macro in the Personal Macro Workbook, Excel shows the following messages to them.

Error - Personal Macro Workbook in the startup folder must stay open for recording

Error - Unable to record

If you are also having this problem, this post will help to solve the issue. Follow these easy steps.

Launch the Excel application. Click on the “FILE” menu.

Click FILE menu

Then click “Options”.

Click Options

Excel Options dialog box will open like this. Then click the Add-Ins category.

Click Add-Ins category

In the Add-Ins category you can find the “Manage” dropdown at the bottom.

Manage dropdown

Then select the “Disabled Items” from the dropdown list.

Select the Disabled Items

Next click the “Go...” button.

Go… button

Disabled Items Window will open like this.

Disabled Items Window

Select the personal.xlsb from the list. Then click the “Enable” button.

Select personal.xlsb

Close the Disabled Items window. Then close the “Excel Options” dialog box. Now close the Excel application and start it again. You will be able to store the macros in the Personal Macro Workbook again.

How To Remove Gridlines In Excel

Gridlines are light grey color lines that show up between rows and columns. These lines make it easier to distinguish the content of adjacent cells. But if we design application interfaces or templates sometimes these gridlines can have a negative effect on the appearance. In this lesson I will show you how to remove these gridlines from an Excel sheet.

Here is a simple data entry application interface designed on an Excel sheet.

Sample data entry application interface

We have used a fill color and outlines for the data input area. But we can further improve the appearance if we remove the gridlines.

To remove the gridlines, first go to the “VIEW” tab of the Excel ribbon.

Go to the View tab

In the “Show” group, uncheck the “Gridlines” checkbox.

Uncheck Gridlines checkbox

This is how the application interface looks like after removing the gridlines.

Final appearance of the application interface

Lock Only Specific Cells In An Excel Worksheet

In the previous lesson we learnt how to protect a worksheet. By default all the cells of worksheets are locked. So when we protect the sheet, we can not change the values of any cells. But sometimes we need to lock only specific cells of a worksheets. Then we will be able to change values in cells we need. So In this lesson you can learn how to lock only specific cells in an Excel worksheet. Let’s consider this sample Excel sheet.

Sample data entry application interface

This is a data entry interface of a simple VBA project. Here the data entry interface is designed on a worksheet instead of a VBA form. Column B of this sheet has field names. And grey color cells of column C are used to get the user input. Suppose that we want to lock only field names of the worksheet. Then users can enter the data to relevant cells of column C. But they will not be able to change the field names in column B. Now let’s look at how to do this.

First select the cells which you don’t want to lock. In this example we need to select the grey color cells of the column C.

Select cells you don’t want to lock

Then right click on one of selected cells and select “Format Cells…” from the shortcut menu.

Select Format cells from the shortcut menu

Then go to the “Protection” tab of the “Format Cells” window. Uncheck the “Locked” option.

Uncheck locked option

Now we need to protect the sheet. You can follow the steps explained in our previous post to protect the sheet.

How To Protect A Worksheet In Excel

Then even after protecting the sheet, we can enter values to the grey color cells we unlocked. But Excel will display this message if we try to change any other cells.

Warning message

How To Protect A Worksheet In Excel

Sometimes you may want to protect a worksheet before sending it to others. So in this post I will show you how to protect a worksheet.

First go to the worksheet you want to protect. I’m going to protect Sheet2 from this sample file.

Go to the sheet you want to protect

Next go to the “REVIEW” tab.

Go to the Review tab

Then click on the “Protect sheet” icon of the “Changes” group.

Click on the Protect sheet icon

“Protect Sheet” window will open like this. Now select operations you want to permit your users to carry out in the locked sheet. By default first two operations (“Select locked cells” and “Select unlocked cells”) are selected. For this sample file, I will proceed with default selection.

Protect sheet window

Next, enter a password to unprotect the sheet and click OK.

Enter password to unprotect sheet

Excel will ask you to reenter the password. Reenter the password and click OK.

Reenter password

Now the worksheet is protected. If you try to change a value or enter a new value, Excel will display a message like this.

Warning message

Also lots of icons are disabled as I haven’t permitted the users other activities than selecting locked and unlocked cells.

Icons are disabled

Get Selected Rows Using VBA Macro

In this lesson I will show you how to get selected rows of an Excel sheet using a macro. As developers we need to create various kinds of macros to fulfil user’s requirements. Sometimes users want to select rows manually in the excel sheet before running the macros. Then macro may need to detect the rows selected by the user before processing the data. For an example user may want to transfer selected rows to another file or sheet. Or combine data of selected rows. Those are a few examples where you need to get the selected rows. Now let’s learn how to get the selected rows using VBA.

Suppose a user has selected a one row like this.

One row is selected in Excel sheet

Then we can use this macro below to print the address of the row in the immediate window.

Sub GetSelectedRows()

     Dim SelectedRows As String
     Dim Rng As Range
     Set Rng = Selection
     SelectedRows = Rng.Address
     Debug.Print SelectedRows

End Sub

Here is the result of the above macro.

Result printed in the immediate window

What if the user selects multiple rows like this. 

User select contiguous rows

Then we will get the below result if we run the same macro.

Result of the contiguous rows

In the above examples, the result was a string. But usually we need to get the starting row and the ending row as numbers. Then only we can use those values in the next steps. So next let’s look at how we can get the starting row and the ending row. We can create two functions to get starting and ending rows. This is the function to get the starting row.

Function StartingRow() As Long

     Dim SelectedRows As String
     Dim Rng As Range
     Set Rng = Selection
     SelectedRows = Rng.Address
     StartingRow = Replace(Split(SelectedRows, ":")(0), "$", "")

End Function

In this function we have used two inbuilt functions. Split function and Replace function. Split function divides the string at the character “:”. For the above example, Split(SelectedRows, ":")(0) will return $3
Then the replace function will remove the $ symbol.

And this is the function to get the ending row.

Function EndingRow() As Long

     Dim SelectedRows As String
     Dim Rng As Range
     Set Rng = Selection
     SelectedRows = Rng.Address
     EndingRow = Replace(Split(SelectedRows, ":")(1), "$", "")

End Function

Only difference is you have to replace 0 in the split function with 1. Then the split function will split the string at character : and take the second element. Now you can use the above functions in a subroutine.

Sub Main()

     Dim StartRow As Long
     Dim EndRow As Long
     StartRow = StartingRow()
     EndRow = EndingRow()
     Debug.Print "Start - "; StartRow
     Debug.Print "End - "; EndRow

End Sub

Here is the result you will get.

Return values of the function

But sometimes users may select non contiguous rows like this. 

Selection of non contiguous rows

Then our first macro (GetSelectedRows) will output this result.

Result for the non contiguous rows

But then our two functions will fail to output correct results. In a next lesson I will show you how to detect a non contiguous range and output the result as a two dimensional array. 

How To Run A Macro In Excel

You can use macros to automate manual tasks you do in Microsoft Excel. Using this feature you can complete repetitive and time consuming tasks in a few clicks. Even you can accomplish things which are practically impossible to do manually. For example , suppose you want to compare two sheets which are having more than 1 million rows of data. Doing this manually is not practical. But a macro can handle this task pretty easily. Also once you create a macro, you can use it indefinitely. Macros are developed using a programming language called VBA. If you know VBA, then you can create advanced and highly effective macros. However even if you don’t know the VBA, still you can create macros using the macro recorder feature. Check this post if you want to know how. Record a Macro in Excel

In this post I will show you how to run a macro step by step. Suppose you received a file which has a macro. If the developer has designed the interface or added controls like buttons to the worksheet then you can run the macros using those tools. But if no such option is available you can run any macro from the Developer tab.

So let’s look at how to run a macro from the Developer tab. First go to the Developer tab of the Excel ribbon.

Go to the developer tab

“Developer” tab not visible in Excel? Learn how to enable the developer tab

Next click on the “Macros” icon.

Click on the Macros icon

Also you can use shortcut keys Alt + F8 instead of clicking the icon. Then the Macro window will appear like this.

Macro window

There is only one macro name in this list. If your file contains multiple macros, then the Macro window will show all the available macros in the list. If there are multiple macros, you should select the macro you want to run from the list. Also you can use the dropdown to see the macros available in all open workbooks, in the current workbook or any other workbook.

Use dropdown to see macros available in all open workbooks or specific workbook

After selecting the macro name from the list you can run it by clicking the “Run” button.

Contact Form


Email *

Message *