How to Protect Specific Columns Using VBA in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Protetct Specific Columns in Excel

Steps:

  • Go to the Developer tab, then click Visual Basic.

Protetct Specific Columns in Excel

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

Protetct Specific Columns in Excel

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

Protect Active Rows in Excel using VBA

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

Protect Active Rows in Excel using VBA

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

Protect Active Columns in Excel using VBA

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

Protect Active Columns in Excel using VBA

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

Protect Multiple Rows in Excel using VBA

  • Go to the Developer tab, and click Visual Basic.

Protect Multiple Rows in Excel using VBA

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

Protect Multiple Rows in Excel using VBA

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

Protect Multiple Columns in Excel using VBA

  • Go to the Developer tab, then click Visual Basic.

Protect Multiple Rows in Excel using VBA

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

Protect Multiple Columns in Excel using VBA

  • Run the protect_multiple_column from the Macro window.

Protect Multiple Columns in Excel using VBA

  • 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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo