Verify various permissions in protected worksheet Excel

Protection

There are scenarios where you need to verify what all types of permissions are applied while protected a Sheet or Worksheet? In this article we will use VBA, VSTO (C#, VB) style code to trap protection levels applied on a sheet. Considering reader is aware of VSTO (Visual Studio Tools for Office) :

C# code example

private void btnVerifyProtection_Click(object sender, RibbonControlEventArgs e)
{
	//Get Sheet reference
	excel.Worksheet oWorksheet = Globals.ThisAddIn.Application.ActiveSheet;

	//check protection levels
	//Deleting column
	if (oWorksheet.Protection.AllowDeletingColumns == false)
		MessageBox.Show(@"Deleting Columns is not allowed");
	//Filters
	if (oWorksheet.Protection.AllowFiltering == false)
		MessageBox.Show(@"Filter is not allowed");
	//Deleting rows
	if (oWorksheet.Protection.AllowDeletingRows == false)
		MessageBox.Show(@"Deleting Rows is not allowed");
	//Formatting cell
	if (oWorksheet.Protection.AllowFormattingCells == false)
		MessageBox.Show(@"Cell Formatting is not allowed");
	//Formatting columns
	if (oWorksheet.Protection.AllowFormattingColumns == false)
		MessageBox.Show(@"Column formatting is not allowed");
	//Formatting rows
	if (oWorksheet.Protection.AllowFormattingRows == false)
		MessageBox.Show(@"Formatting rows is not allowed");
	//Allow Inserting column
	if (oWorksheet.Protection.AllowInsertingColumns == false)
		MessageBox.Show(@"Insert column is not allowed");
	//Insert Hyperlinks
	if (oWorksheet.Protection.AllowInsertingHyperlinks == false)
		MessageBox.Show(@"Hyperlinks insertion is not allowed");
	//Insert new row
	if (oWorksheet.Protection.AllowInsertingRows == false)
	MessageBox.Show(@"New row insertion is not allowed");
	//Sorting
	if (oWorksheet.Protection.AllowSorting == false)
		MessageBox.Show(@"Sorting is not allowed");
	//Pivot table creation
	if (oWorksheet.Protection.AllowUsingPivotTables == false)
		MessageBox.Show(@"Piovt Table creaton is not allowed");
}

VB.Net code example

Private Sub VerifyProtections_Clicksender As Object, e As RibbonControlEventArgs) Handles VerifyProtections.Click

	'Get Sheet reference
	Dim oWorksheet As excel.Worksheet 
	oWorksheet = Globals.ThisAddIn.Application.ActiveSheet

	'check protection levels
	'Deleting column
	If oWorksheet.Protection.AllowDeletingColumns = False Then
		MessageBox.Show(@"Deleting Columns is not allowed")
	End If
	'Filters
	If oWorksheet.Protection.AllowFiltering = False Then
		MessageBox.Show(@"Filter is not allowed")
	End If
	'Deleting rows
	If oWorksheet.Protection.AllowDeletingRows = False Then
		MessageBox.Show(@"Deleting Rows is not allowed")
	End If
	'Formatting cell
	If oWorksheet.Protection.AllowFormattingCells = False Then
		MessageBox.Show(@"Cell Formatting is not allowed")
	End If
	'Formatting columns
	If oWorksheet.Protection.AllowFormattingColumns = False Then
		MessageBox.Show(@"Column formatting is not allowed")
	End If
	'Formatting rows
	If oWorksheet.Protection.AllowFormattingRows = False Then
		MessageBox.Show(@"Formatting rows is not allowed")
	End If
	'Allow Inserting column
	If oWorksheet.Protection.AllowInsertingColumns = False Then
		MessageBox.Show(@"Insert column is not allowed")
	End If
	'Insert Hyperlinks
	If oWorksheet.Protection.AllowInsertingHyperlinks = False Then
		MessageBox.Show(@"Hyperlinks insertion is not allowed")
	End If
	'Insert new row
	If oWorksheet.Protection.AllowInsertingRows = False Then
		MessageBox.Show(@"New row insertion is not allowed")
	End If
	'Sorting
	If oWorksheet.Protection.AllowSorting = False Then
		MessageBox.Show(@"Sorting is not allowed")
	End If
	'Pivot table creation
	If oWorksheet.Protection.AllowUsingPivotTables = False Then
		MessageBox.Show(@"Piovt Table creaton is not allowed")
	End If
End Sub

VBA code example

Private Sub VerifyProtectionsExample()
    'Get Sheet reference
    Dim oWorksheet As Excel.Worksheet
    Set oWorksheet = ActiveSheet

    'check protection levels
    'Deleting column
    If oWorksheet.Protection.AllowDeletingColumns = False Then
        MsgBox "Deleting Columns is not allowed"
    End If
    'Filters
    If oWorksheet.Protection.AllowFiltering = False Then
        MsgBox "Filter is not allowed"
    End If
    'Deleting rows
    If oWorksheet.Protection.AllowDeletingRows = False Then
        MsgBox "Deleting Rows is not allowed"
    End If
    'Formatting cell
    If oWorksheet.Protection.AllowFormattingCells = False Then
        MsgBox "Cell Formatting is not allowed"
    End If
    'Formatting columns
    If oWorksheet.Protection.AllowFormattingColumns = False Then
        MsgBox "Column formatting is not allowed"
    End If
    'Formatting rows
    If oWorksheet.Protection.AllowFormattingRows = False Then
        MsgBox "Formatting rows is not allowed"
    End If
    'Allow Inserting column
    If oWorksheet.Protection.AllowInsertingColumns = False Then
        MsgBox "Insert column is not allowed"
    End If
    'Insert Hyperlinks
    If oWorksheet.Protection.AllowInsertingHyperlinks = False Then
        MsgBox "Hyperlinks insertion is not allowed"
    End If
    'Insert new row
    If oWorksheet.Protection.AllowInsertingRows = False Then
        MsgBox "New row insertion is not allowed"
    End If
    'Sorting
    If oWorksheet.Protection.AllowSorting = False Then
        MsgBox "Sorting is not allowed"
    End If
    'Pivot table creation
    If oWorksheet.Protection.AllowUsingPivotTables = False Then
        MsgBox "Piovt Table creaton is not allowed"
    End If
End Sub

Next >> Understand various views in a workbook

Leave a Reply

Your email address will not be published.