Dividing a work book among various groups, hiding sheets and passwords!

[exelblagger]

I am new to Exel and have been dropped in the deep end with my new job and although my learning curve has been steep there are a couple of things I urgently need to do and I am confused to hell! And I quite like my job so please can anyone help me!?

I am working on a large work book about 20 seperate work sheets and what I need to do make sure that certain people when they open the sheet can only see certain sheets with the rest remaining completely hidded. Essentially 4 different groups with 5 worksheets each! I have trawled the site and found some helpful code relating to 'veryhidden' and some coding. None of which I could get to work.

Could anyone help me with an 'idiots guide to solving my problem' I have read the forum rules and I know shouting is frowned upon but imagine this text in twenty feet high letters! Help!

Many thanks for your time and if I have made any school boy errors, please forgive me this is my first ever forum post!

 

[kpark91]

Hi, exelblagger.

The best alternative solution is for you to only allow one user to edit the master workbook
and when you're done with the master workbook, you create 4 separate workbooks which contain the corresponding 5 worksheets each!
I believe that having sensitive data on the same workbook is not a good idea, since there is a tool for cracking those worksheets.

However, if you insist I think the best way of achieving your goal is to use macro, which seems you already know.

I am not sure if you're working on a large workbook which is going to be shared on the network folder.
If you are then, let me just point it into correct direction:
1) An important feature of Excel to keep in mind is that a user usually starts Excel with macro disabled. However, there is no code to manually turn on their macro privilege (since it will be a great security risk!), so it'd be best if you keep all the worksheets "very hidden" on the point of exiting the worksheet.
2) I am unsure about whether you can disable your macro in the middle of the manipulation of data but if that's the case, you have a huge security risk of showing sensitive data to unprivileged users.
3) Put password on VBA code you've written.
4) Possibly, if there are only a limited set of users, you may use Environ("UserName") function to give privileges to sensitive data.

In the future, when you are posting a question, which requires tons of code, it is best if you break up your question into little technical questions. Because nobody wants to write a huge code for somebody they don't even know!

 

[Smitty]

Welcome to the Board!

If you PM me your e-mail address I'll send you a workbook that will do what you want.

 

[shyy]

Good starting point would be

Private Sub Workbook_Open() 
 If Application.UserName = "excelblagger2" Then 
    'code to hide/unhide sheets
Elseif
Application.UserName = "excelblagger2" Then 
    'code to hide/unhide sheets
Elseif Application.UserName = "excelblagger3" Then
'code to hide/unhide sheets
Else
'code to hide/unhide sheets
 End If 
End Sub

 

[exelblagger]

That is fantastic help! I am greatful for your advice on posing questions.

I don't think anyone will try and crack the code, it's just to ensure they can only see what they need. If you have a second could you explain or even a tiny piece of code to show how the Environ("Username") function works as I am guessing you could select the worksheets for each environment?

I am not really that good with Exel and have only been using it about three weeks. I have just been plunged in at the deep end! Hence Blag in my name! Thanks for taking the time to help me!

Kind regards

 

[exelblagger]

I will have a look at that! thank you so much for your time!

 

[exelblagger]

If my user name was joe.bloggs and the sheets I wanted joe to see had been named dashboardone dailychristmas
and billy.blagger needed to see dashboardtwo and dailyinternational.....

How would that code look? I have tried the above code but I can't get it to work!?


Sorry to be a pain!