CODEWITHSUNDEEP
excelvba
PeanutsMonkey
PeanutsMonkey

Reputation: 7095

How do i disable unhiding columns in Excel using VBA?

I have a number of columns hidden in Excel and would like to prevent users from using the format control option as well as context menu from unhiding these columns. How can i do so with VBA?

Upvotes: 2

Views: 11475

Answers (3)

Hugo
Hugo

Reputation: 113

Just hide the headings, and that makes it harder to unhide the columns

Upvotes: 0

psubsee2003
psubsee2003

Reputation: 8741

In terms of actually preventing users from unhiding columns via the menu options, ultimately the options in VBA are limited as VBA doesn't have any special magic. It would be utilizing the exact same functionality as you are using in the Excel application, so the same limitations that you are finding in using the Sheet protection features will be the same in a VBA solution.

There may be some VBA-like solutions that could give you close to what you need, but they won't be perfect and would be very easily defeated by simply disabling macros, rendering your security measures completely ineffective.

One option is to rehide the column anytime the selection changes, however this won't prevent user from selecting the entire column and unhiding the row, but as soon as they select a cell, it will be hidden again. you can prevent someone from selecting the cell by locking only the cells in this column and protecting the sheet to prevent selection of locked cells. You won't stop anyone from unhiding and seeing the data, but you will prevent them from modifying the data and will make it difficult to see the specific formulas.

The code to do that is here and goes in the Worksheet code section (where you capture worksheet events)

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 

    Columns("F").EntireColumn.Hidden = True 

End Sub

Another option would to disable the appropriate menu options in the Workbook, but this doesn't remove the functionality, just makes it more difficult to access.

The code to do that is here and goes in the Workbook code section (where you capture Workbook-level events)

Private Sub Workbook_BeforeClose(Cancel As Boolean) 

    //Re-enable in Column Context Menu
    Application.CommandBars("Column").Controls("Unhide").Enabled = True 

    //Re-enable in Ribbon Format menu
    Application.CommandBars("Format").Controls("???").Enabled = True 

End Sub 

Private Sub Workbook_Open() 

    //Disable in Column Context Menu
    Application.CommandBars("Column").Controls("Unhide").Enabled = False 

    //Disable in Ribbon Format menu 
    Application.CommandBars("Format").Controls("???").Enabled = True 

End Sub

I haven't found the right combination of Menu/Submenu items to disable to menu item from the "Format" menu (hence the "???"), but you would need to do it there as well. Being a macro solution, you are still dependent on someone enabling macros when opening the Workbook. Plus you are physically disabling the menu item so you remove the ability to selection those options for any other sheet in the workbook and any other open workbook, not just the specific columns you want.

This is also very version dependent I've found in the past, as menu options have changed in versions of Excel, so what worked in Excel 2007 or 2010, may not work the same in 2013, which means you now need to include a version check in the code and enabled/disable based on the appropriate names for the specific verson.

Additionally, if Excel crashes and the BeforeClose code does not run, you may not get the menu options re-enabled correctly.

Ultimately, the best fail safe is not to hide the columns and to move any sensitive information and formulas into another sheet, which you can then hide via VBA to make it almost invisible from the Excel application.

From the Visual Basic Editor, click on a sheet in the Project pane, then from the property pane below, click Visible and change the option to xlSheetVeryHidden

enter image description here

The sheet will now be hidden but will not be visible from the Sheet/Unhide menu, and will only be accessible from the Visual Basic editor (where you have to unhide it again to see it). The best part of this solution is, while it is only accessible from the Visual Basic editor, it is NOT a VBA solution, so will not depend on someone enabling macros to function.

Upvotes: 4

Tim
Tim

Reputation: 1

Once the columns are hidden protect the sheet and ensure that Format Cells/Rows/Columns are not ticked in the Protect Sheet dialog box.

Upvotes: 0

Related Questions