VBA - Select All Unlocked Cells

We saw how to use Excel protection features in one of our earlier post. We could unlock certain cells and lock other cells so that users can only have option to modify unlocked cells. We also noted that Locking and Unlocking cells will have no effect until the sheet is protected. All good till there, however there is no direct way in Excel to quickly see which cells are locked or unlocked.

When the sheet is protected, you can use Tab key to move through all the unlocked cells. But imagine a situation where you are preparing a sheet to distribute to your colleagues with only few unlocked cells spreading across sheet. To make sure that you have locked / unlocked required cells, you will have to go through Format Cells option and check if the cell you selected is locked or not.

Now imagine yourself as a user of such sheet that someone else designed. You cannot know which cell is unlocked and ready to be modified, unless such cells are highlighted by different colour or any such other markings.

In this post, we are going to develop a code to quickly select all the cells that are in unlocked status. Let’s go ahead and do it. Before going to the coding, let’s discuss a bit on the logic we should follow.

1. First, we should run the macro in the sheet from where the code is run. We know that ActiveSheet object refers just that.
2. In the sheet, the code should run in only those cells which are used i.e. modified, locked or unlocked etc. Why to run the code in all cells in entire sheet there by wasting precious processing time, isn’t it? Alright, with bit of research in Excel Help or going through the Properties of ActiveSheet, you will know that there is a property of ActiveSheet object which is UsedRange that is a collection of all cells that are used.
3. Now we have a collection of all used cells, we need to loop through each of these cells and see if that particular cell is locked or not. We know that For Each Loop can do this work for us.
4. How to know if cell is locked? Let’s see what Excel records when we lock / unlock a cell by recording a macro with this action. By default all the cells are locked, I am going to record a macro while unlocking cell A1. Below is the code Excel gave me.

• Sub Macro1()
  '
  ' Macro1 Macro
  '
   '
      Selection.Locked = False
      Selection.FormulaHidden = False
  End Sub
5. Selection.Locked = False is the line we need. Cell A1 which I selected is called through Selection object and its property Locked is set to False, that means cell is now not locked. Excel Help is your friend again; each Cell has a property Locked. If this property is set to True that means that cell is locked otherwise it is unlocked. (Additional info: Notice the line Selection.FormulaHidden = False, this line is not needed for us now, this code is used if you want to hide the formulas from displaying in Formula Bar)
6. Alright, now we know how to see if a cell is locked or not. One last thing that is left out is what to do with this information. This one depends on your need, you may want to select all such cells or get the addresses of all those cells in a sheet etc. For this exercise, I am going to select all such cells.
• I recorded another macro while selecting cell A2. This is the code.
• Sub Macro2()
  '
  ' Macro2 Macro
  '
  
  
  '
      Range("A2").Select
  End Sub
7. Good, now we know that Range object has a method Select which selects the cell address given in Range object.
8. Now the tricky part, in Excel you can only select a cell or a combination of cells in one operation. If you try to select cell A1 and after that try to select A2, A1 selection will be gone (unless you keep pressing Ctrl button while selecting A2). Since our macro will loop through to identify an unlocked cell and select it, in the next looping, already selected cell selection will be gone. Let’s observe how Excel captures selecting various cells when you press down Ctrl. Below is the code recorded by Excel when I select so many random cells.
9. Sub Macro3()
   '
   ' Macro3 Macro
   '
   
   
   '
       Range("E9,C5,C9,B13,A8,A5,B3,D3,F2,H2,H5,J5,J8").Select
       Range("J8").Activate
   End Sub
10. See, Excel just combines all cells addresses with a comma and embed it in one Range object and use Select method to select them. We will also use the same thing, I am going to use a String to capture unlocked cell addresses and select all of them at the end of the code using above logic.

Let’s put all the above information we noted in code format. Here is my code.

Sub SelectUnlockedCells()
Dim Cl As Range
Dim MyStr As String
 For Each Cl In ActiveSheet.UsedRange 'Start looping, refer to point 1, 2 & 3
    If Cl.Locked = False Then MyStr = MyStr & Cl.Address & "," 'Refer point 4 & 5
Next Cl ‘End looping
If MyStr <> "" Then Range(Left(MyStr, Len(MyStr) - 1)).Select 'Point 6, 7 & 8
End Sub

I used MyStr to capture all the cells that are having Locked property False. I also used a comma (,) to combine different cells addresses. In the last line of code, I am checking if MyStr is empty (means no unlocked cells are found), if it is not empty then used Range object Select method to select all those cells.

One thing I have to explain is what Left(MyStr, Len(MyStr) - 1) does in the code. If you notice the MyStr just before executing this line of code, there will be a comma at the end of the string. This would result in failure of the code as Range object needs exact cell address with comma in-between but not at the ends. So I am removing the extra comma at the end of MyStr by using Left function of VBA. (This function is similar to Excel’s built-in Left function).

Just run the code now and all the unlocked cells will be selected. Now colour them, enter values in them or apply borders, it is up to you what do you want to do.

