Hide and Unhide Columns

**HangMan** · 04-21-2015, 03:30 AM

I have a sheet containing numerous columns. Some of the columns contain data that I don't want the user to see. Obviously I can simply hide the respective columns but this also means the user can easily unhide them.

Is there a way to create a password protected macro (I don't know if macro's can be password protected?) that allows specific columns to be specified which can be hidden when run, e.g. columns F, I, K, L and J and then a second macro (if this is the best option) to unhide the columns.

This would need to allow for the fact that the user 'could' insert their own additional columns into the sheet, so ideally the column references to be hidden/unhidden would need to automatically update in the macro (if this is even possible) to accommodate this.

Obviously any data in the hidden columns would need to be sorted along with the rest of the sheet, should the user decide they want to sort by any of the visible columns.

Many thanks

**TMS** · 04-21-2015, 06:37 AM

It may be possible but it won't be easy. Firstly, if it is macro driven, then the user MUST enable macros. If they don't, then there is little/no protection. So, you have to make the workbook unusable if macros are not enabled. That requires a process of hiding/unhiding sheets on open, save and close of the workbook. Lots of examples around of using a "Splash" screen.

Second, it is possible to hide columns using their column names. However, once again, this can be circumvented if the user changes column header names ... and you don't have to be able to see what is in a cell to change or delete it.

Thirdly; yes, the hidden columns will be sorted when you sort the data.

Fourth, it would be better to put sensitive information and calculations on a separate sheet and use a key field to link to it ... but that would depend on how the data is created and structured.

Lastly, Excel is not a secure environment. Whatever you do to protect the "sensitive" information, it will be possible to unprotect it and make it visible.

All in all, don't hold your breath for a solution that is safe and secure. There is unlikely to be one.

Regards, TMS

**HangMan** · 04-21-2015, 07:01 AM

Hi TMS,

In terms of 'Safe and Secure' regarding 'Sensitive Information'. The data is all internal and my use of the word 'Sensitive' was perhaps not the best choice as in it can be seen by all without problem, but the logic of what I want to achieve is to simply restrict 'when' we make the information avbailable, hence the hidden columns approach, which I appreciate isn't foolproof. It is not the end of the world if the user were to see the hidden coumns, but I simply want to delay when.

In response to your observations:

With respect to the user enabling macro's, that isn't an issue as I can ensure they do that.

Columns headers, these will be fixed, which of course doesn't prevent the user changing them, but they will have no need to do so. If it became an issue I could Data Validate the column headers or protect the cells.

I don't know if that changes your view on whether this is possible to achieve?

Many thanks

**JasperD** · 04-21-2015, 07:10 AM

It's possible - upload a sample workbook.

Re: TMS's remarks

1) Agreed - use a splash screen, you cannot ensure they'll enable macros if you just trust their word on it ;-)
2) Column header names can be secured with a worksheet_change event that resets the column header on each change. Alternatively, you can protect the cells & worksheet.
3) Sorting if you set the sort area to include the hidden columns. Otherwise they won't (for example, if you'd have an empty column in there).
4 & 5) I side with TMS

**HangMan** · 04-21-2015, 07:20 AM

Okay, simplistic sample attached.

Assume I want to 'hide' columns B, F & G with the ability to hide/Unhide using a macro as discussed...

I take on board your comments...