Question about locking/hiding columns/tabs on a sharepoint file

[FrumpyJones]

So, my question is slightly leveled. I apologize in advance if it sounds crazy.

We have a file that only three people(managers) really work on. But they want to share everything but one column to the world. Up until now, one of the managers is literally copying the file once a month, deleting the column, and then sending me the file to put on the sharepoint site as read only. These managers want the supervisors to check the "snapshot" file, and email them any problems they see in the file, but these managers are constantly working the real/live file and about 80% of the emails they are getting regarding the "snapshot" file have actually already been fixed (50+ supervisors looking at this file).

The managers, not surprisingly, have voiced a frustration with having to check on these edit requests that have already been worked on. I had two possible ideas to put the live file on the sharepoint and lock it down, but am unsure how this would work in a sharepoint environment:

• Hide the column and lock the file down with a password. (MY CONCERN: if one of the managers comes into the live file and uses the password to access the hidden column, that people with read-only access who are also viewing the file, can now see the unlocked/un-hidden column)
• Create a Tab that holds the full live file, and hide/lock/password that tab, and have another tab that has a view/copy of the columns from the hidden tab, minus the one column (MY CONCERN: Same as the other idea. if a manager unhides the tab, will people who COULDN'T unhide it themselves now be able to see it?)

Hope this all makes sense. I could try to test this by creating a file and doing all this, but then I would need a manager and a supervisor to test at the same time, and if I try to tell you how hard that would be... so I'm hoping that someone has an experience with a scenario like this.

Thanks

 

[Candyman8019]

I've had that exact experience with this scenario. Now, depending on the exact requirement...if other users can see that hidden column, but not edit it...would that be acceptable? If so you can use a worksheet_change event that checks if the editing user is one of the three Managers, then do nothing, else undo the change.

 

[FrumpyJones]

I've had that exact experience with this scenario. Now, depending on the exact requirement...if other users can see that hidden column, but not edit it...would that be acceptable? If so you can use a worksheet_change event that checks if the editing user is one of the three Managers, then do nothing, else undo the change.

They want people to not see the column at all, but THEY can see the column and edit it as need be. My thought is that once a manger unprotects the sheet to edit. Anyone who comes into the file until the manger (hopefully remembers to) hide and protect the column again, will be able to see this column that should not be seen. I think these managers just have to suffer with the emails.

 

[Candyman8019]

I haven't tested this, but what if you were to create another tab with just that hidden column on it. create formulas on the main sheet to reference this hidden sheet. Then use a worksheet_activate event to say that if the user isn't one of the three managers then activate sheet1 again. I haven't tested this, so I don't know if there would be a visible blip in the tab switching or if it would be seamless. definitely worth a shot though.

 

[FrumpyJones]

I haven't tested this, but what if you were to create another tab with just that hidden column on it. create formulas on the main sheet to reference this hidden sheet. Then use a worksheet_activate event to say that if the user isn't one of the three managers then activate sheet1 again. I haven't tested this, so I don't know if there would be a visible blip in the tab switching or if it would be seamless. definitely worth a shot though.

possibly. But will that event work on both browser and app-based versions?

 

[Candyman8019]

Okay, I just tried this and it's quite quick.

Anything with VBA will only work with the app-based version. Your sharepoint or site administrator can force this app or library to open in the desktop app. If they refuse for whatever reason, there are other ways to get people to use the app rather than web version.

 

[Candyman8019]

For example, Create a workbook_close event that makes all tabs except one “veryhidden”.
Create a workbook_open that unhides the relevant tabs.
On the tab that remains visible on close, put in large red lettering “You must use the desktop app for full functionality” and whatever other message you may choose.

If the user opens this workbook with the web, it won’t trigger the VBA to unhide the sheets and leaves it useless to them. When they open it in the desktop app, and enable content, the VBA will unhide sheets as expected.

 

[Candyman8019]

Even with this approach, you may run into issues where one person has it visible already...so may need to be tested first.

 

[Candyman8019]

possibly. But will that event work on both browser and app-based versions?

No VBA event will work with the browser...You could do some research into office script, but I have no idea what it's capable of.