Need to auto hide columns when exiting!

[spindlewood]

Hi.

I have a worksheet that is shared between employee's which has some columns that has some sensitive data shown.

I need these to be hidden when the sheet is opened, but when the sheet is unlocked by myslef, I can then unhide the columns.

So far I have managed to do... However I was thinking, if I forgot to hide these confidential columns when exiting, they would be seen if someone else opened t.

So, in an ideal world, when exiting, I need the sheet to auto hide 3 columns so that when the next person logs on they are not visible...

Any ideas?


Peter

[zzzeddy]

Hi Peter

You just need to add some code in the Workbook module and save you file as a macro-enabled file e.g. .xlsm format

This code would make sure columns [H:M] are hidden on sheet named "xxx" ; adjust to suit..

Please Login or Register  to view this content.

zeddy

[AlphaFrog]

Put this in the ThisWorkbook code module

Change the sheet name, columns, and sheet password to suit

Please Login or Register  to view this content.

[zzzeddy]

Hi Peter

I need these to be hidden when the sheet is opened

..you can also use the Workbook_Open() event to do this

zeddy

[spindlewood]

Thanks Guys, will give it a try now!

[spindlewood]

Thanks.

I have tried this as shown below

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

With ThisWorkbook.Sheets("Sheet1")
.Unprotect Password:="Secret"
.Columns("OD:OS").Hidden = True
.Protect Password:="Secret"
End With

End Sub

But when I then go to save the document it says

Runtime Error 9

Subscript out of range



Any ideas?

[zzzeddy]

Change "Sheet1" to the name in the tab for your actual worksheet.

zeddy

[spindlewood]

Thanks Mate...