Request of Password on different selections of Drop down list

Hi all,

This one is a very interesting one. I know it will be lots of work, and please if it is ask me for you help, I'm pretty good in following instructions... Actually that's a good way to learn...

I have the attached workbook. And I would like excel to ask the user to enter a different password for the different selections that can be done on Sheet1!A1, the list of required password per selection are on Sheet2!B2:B16. Is that possible guys?

The reason will be because depending on the selection different data will be generated (No help there, I got that one...), but only the respective department can view its department data. For instance I don't want Dangriga to be interfering with Belmopan's data.

I know I will have to pwd-secure the VBA object and Sheet2 from viewing. How do you secure a sheet from viewing?

Thanks,

Well, you see once a department is selected, on the actual file there are several other options that can be selected under the department menu. For example period of report (Several Years), whether that period needs to be viewed by quarter, monthly, or YTD. Then the data of several projects the department has undertaken (too many to count), the template is standards but the data is dynamic. And the most important thing is that it also has a dynamic chart.

So printing or preparing different excel sheets for each department. The amount of of permutations that are possible out of that file will be huge!

So I need a way that one department does not peek into the other department. They are not that excel-savvy so with hiding the proper data, and having the proper macro, hopefully it is possible...

Thanks,

In that case, rather than having a password prompt pop up for each department in the dropdown list, why not have a multi-password prompt pop up when first running the xls file? (by multi-password, i mean a prompt that handles multiple passwords, but just one input).

Based on the password entered, it will hide all other user data except what is to be shown for that department.

(sadly I am not as VB savvy as the others on the site, so I cannot do more than offer suggestions.)

Hi guys, can this be done. I'd really appreciate if you get me started, I can then modify it to suit us.

Thanks

Hate to be a pain guys, but if macro is a bit complicated (Please tell me where to assist if this the problem) do you suggest taking another route? Ignore all posts except the first one so as to not make the thread look overwhelming...

Thanks

Hey everybody again. I think because of the many responses on the thread, the people that can help got discouraged to help or thought that help was not needed, so I did something I hadn't done before (by notifying) - post the question somewhere else - here

If you still want to help. I'd really appreciate it. And I'll inform the corresponding crossposting once I can find a solution.

Thanks,

I have an idea, but still I'd need your help in devising the code if this is easier than above.

What about if depending on a password that will be input by user, the validation list range will change to that value that only him is allowed to view.

This the list of departments and its corresponding password:
Private Banking |Test100
Dangriga |Test625
Placencia |Test626
Punta Gorda |Test630
Corozal |Test635
Corozal Free Zone |Test636
Orange Walk |Test640
Belmopan |Test645
San Ignacio |Test650
San Pedro |Test670
North Side |Test680
Belize City |Test695
Staff Branch |Test700
BZE & Staff |Test695700
Consolidated |Test999


As I said A1 will always be blank. We can place a code before closing the workbook to clear the cell. Then when opening if Belmopan is selected then "Test645" has to be entered, otherwise return to blank.

Now my new idea:A pwd will be requested on opening the workbook. If the users enters "Test645", then the validation list reference will change to just that value. However, if "Test999" is entered then the entire list is required. Does that make sense?

Haven't users logged in (network)?
Use those usernames to determine which data are available for that user. Do not bother uniquely identified users with passwords.

Originally Posted by snb

Haven't users logged in (network)?
Use those usernames to determine which data are available for that user. Do not bother uniquely identified users with passwords.

Yes they do log in to a network. But you see the excel workbook already has data for all departments and its generated depending on what you select on various drop down lists. However, the main drop down list which needs to be restricted is the Department drop down on A1, all other sublists just determine the data that is available for the specific department.

Would you mind elaborate on what you meant above?

You can skip the dropdown in A1; dependent of the username you can select only those data that belong to the user's department.

I'm starting to become a pain - definitely not what I wanted...

That definitely sounds interesting. But I have no idea what to do...

I know the user names of the supervisors, and for right now lets assume that the user names are the same as the department name. However, bear in mind that in the case of my user name and my boss, we still need the drop down and we need to see all. So in the case of of departments, I guess the value of A1 only needs to be their department name, and in my case and boss's, we need A1 to contain all the validation list...

Any suggestion on how to get me started. And please don't ask to liaise with my IT department, I think I know more excel than they do. They are just network geeks (geek to me is a badge of honour, no offense intended).

Thanks,

Cfr. the attachment

Hi bud,

uhm, how do you say this... Uhm, would you mind walking me through as to what I'm looking (or what I should be looking) at here so that I can apply it to my case?

I'm assuming that depending on what the user name is an additional worksheet will be displayed. However, I'd like that all the work remain on one worksheet, and all data for all departments will be displayed on that worksheet but the worksheet is dynamic depending on the selection on A1.

And here is where I'm stucked, If you open my attachment on post 1, what I'd like is for A1 of Sheet1 to remain blank when workbook is opened. Then if, for example, Private Banking is selected, then a password prompt or something like that will determine whether the user has access to view the data that will be generated by that selection, otherwise let A1 to remain blank.

Thanks for the work done this far. I really appreciate it.

Please try to follow my line of thought.

Every department has its own sheet with data.(department, 1,2 ,3)
Dependent of the username, that name wil be categorised in department1, department2, etc..
You see in the eventprocedure Workbook_open that some username will be categorised in department1, others in department2.
Dependent of that, data from different wordksheets will be loaded into the listbox in the sheet (overview) that is accessible to all users.
That's the main idea. So you won't need to ask a password to distinguish between users of different departments.
If you don't get this or do not like it my further contribution stops at this point. I can't be clearer than this.

First off: Thank you very much. I was starting to think that this was not possible or the task was a bit ardeous or boring for a fellow member to overtake.

Secondly: I apologize that I taking too much of your time. I don't want to sound that I want to take advantage of your good gesture...

And thirdly: I think I like your idea. I said "think" because I like what I understand so far. However, I'm REAAALLLY lost on few things:

1. You said, "Dependent of the username, that name wil be categorised in department1, department2" How does the code determine your user name? I cannot for the love of me open those sheets. Something I'd like in my code by the way. But I'd like at least myself to be able to open them.
   
2. Whenever you get some chance. Donesn't have to be today or this week. It will help if it is by the end of the current month as I need to prepare those reports. How were you able to do that? Make some sheets not available for unhiding? And the overview to just show you few rows and few columns.
3. Where does the list in the combo box come from? How can I edit it?

Once again my apologies, thank you very much and I'll appreciate a clarification from you or anybody else with few more brain cells than me that can follow snb's work.