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.

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.

Then click “Options”.

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

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

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

Next click the “Go...” button.

Disabled Items Window will open like this.

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

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.

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.

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

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

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.

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.

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

Then go to the “Protection” tab of the “Format Cells” window. Uncheck the “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.

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.

Next go to the “REVIEW” tab.

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

“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.

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

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

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

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

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.

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.

What if the user selects multiple rows like this. 

 

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

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.

But sometimes users may select non contiguous rows like this. 

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

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.

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

Next 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.

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.

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