I hope the above post explains clearly on how you proceed to code your requirement in the start. Once you know VBA Code Structure, you don’t even need to record macros to see what Object to use. So remember the mantra, the more you experiment the more you learn. Happy Learning!!

VBA - Work With Gridlines

Do you notice the faint lines around cells in any Excel sheet? These are called gridlines. They are used to distinguish cells on the worksheet i.e. each row and each column is separated by a gridline.

Gridlines are visible by default in Excel and you can hide them if you don’t want at any point of time. However, gridlines are sheet specific. That means you will have to hide them for each sheet or select multiple sheets and hide the gridlines for those sheets.

Also, if you are using Excel 2003 or earlier versions, finding the gridlines hide/unhide option is bit long. You will have to follow Tools > Options > View > Gridlines (Check/uncheck the box). It is relatively easy in Excel 2007 or later as this option is available directly under the View tab.

Today, we will see how to hide/unhide gridlines for all the sheets in a workbook through a macro. We will follow the same logic we followed in Unhide all hidden sheets post.

Record Hiding a Sheet’s gridline

Create a new workbook and save it as macro enabled workbook.

In Excel 2007 or later: Click on View ribbon > Macros drop-down button > Record Macro

In Excel 2003 or earlier: Click on Tools menu > Macro > Record New Macro

• Enter "Macro Name" as HideGrid
• Click OK.

After clicking OK, you can notice a blue coloured button in Excel 2007 or later and a word "Recording" on the status bar in Excel 2003 or earlier. Now your macro started recording.

Excel 2003 or earlier versions click on Tools > Options > View > Gridlines (uncheck the box to hide gridlines). Excel 2007 or later, click View tab > Show group > Gridlines (uncheck the box to hide gridlines). Your macro would have recorded your action, let’s go and check it out. Before that stop macro recording by clicking on that blue button.

Press Alt+F11. This should open up the VBE window. (Read about VBE appearance here). In Project Explorer window, you can notice that Excel created a new folder Modules with a plus sign next to it. Clicking on that sign or double clicking on folder name shows you the child item under that folder which is Module1. If you are unable to view Project Explorer window, access it from View menu of VBE window. My recorder shows the below code.

Sub HideGrid()
'
' HideGrid Macro
'
 '
    ActiveWindow.DisplayGridlines = False
End Sub

Notice the line ActiveWindow.DisplayGridlines = False. This is what we need to hide the gridlines for the sheets; on the contrary, setting the property to True will show the gridlines. Now all we need to do is apply this setting to each sheet in the workbook. At this point, you might want to take a look at Sheets collection and how we use a For Each statements to loop through all sheets in our earlier post.

Also in the above code, note that ActiveWindow is the sheet that is active at the point of running the macro. So instead of changing the sheet property, macro is actually accessing ActiveWindow property to change the visibility of gridline. That gives an important point to note for this post, you should have a sheet active (selected) to change its gridlines property.

So, logic for our code would be, for every sheet in this workbook, activate (select) the sheet and change the gridlines property to false. Let’s go ahead and do that in code format.

Sub HideAllGrids()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Sheets ‘For all sheets in workbook
    Sht.Select ‘Select sheet
    ActiveWindow.DisplayGridlines = False ‘change gridlines property to false
Next Sht ‘go to next sheet
End Sub

The above code uses a variable Sht to represent the sheet. We are activating each sheet in the workbook and changing the gridline property to False. This process repeats until the entire sheet’s gridlines are changed.

Show All Gridlines

In the above code, change the line ActiveWindow.DisplayGridlines = False to ActiveWindow.DisplayGridlines = True if you want to show the gridlines in all sheets.

Toggle Gridlines

In the above code, change the line ActiveWindow.DisplayGridlines = False to ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines if you want to toggle the gridlines in all sheets.

Hide Gridlines – Take 2

Code above will loop through each sheet to change the property. You can get the same result from the below code without use of a variable.

Sub HideAllGrids()
Sheets.Select
ActiveWindow.DisplayGridlines = True
End Sub

Hide Gridlines – Take 3

Above set of codes work perfectly when all the sheets are ready to be selected. I mean to say, if there is a hidden sheet, that sheet cannot be selected, isn’t it? So, above code will fail if there is at least one sheet that is hidden. What to do in that case?

Use On Error Resume Next

If you just want to ignore the hidden sheet, include the above error trapping in your code.

Sub HideAllGrids()
Dim Sht As Worksheet
On Error Resume Next
For Each Sht In ActiveWorkbook.Sheets ‘For all sheets in workbook
    Sht.Select ‘Select sheet
    ActiveWindow.DisplayGridlines = False ‘change gridlines property to false
Next Sht ‘go to next sheet
End Sub

Check the Visibility of Sheet

You can check the visibility property of the sheet and decide about the next step. For example, you might want to unhide the sheet and apply the property. Let’s see how to do that.

Sub HideAllGrids()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Sheets
    If Sht.Visible <> True Then Sht.Visible = True ‘Check if sheet is visible, if not make it visible
    Sht.Select
    ActiveWindow.DisplayGridlines = False
