+ Reply to Thread
Results 1 to 17 of 17

Hiding tabs from specific Users!!

  1. #1
    Registered User
    Join Date
    04-11-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    92

    Hiding tabs from specific Users!!

    Hi Guys,

    Thanks in advance for your help.

    I am trying to create a spreadsheet which allows different teams to input their data on their individual sheets, and which then aggregates all the teams data onto a general sheet for all the group. The idea is that each user will only be able to see his specific team sheet and the general sheet, but not other teams sheets. The line manager should be able to see all the team sheet and the general sheet.

    I thought about the following:

    Create a Number of team sheets namely for the teams CORPORATES, FINANTIALS, COVERED, ILLIQUIDS. Automatically hide these sheets so that they cannot be unhidden manually. These sheets will contain the individual team information.

    Create a Master sheet, with a number of buttons, namely CORPORATES, FINANTIALS, COVERED, ILLIQUIDS. Upon clicking these, the system will check if the username corresponds to a valid username for that team, and if so will unhide that particular team's sheet.

    Create a summary sheet which feeds from all the team sheets and adds them up. Everyone should be able to see this sheet, but only one username, the manager, can edit it.

    Any Ideas? I have programmed in C before, but I am quite new with VBA and Macros, so any help or Code that you can give me will be great!

    Thanks forum!

    Jaime

  2. #2
    Registered User
    Join Date
    04-11-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    92

    Re: Hiding tabs from specific Users!!

    - Also, do you think buttons is the best way to go? Thanks

  3. #3
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Re: Hiding tabs from specific Users!!

    here is my effort.

    One thing you need to do to make it work, is edit the User spreadsheet so that you have your usernames in colomn A (this should be their "environ" names) and the corresponding accesslevel for that user should be in Col C.

    After you have done that, enable the last line in this code

    Please Login or Register  to view this content.
    so it looks like this

    Please Login or Register  to view this content.
    and then your users sheet will be hidden as well.

    The Password to edit the code is set to PW by the way.

    hope it helps.

    Nick

    PS - this is just the start, I am not sure what you mean yet by:

    "Create a summary sheet which feeds from all the team sheets and adds them up. Everyone should be able to see this sheet, but only one username, the manager, can edit it.
    "
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    04-11-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    92

    Re: Hiding tabs from specific Users!!

    Thanks a lot, this is very helpful!

    I have now editted the User sheet but I get a compilation error on the line which was commented. I attach the program with the changes.

    What I meant with the "Summary Sheet" was that, additionally to the corresponding team sheet revealing itself according to who opens the document, there is also an extra Sheet, which we can call "Summary", which has the function of synthesizing the individual team sheets and giving a general picture for the whole department. The idea is that this sheet is always Visible, but locked for editing except for the manager. For example, if a member of CORPORATES opened the document, they would see the Summary sheet (which they could not modify), and the CORPORATES sheet, which they would be able to change.

    Therefore I would need to create an additional Access level, namely "MANAGER". When a user with this access level opens the document then all sheets ( SUMMARY,CORPORATES,FINANCIALS,COVERED,STRUCTURED,ILLIQUIDS) should become visible, and SUMMARY should be Editable.

    I hope that this clarifies the issue. Let me attach what I have now. Thanks a lot in advance!TEST2.xlsm

    Jaime

  5. #5
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Re: Hiding tabs from specific Users!!

    ok done a slight amendment, I think your usernames are wrong.

    To test, I have created a module called environ - run this and it will tell you what your username is. this is the name you need to enter in Colomn A.

    You got ythe compilation error which was my fault. When you edited the code as I suggested it could result in all sheets being hidden and therefore there was no sheet to display. now corrected.

    Try again and test for me.
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Re: Hiding tabs from specific Users!!

    slight update here:
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-11-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    92

    Re: Hiding tabs from specific Users!!

    Cool,

    You were right about the username, I ran ENVIRON and I am J, SUAREZ CORONEL.

    However when I open the Excel I only see see SUMMARY instead of everything (I am MANAGER), and if I change myself to (CORPORATES) and open it, I only see SUMMARY instead of SUMMARY and CORPORATES.

    Thanks for your help!

  8. #8
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Re: Hiding tabs from specific Users!!

    you must not be typing in your username in correctly as it works perfectly for me.

    create a new sub to test your username - run the macro and if you get a yes then copy and paste the username in the code below to your Colomn A. If No then you are mistyping it.

    Please Login or Register  to view this content.
    Last edited by nickmax1; 04-12-2013 at 03:23 PM.

  9. #9
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Hiding tabs from specific Users!!

    Another way to approach this is to make use of a UserForm.
    Attached Files Attached Files
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  10. #10
    Registered User
    Join Date
    04-11-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    92

    Re: Hiding tabs from specific Users!!

    Thanks Nick!

    You were right, I created a macro to output my username onto a cell and its J(space),SUAREZ(space)CORONEL. It now works perfectly!

    One last thing, unfortunately the tabs are only hidden when the user enables the macros, is there a way so that they are hidden from the start no matter what? Or that the document can only be opened in macro enabling form? Thanks!!

  11. #11
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Re: Hiding tabs from specific Users!!

    try this one and test

    Once you are done with all this you need to password protect the VBA code, and you need to make sure users cant run the code themselves (or they can edit the form as you can!)
    Attached Files Attached Files

  12. #12
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Hiding tabs from specific Users!!

    Add this to your Code in the ThisWorkBook Event.

    Please Login or Register  to view this content.

  13. #13
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Re: Hiding tabs from specific Users!!

    winon,

    your code is pretty much what i have in the attached spreadsheet.....good work.

  14. #14
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Hiding tabs from specific Users!!

    @ nickmax1,

    Oops, sorry about that!

    I should learn to check first.

    Seems like my UserForm approach, was not considered as an alternative option,

  15. #15
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Re: Hiding tabs from specific Users!!

    winon

    I liked your userform mehod - an impressive piece of coding well above my simple capabilities. but your way does involve more manual entry and doesnt follow on from what the OP requested.

  16. #16
    Registered User
    Join Date
    04-11-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    92

    Re: Hiding tabs from specific Users!!

    Hey Winon,

    I must say I agree, an impressive piece of code although it dos not fit what I am looking for exactly.

    There must be a Summary sheet which is visible by all levels of access.
    When a CORPORATE user enters the file he should only have access to summary and corporate sheets.
    Access control should only be visible for MANAGER status.

    Thanks to you both!!!

  17. #17
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Re: Hiding tabs from specific Users!!

    Jsuarez199
    If you are happy with the solution, can you mark the thread as solved please (top right drop down called "thread tools", in there "mark as solved"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1