How to Protect Specific Columns Using VBA in Excel

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 that columns and rows. so basically we need to protect or restrict them from editing. How we can protect those specific columns and rows in Excel using VBA macro are discussed here with elaborate examples.


Download Practice Workbook

Download this practice workbook below.


Steps to Protect Specific Columns Using VBA in Excel

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

  • First, go to the Developer tab, then click Visual Basic.

Protetct Specific Columns in Excel

  • Then 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
  • Then close the window.
  • After that, go to the View tab > Macros > View Macros.

Protetct Specific Columns in Excel

  • After clicking View Macros, select the macros that you created just now. The name here is protect_specific_column. Then 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.

Breakdown of the VBA Code

  • 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 user insert any columns on an encrypted spreadsheet. False is the default setting.
  • AllowInsertingRows:=True: True provides the capability to user insert any rows on an encrypted spreadsheet. False is the default setting.
  • AllowInsertingHyperlinks:=True: True provides the capability to user 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 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 want 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

  • First, go to the Developer tab, then click Visual Basic.

Protect Active Rows in Excel using VBA

  • Then 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.
  • After that, go to the View tab > Macros > View Macros.

Protect Active Rows in Excel using VBA

  • After clicking View Macros, select the macros that you created just now. The name here is protect_active_row. Then 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.

  • To unprotect the cell, you just need to use the Unprotect Sheet feature. That is in the cells group in the Home tab.

Breakdown of the VBA Code

  • 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 user insert any columns on an encrypted spreadsheet. False is the default setting.
  • AllowInsertingRows:=True: True provides the capability to user insert any rows on an encrypted spreadsheet. False is the default setting.
  • AllowInsertingHyperlinks:=True: True provides the capability to user 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.

Read More: How to Protect Columns in Excel (6 Easy Methods)


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

  • First, go to the Developer tab, then click Visual Basic.

Protect Active Columns in Excel using VBA

  • Then click 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
  • Then close the window.
  • After that, go to the View tab > Macros > View Macros.

Protect Active Columns in Excel using VBA

  • After clicking View Macros, select the macros that you created just now. The name here is protect_active_column. Then 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.

To unprotect the cell, you just need to use the Unprotect Sheet feature. Which is in the cells group in the Home tab.

Breakdown of the VBA Code

  • 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 user insert any columns on an encrypted spreadsheet. False is the default setting.
  • AllowInsertingRows:=True: True provides the capability to user insert any rows on an encrypted spreadsheet. False is the default setting.
  • AllowInsertingHyperlinks:=True: True provides the capability to user 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.

Read More: How to Protect Columns in Excel without Protecting Sheet (4 Easy Ways)


Protect Multiple Rows and Columns with Excel VBA

Now, we have already shown you 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

  • To do this, first, go to the Developer tab, then click Visual Basic.

Protect Multiple Rows in Excel using VBA

  • Then 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
  • Then close the window.
  • After that, go to the View tab > Macros > View Macros.

Protect Multiple Rows in Excel using VBA

  • After clicking View Macros, select the macros that you created just now. The name here is protect_multiple_row. Then click Run.

  • 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 and rows using VBA in Excel.

To unprotect the cell, you just need to use the Unprotect Sheet feature. Which is in the cells group in the Home tab.

Breakdown of the VBA Code

  • 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 user insert any columns on an encrypted spreadsheet. False is the default setting.
  • AllowInsertingRows:=True: True provides the capability to user insert any rows on an encrypted spreadsheet. False is the default setting.
  • AllowInsertingHyperlinks:=True: True provides the capability to user 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.

Read More: How to Protect Hidden Columns in Excel (4 Easy Methods)


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 columns C as shown in the image below.

Protect Multiple Columns in Excel using VBA

  • To do this, first, go to the Developer tab, then click Visual Basic.

Protect Multiple Rows in Excel using VBA

  • Then 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
  • Then close the window.
  • After that, go to the View tab > Macros > View Macros.

Protect Multiple Columns in Excel using VBA

  • After clicking View Macros, select the macros that you created just now. The name here is protect_multiple_column. Then click Run.

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.

To unprotect the cell, you just need to use the Unprotect Sheet feature. Which is in the cells group in the Home tab.

Breakdown of the VBA Code

  • 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 user insert any columns on an encrypted spreadsheet. False is the default setting.
  • AllowInsertingRows:=True: True provides the capability to user insert any rows on an encrypted spreadsheet. False is the default setting.
  • AllowInsertingHyperlinks:=True: True provides the capability to user 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.

Conclusion

To sum it up, the question “how to protect specific columns in Excel VBA” is answered here by using a simple VBA macro. We expand our discussion to protecting 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 Exceldemy community will be highly appreciable


Further Reading

Rubayed Razib Suprov
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo