Request of Different Pwd on different selections of drop down list

R

ron2k

Board Regular
Joined
Jan 28, 2008
Messages
139
Hi everybody,

This is the first time I do this (to cross-post and notify); so I need your help. I asked this question here sometime last week, but it got buried after some discussion posts with a fellow member who didn't provide an answer to the issue.

But I have this workbook with two worksheets (Sheet1 and Sheet2) and on Sheet1!A1 there is a validation list referencing to Sheet2!A2:A16. Its corresponding password is on Sheet2!B2:B16. I'd like a piece of code that will ask the user its corresponding password depending on the selection made. A sample worksheet is on that thread and for those of you who are not members there, let me know so I can place it somewhere else but it will have to be until I get home cause I don't have access to those sites from work.

I know this is far from secure but the supervisors for the departments on that list are not well versed in Excel and by hiding the proper VBA objects and worksheets, I think we can make it safe enough. The permutations that will be available per selection of department will be almost endless and that is why we cannot print a separate snap shot for the different combination.

What do you suggest?

Thanks,

Ron
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
GTO said:
If I am following... see if this helps: http://www.vbaexpress.com/kb/getarticle.php?kb_id=531
Click to expand...

Well, in reality all departments need to have access to Sheet1!A1 (which is where a list of 15 items exist). Everything else will be locked. Now whether the cell will accept the user selection will depend on whether he inputs the proper password, otherwise I'd like that cell to remain blank.

All other cells except A1 (and other validation lists cells) will be locked - the worksheet is dynamic. They can only select from lists, but the determining one will be whether they are allowed to see a particular department.

But thanks for the above. It will be useful one day.

Ron
 
Upvote 0
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:
<table style="border-collapse: collapse; width: 223pt;" border="0" cellpadding="0" cellspacing="0" width="296"><col style="width: 92pt;" width="122"> <col style="width: 131pt;" width="174"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 92pt;" width="122" height="17">Private Banking</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="width: 131pt;" width="174">Test100</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Dangriga</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td>Test625</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Placencia</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td>Test626</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Punta Gorda</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td>Test630</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Corozal</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td>Test635</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Corozal Free Zone</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td>Test636</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Orange Walk</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td>Test640</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Belmopan</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td>Test645</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">San Ignacio</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td>Test650</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">San Pedro</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td>Test670</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">North Side</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td>Test680</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Belize City</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td>Test695</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Staff Branch</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td>Test700</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">BZE & Staff</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td>Test695700</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl63" style="height: 12.75pt;" height="17">Consolidated</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td>Test999</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> </tbody></table>
As I said A1 will always be A1. 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?
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Column data selection drop-down
Replies
5
Views
352
SanjayGMusafir
SanjayGMusafir
S
  • Solved
How do I password protect a drop down selection?
Replies
8
Views
534
kevin9999
K
F
  • Solved
How to format a vendor list when vendors offer several different products?
Replies
8
Views
246
Micron
M
P
  • Question
How do I password protect a drop down list
2
Replies
19
Views
1K
Akuini
Akuini
B
  • Solved
VBA to save one file as multiple files in location
Replies
7
Views
677
bh24524
B

Forum statistics

Threads
1,215,894
Messages
6,127,619
Members
449,390
Latest member
joan12

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top