Next Sht
End Sub

That’s all. As we did above, you can modify your code to fit to any scenario. Just remember doing a little bit of research every time, doesn’t matter if you fail at the beginning, you will surely master it quickly. Happy Learning!!

VBA - Remove All Shapes In a Sheet

Hi. This post is a result of recent situation I faced. I copied contents of a webpage and pasted into my Excel workbook for further analysis. All was good until I noticed many tiny little checkboxes / shapes that were part of webpage also got pasted into the workbook. I could have used Paste Special command to paste only text but I also need the formats, however that’s not the point. We are trying to delete all shapes in a worksheet with single operation. There are few ways of deleting these shapes in Excel but all involve more than one click. Let’s see how to do this by way of a macro; it will also give us some more learning in to the coding world. Following our standard from earlier posts, we will start by recording a macro while deleting a shape and analyse the code.

Record the code

Open the workbook where the shape / picture (s) are inserted (if you don’t have, you can start by inserting a shape / picture of your choice in the workbook). For easy reference, I have a workbook in which I have various shapes ranging from a rectangle, a clip art, a star & a form button.

Now, let’s record a macro by deleting any one shape and see the code. (How to record a macro & how to access the recorded code)

Below is the code VBA shown me when I deleted the rectangle.

Sub Macro1()
'
' Macro1 Macro
'
 '
    ActiveSheet.Shapes.Range(Array("Rectangle 1")).Select
    Selection.Delete
End Sub

By analysing the above code, we could see ActiveSheet (a sheet that is active at the time of macro being recorded) has a property called Shapes and one of the shapes named Rectangle 1 was selected and deleted.

Let’s use this information. If you are looking to delete all the shapes in active sheet you can straight away use ActiveSheet object. However, if you are looking to apply this macro to a different sheet, you already know how to reference different sheet(s). We saw that Shapes object contains the collection of all shapes in the sheet, since we need not worry about a particular shape in our sheet (we are trying to delete all, remember), we just need to delete whatever is there in the Shapes object.

A quick look in VBA help on Shapes object will reveal the members of Shapes object. One of such member is SelectAll method which as the name implies, will select collection of the shapes that are part of Shapes object. Last line of above code will then be used to delete all the selected shapes.

So our first cut code looks something like below.

Sub Test()
ActiveSheet.Shapes.SelectAll ‘Selects all the shapes in active sheet
Selection.Delete ‘Delete the selected shapes
End Sub

Execute this macro and check. All the shapes in your active sheet must be gone. Clear?

You can apply the above code for all sheets in entire workbook as below.

Sub Test2()Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Sheets
    Sht.Shapes.SelectAll
    Selection.Delete
Next Sht
End Sub

There is another way you can delete all shapes in a sheet by using For Each loop. Check out the below code.

Sub Test3()
Dim Shp as Shape
For Each Shp In ActiveSheet.Shapes
    Shp.Delete
Next Shp
End Sub

I am assigning each shape to a variable and looping through all the shapes and deleting each one of them. However, in some versions of Excel, above code was said to remove the comments and AutoFilter as well. If you face such an issue, you can use the below code.

Sub Test4()
On Error Resume Next
ActiveSheet.DrawingObjects.Delete
End Sub

If you are deleting all the shapes, our first code is efficient however the second code will come to your rescue when you have to delete only certain types of shapes or only particular shapes.

Delete Specific Shapes

You have many types of shapes in your sheet and want to delete only certain type(s). For example: You want to delete all of the text boxes in the sheet.

Prerequisite to code above requirement is to know that each Shape in Excel contains a Type property which determines if a particular shape is text box or Image or a button etc.

By looking at the Type property values, you can notice text box type under Shapes is msoTextBox (also represented by value 17).  You can look at all the type object values in Microsoft Developer Network

Now, we just need to loop through the Shapes and see if any Shape Type is msoTextBox. If yes, then delete that Shape otherwise move to next shape. Simple? Let’s code it.

Sub test()
Dim Shp As Shape
For Each Shp In ActiveSheet.Shapes
    If Shp.Type = msoTextBox Then Shp.Delete
Next Shp
End Sub

Delete All but One Shape

Assume a situation where you have to delete all shapes but leave only one shape named Check Box 1. You cannot obviously use SelectAll method hence the above code with IF Then Else statement would help you. See the below code.

Sub Test5()
Dim Shp As Shape
For Each Shp In ActiveSheet.Shapes
    If Shp.Name <> "Check Box 1" Then Shp.Delete
Next Shp
End Sub

Look at the Shape object members. Read about the methods and properties supported by Shape object. You will master playing with all and all shapes in your workbook. Go on and do experiments with what all you can do using Shapes object. As I mentioned in one of previous post, learning VBA is all about how much you consult VBA help, everything you need to learn in VBA is out there. Happy Learning!!

PS: If you are trying to delete all the objects in an Excel sheet, you can do that without using VBA. Clicking on Go To Special > Objects will select all the objects and you can use Delete button to delete all of them in one go. You might also want to read our post on Working with Shapes.