How can I prevent users from unhiding columns in VBA?

Preventing users from unhiding columns in VBA involves setting the column's "Hidden" property to True and then protecting the worksheet. Here’s how you can accomplish this:

1. Hiding Columns via VBA:

- Use the Columns property to select the column(s) you want to hide and set their Hidden property to True. For example, to hide column "A", you would use:

Columns("A").Hidden = True

- You can hide multiple columns by specifying a range. For example, to hide columns "A" to "C":

Columns("A:C").Hidden = True

2. Protecting the Worksheet:

- After hiding the columns, protect the worksheet to prevent users from manually unhiding them. This is done using the Protect method of the Worksheet object.

- To protect the worksheet with default options (allowing actions like selecting cells but preventing structural changes like unhiding columns):

ThisWorkbook.Sheets("Sheet1").Protect

- If you want to allow specific actions while still preventing column unhiding, you can use specific protection parameters. For instance, to allow users to select locked cells:

ThisWorkbook.Sheets("Sheet1").Protect UserInterfaceOnly:=True, AllowSelectingLockedCells:=True

- Note that UserInterfaceOnly:=True is useful if you have VBA code that needs to modify the sheet; it allows code to operate even when the sheet is protected, but protects against user changes.

3. Complete Example VBA Code:

Here’s a complete code example combining hiding and protecting, applied to "Sheet1" and hiding columns "A" and "B":

Sub PreventUnHideColumns()
  With ThisWorkbook.Sheets("Sheet1")
    .Columns("A:B").Hidden = True
    .Protect UserInterfaceOnly:=True, AllowSelectingLockedCells:=True
  End With
End Sub

4. Important Considerations:

- Password Protection: While you can set a password to protect a sheet, it's not considered strong security. Consider alternative options if sensitive data is involved.

- User Interface Only Protection: If your VBA code will need to manipulate the hidden columns after protection, use UserInterfaceOnly:=True in the Protect method. This allows VBA to run while preventing manual changes.

- Unprotecting: You need to unprotect the sheet if you need to make manual changes through the interface or manipulate hidden columns through VBA. Remember to re-protect after.

By following these steps, you can prevent users from easily unhiding specific columns in your Excel worksheets using VBA. This provides a method for controlling what data is viewable.

Learn more about this