Show hidden sheet and columns based on user

Hi everybody, I am new at the forum and also in VBA. I would like to obtain help on the following: I have a document with several tables located one on each sheet. Now I would like to update the info from that tables but several teams have to do it. I have one sheet that contains the table name, the fields and the team in charge to update that field on that table, resulting that maybe several teams have to view one table however not the same field. So what I would like to do, to avoid errors, is for the teams can only view the table and columns that they need to update only.

I have managed, looking trough the web, to create a document with user and password and depending on the user it only shows the sheets that the user should see. However I do not know how to make also the columns to be hidden according to user. Do you have any idea how can I do this? Thanks!!!

I am attaching a zip file with two documents: "Zzz" is the document with user and password, "Table Names" is the excel document with the sheet with reference to the table names and fields.

Hi mari_hitz.

I'm afraid your code is going to need some further tweaks if you need this to be secure.
To see what I mean, try these two things:

1. Open the file, and enable macros. Put the wrong password in. When Excel says Access Denied, click on OK. THen when the messagebox prompting whether changes should be saved comes up, click CANCEL. THe file remains open. An experienced user can then make the hidden sheets visible by pressing Alt F11 and then setting the Visible property of any hidden sheets to xlSheetVisible. Note that if you add the line Application.DisplayAlerts = false to you code just before the ThisWorkbook.Close line, then users won't get prompted to save, and so won't have a chance to cancel.
2. Open the file, but don't enable macros. The Workbook_Open code won't fire, and users can again make any hidden sheets visible by pressing Alt + F11 and then setting the Visible property of any hidden sheets to xlSheetVisible. In fact, currently if you do this you see the User List sheet with everyone's passwords.

To stop users manually unhiding sheets, you will need to have code that hides any sensitive sheets whenever the file is closed, and then appply a password to the structure of the workbook so they can't manually unhide sheets via the Alt + F11 route I mention above.

How sensitive will the information be in this file? Or is it just that you don't want non-administrators to break anything?

To hide columns according to user, basically you just instruct VBA which ones to show depending on who the user is. I can help you with code for this (I have code from several projects I can share) but need to hear back from you first about the above.

Regards,

Jeff

You could check out my PassWord form

royUK: This looks great. Note that if you put in a nonsense name that's not in the picklist, it thows an error, at which point you can cancel out of the macro and then have access to the file.

Simple fix, change the ComboBox MatchEntry Property to True

Edit, I've amended the example