In all types of day-to-day use of Excel, we often need to lock certain columns and rows so that other people or users can’t change the contents of those columns and rows. Basically, we need to protect or restrict them from editing. How we can protect those specific columns and rows in Excel using VBA macro is discussed here with elaborate examples.
How to Protect Specific Columns Using VBA in Excel: Step-by-Step Procedures
For the demonstration purpose, we are going to use the below dataset. In this dataset, we got marks from different students in various subjects. A VBA macro can be used to protect specific columns without loads of trouble. And without a huge amount of time being wasted.
Steps:
- Go to the Developer tab, then click Visual Basic.
- Click Insert > Module.
- We are going to protect column C in the worksheet.
- In the Module window, enter the following code to protect specific columns:
Sub protect_specific_column()
Range("C5").EntireColumn.Select
Selection.Locked = True
Selection.FormulaHidden = True
ActiveSheet.Protect DrawingObjects:=False, _
Contents:=True, Scenarios:=False, _
AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingRows:=True, _
AllowInsertingHyperlinks:=True, _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, _
AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
End Sub
- Close the window.
- Go to the View tab > Macros > View Macros.
- Select the protect_specific_column macro and click Run.
- After clicking Run you will notice the mentioned columns are no longer available to edit. If you try to edit any of those cells, there will be a warning sign as below.
To unprotect the cell, you just need to use the Unprotect Sheet feature. Which is in the cells group in the Home tab. In this way, you can protect specific columns in Excel with VBA codes.
Code Breakdown
- Sub protect_specific_column(): This is the name of the Subroutine that we are going to use.
- Range(“C5”).EntireColumn.Select: This means selecting the entire column in which cell C5 is situated.
- Selection.Locked = True: This property locks the selected part of the worksheet.
- Selection.FormulaHidden = True: This property returns that the formula will be hidden when the worksheet is protected.
- ActiveSheet.Protect DrawingObjects:=False: This will protect shapes for True value The default value is False.
- Contents:=True: While placed on a chart, this protects the entire chart. For a worksheet, this protects the cells that are locked. The value True is set as the default.
- Scenarios:=False: This argument is only applicable to worksheets. True is set as the default.
- AllowFormattingCells:=True: True provides the capability to user format any cell on an encrypted spreadsheet. False is the default setting.
- AllowFormattingColumns:=True: True provides the capability to user format any column on an encrypted spreadsheet. False is the default setting.
- AllowFormattingRows:=True: True provides the capability to user format any rows on an encrypted spreadsheet. False is the default setting.
- AllowInsertingColumns:=True: True provides the capability to the user to insert any columns on an encrypted spreadsheet. False is the default setting.
- AllowInsertingRows:=True: True provides the capability to the user to insert any rows on an encrypted spreadsheet. False is the default setting.
- AllowInsertingHyperlinks:=True: True provides the capability to the user to insert any hyperlinks on an encrypted spreadsheet. False is the default setting.
- AllowDeletingColumns:=True: True provides the capability to the user to delete any columns on an encrypted spreadsheet. False is the default setting.
- AllowDeletingRows:=True: True provides the capability to user delete any rows on an encrypted spreadsheet. False is the default setting.
- AllowSorting:=True: True provides the capability for users to sort on an encrypted spreadsheet. False is the default setting.
- AllowFiltering:=True: True provides the capability for users to apply filters on an encrypted spreadsheet. False is the default setting.
- AllowUsingPivotTables:=True: True provides the capability for users to use pivot table reports on an encrypted spreadsheet. False is the default setting.
Protect Active Row and Column with Excel VBA
With this method, we can protect or restrict rows and columns in Excel while selecting a cell. Compared to previous methods, where we needed to edit the code to select which column or row we wanted to protect. Here we just select the cell of which column or row we want to protect.
1. Protect Active Row in Excel
A VBA macro can be used to protect a selected row without a load of trouble and a huge amount of time being wasted.
Steps:
- Go to the Developer tab, then click Visual Basic.
- Click Insert > Module.
- In the Module window, enter the following code.
Sub protect_active_row()
ActiveCell.EntireRow.Select
Selection.Locked = True
Selection.FormulaHidden = True
ActiveSheet.Protect DrawingObjects:=False, _
Contents:=True, Scenarios:=False, _
AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingRows:=True, _
AllowInsertingHyperlinks:=True, _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, _
AllowFiltering:=True, AllowUsingPivotTables:=True
End Sub
- Then close the window.
- Go to the View tab > Macros > View Macros.
- Select the macro named protect_active_row and click Run.
- After clicking Run, you will notice the mentioned column is no longer available to edit. If you try to edit any of those cells, there will be a warning sign shown below.
Code Breakdown
- Sub protect_active_row(): This is the name of the Subroutine that we are going to use.
- ActiveCell.EntireRow.Select: This means selecting the entire row in which the cell is selected right now.
- Selection.Locked = True: This property locks the selected part of the worksheet.
- Selection.FormulaHidden = True: This property returns that the formula will be hidden when the worksheet is protected.
- ActiveSheet.Protect DrawingObjects:=False: This will protect shapes for True value. The default value is False.
- Contents:=True: While placed on a chart, this protects the entire chart. For a worksheet, this protects the cells that are locked. The value True is set as the default.
- Scenarios:=False: True to preserve scenarios. This argument is only applicable to worksheets. True is set as the default.
- AllowFormattingCells:=True: True provides the capability to user format any cell on an encrypted spreadsheet. False is the default setting.
- AllowFormattingColumns:=True: True provides the capability to user format any column on an encrypted spreadsheet. False is the default setting.
- AllowFormattingRows:=True: True provides the capability to user format any rows on an encrypted spreadsheet. False is the default setting.
- AllowInsertingColumns:=True: True provides the capability to the user to insert any columns on an encrypted spreadsheet. False is the default setting.
- AllowInsertingRows:=True: True provides the capability to the user to insert any rows on an encrypted spreadsheet. False is the default setting.
- AllowInsertingHyperlinks:=True: True provides the capability to the user to insert any hyperlinks on an encrypted spreadsheet. False is the default setting.
- AllowDeletingColumns:=True: True provides the capability to user delete any columns on an encrypted spreadsheet. False is the default setting.
- AllowDeletingRows:=True: True provides the capability to user delete any rows on an encrypted spreadsheet. False is the default setting.
- AllowSorting:=True: True provides the capability for users to sort on an encrypted spreadsheet. False is the default setting.
- AllowFiltering:=True: True provides the capability for users to apply filters on an encrypted spreadsheet. False is the default setting.
- AllowUsingPivotTables:=True: True provides the capability for users to use pivot table reports on an encrypted spreadsheet. False is the default setting.
2. Protect Active Column in Excel
A VBA macro can be used to protect a certain column without a load of trouble and a huge amount of time being wasted.
Steps:
- Go to the Developer tab, then click Visual Basic.
- Select Insert > Module.
- In the Module window, enter the following code.
Sub protect_active_column()
ActiveCell.EntireColumn.Select
Selection.Locked = True
Selection.FormulaHidden = True
ActiveSheet.Protect DrawingObjects:=False, _
Contents:=True, Scenarios:=False, _
AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, _
AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, _
AllowDeletingColumns:=True, AllowDeletingRows:=True, _
AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
End Sub
- Close the window and go to the View tab > Macros > View Macros.
- Select protect_active_column macro and click Run.
- After clicking Run, you will notice the mentioned column is no longer available to edit. If you try to edit any of those cells, there will be a warning sign shown below.
- This is how you can Protect specific active columns and rows using VBA in Excel.
Code Breakdown
- Sub protect_active_column(): This is the name of the Subroutine that we are going to use.
- ActiveCell.EntireColumn.Select: This means selecting the entire column in which the cell is selected right now
- Selection.Locked = True: This property locks the selected part of the worksheet.
- Selection.FormulaHidden = True: This property returns that the formula will be hidden when the worksheet is protected.
- ActiveSheet.Protect DrawingObjects:=False: This will protect shapes for True value. The default value is False.
- Contents:=True: While placed on a chart, this protects the entire chart. For a worksheet, this protects the cells that are locked. The value True is set as the default.
- Scenarios:=False: True to preserve scenarios. This argument is only applicable to worksheets. True is set as the default.
- AllowFormattingCells:=True: True provides the capability to user format any cell on an encrypted spreadsheet. False is the default setting.
- AllowFormattingColumns:=True: True provides the capability to user format any column on an encrypted spreadsheet. False is the default setting.
- AllowFormattingRows:=True: True provides the capability to user format any rows on an encrypted spreadsheet. False is the default setting.
- AllowInsertingColumns:=True: True provides the capability to the user to insert any columns on an encrypted spreadsheet. False is the default setting.
- AllowInsertingRows:=True: True provides the capability to the user to insert any rows on an encrypted spreadsheet. False is the default setting.
- AllowInsertingHyperlinks:=True: True provides the capability to the user to insert any hyperlinks on an encrypted spreadsheet. False is the default setting.
- AllowDeletingColumns:=True: True provides the capability to user delete any columns on an encrypted spreadsheet. False is the default setting.
- AllowDeletingRows:=True: True provides the capability to user delete any rows on an encrypted spreadsheet. False is the default setting.
- AllowSorting:=True: True provides the capability for users to sort on an encrypted spreadsheet. False is the default setting.
- AllowFiltering:=True: True provides the capability for users to apply filters on an encrypted spreadsheet. False is the default setting.
- AllowUsingPivotTables:=True: True provides the capability for users to use pivot table reports on an encrypted spreadsheet. False is the default setting.
Protect Multiple Rows and Columns with Excel VBA
We have already shown you how to protect specific columns and rows with Excel VBA. Following this method can help you to protect multiple rows or columns at a time. Compared to the previous method, where you needed to protect rows and columns one by one, this approach is quite hassle-free.
1. Protect Multiple Rows
In this approach, several rows are selected and protected simultaneously, as defined in the VBA macros.
Steps:
- We are going to protect the 6,7,8 rows shown in the image below.
- Go to the Developer tab, and click Visual Basic.
- Click Insert > Module.
- In the Module window, enter the following code.
Sub protect_multiple_row()
Rows("6:8").Select
Selection.Locked = True
Selection.FormulaHidden = True
ActiveSheet.Protect DrawingObjects:=False, _
Contents:=True, Scenarios:=False, _
AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingRows:=True, _
AllowInsertingHyperlinks:=True, _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, _
AllowFiltering:=True, AllowUsingPivotTables:=True
End Sub
- Closing the window go to the View tab > Macros > View Macros.
- Select and run the protect_multiple_row macro.
- After clicking Run, you will notice the mentioned row is no longer available to edit. if you try to edit any of those cells, there will be a warning sign shown below.
Code Breakdown
- Sub protect_multiple_row(): This is the name of the Subroutine that we are going to use.
- Rows(“6:8”).Select: This means selecting the entire row from 6 to 8.
- ActiveCell.EntireColumn.Select: This means selecting the entire column in which the cell is selected right now
- Selection.Locked = True: This property locks the selected part of the worksheet.
- Selection.FormulaHidden = True: This property returns that the formula will be hidden when the worksheet is protected.
- ActiveSheet.Protect DrawingObjects:=False: This will protect shapes for True value. The default value is False.
- Contents:=True: While placed on a chart, this protects the entire chart. For a worksheet, this protects the cells that are locked. The value True is set as the default.
- Scenarios:=False: True to preserve scenarios. This argument is only applicable to worksheets. True is set as the default.
- AllowFormattingCells:=True: True provides the capability to user format any cell on an encrypted spreadsheet. False is the default setting.
- AllowFormattingColumns:=True: True provides the capability to user format any column on an encrypted spreadsheet. False is the default setting.
- AllowFormattingRows:=True: True provides the capability to user format any rows on an encrypted spreadsheet. False is the default setting.
- AllowInsertingColumns:=True: True provides the capability to the user to insert any columns on an encrypted spreadsheet. False is the default setting.
- AllowInsertingRows:=True: True provides the capability to the user to insert any rows on an encrypted spreadsheet. False is the default setting.
- AllowInsertingHyperlinks:=True: True provides the capability to the user to insert any hyperlinks on an encrypted spreadsheet. False is the default setting.
- AllowDeletingColumns:=True: True provides the capability to user delete any columns on an encrypted spreadsheet. False is the default setting.
- AllowDeletingRows:=True: True provides the capability to user delete any rows on an encrypted spreadsheet. False is the default setting.
- AllowSorting:=True: True provides the capability for users to sort on an encrypted spreadsheet. False is the default setting.
- AllowFiltering:=True: True provides the capability for users to apply filters on an encrypted spreadsheet. False is the default setting.
- AllowUsingPivotTables:=True: True provides the capability for users to use pivot table reports on an encrypted spreadsheet. False is the default setting.
2. Protect Multiple Columns
In this approach, several columns are selected and protected simultaneously, as defined in the VBA macros.
Steps:
- We are going to protect column C as shown in the image below.
- Go to the Developer tab, then click Visual Basic.
- Click Insert > Module.
- In the Module window, enter the following code.
Sub protect_multiple_column()
Columns("C:D").Select
Selection.Locked = True
Selection.FormulaHidden = True
ActiveSheet.Protect DrawingObjects:=False, _
Contents:=True, Scenarios:=False, _
AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingRows:=True, _
AllowInsertingHyperlinks:=True, _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, _
AllowFiltering:=True, AllowUsingPivotTables:=True
End Sub
- Close the window.
- Go to the View tab > Macros > View Macros.
- Run the protect_multiple_column from the Macro window.
- After clicking Run, you will notice the mentioned row is no longer available to edit. if you try to edit any of those cells, there will be a warning sign shown below.
- This is how you can protect specific multiple columns in Excel using VBA.
Code Breakdown
- Sub protect_multiple_column(): This is the name of the Subroutine that we are going to use.
- Columns(“C:D”).Select: This means selecting columns C and D only.
- ActiveCell.EntireColumn.Select: This means selecting the entire column in which the cell is selected right now
- Selection.Locked = True: This property locks the selected part of the worksheet.
- Selection.FormulaHidden = True: This property returns that the formula will be hidden when the worksheet is protected.
- ActiveSheet.Protect DrawingObjects:=False: This will protect shapes for True value. The default value is False.
- Contents:=True: While placed on a chart, this protects the entire chart. For a worksheet, this protects the cells that are locked. The value True is set as the default.
- Scenarios:=False: True to preserve scenarios. This argument is only applicable to worksheets. True is set as the default.
- AllowFormattingCells:=True: True provides the capability to user format any cell on an encrypted spreadsheet. False is the default setting.
- AllowFormattingColumns:=True: True provides the capability to user format any column on an encrypted spreadsheet. False is the default setting.
- AllowFormattingRows:=True: True provides the capability to user format any rows on an encrypted spreadsheet. False is the default setting.
- AllowInsertingColumns:=True: True provides the capability to the user to insert any columns on an encrypted spreadsheet. False is the default setting.
- AllowInsertingRows:=True: True provides the capability to the user to insert any rows on an encrypted spreadsheet. False is the default setting.
- AllowInsertingHyperlinks:=True: True provides the capability to the user to insert any hyperlinks on an encrypted spreadsheet. False is the default setting.
- AllowDeletingColumns:=True: True provides the capability to user delete any columns on an encrypted spreadsheet. False is the default setting.
- AllowDeletingRows:=True: True provides the capability to user delete any rows on an encrypted spreadsheet. False is the default setting.
- AllowSorting:=True: True provides the capability for users to sort on an encrypted spreadsheet. False is the default setting.
- AllowFiltering:=True: True provides the capability for users to apply filters on an encrypted spreadsheet. False is the default setting.
- AllowUsingPivotTables:=True: True provides the capability for users to use pivot table reports on an encrypted spreadsheet. False is the default setting.
Download Practice Workbook
Download this practice workbook below.
Conclusion
To sum it up, the question of how to protect specific columns in Excel VBA is answered here by using a simple VBA macro. We expand our discussion to protect multiple rows and columns. Furthermore, we also discussed how we can protect active rows and columns in Excel. All of these methods require prior VBA-related knowledge to understand from scratch.
For this problem, a macro-enabled workbook is attached where you can practice these methods.
Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the community will be highly appreciated.
Further Readings
- How to Lock and Unlock Cells in Excel Using VBA
- Excel VBA to Protect Range of Cells
- Excel VBA to Lock Cells without Protecting Sheet
- Lock a Cell after Data Entry Using Excel VBA with Message Box Notification Before Locking
- How to Hide Formula in Excel Using VBA
