Select Name from Dropdown List and Password Validation

Hi zulfaizan

Can you clarify:

1. "Whenever I click on it" - what is "it"? (cell?, button?)

2. When the name is selected from the dropdown H7, where is the password being entered - do you want a pop up box for this?

3. Should the input of the correct password trigger addition of signature?

4. Should the input of the correct password automatically trigger status to change from "not approved" to "approved"?

thanks

Reason why i create two workbook as follows:
a) WORKBOOK - Approval : user will download the form of approval and keep with them (desktop/notebook).
b) WORKBOOK - Credentials : i can manage by myself and keep update the list if approval name,password, dept and signature information.

Potential issue here
- to access any data in file Credentials VBA requires path to that file to be available
- is the user connected to the server when "approving"?

- is the user connected to the server when "approving"? (answer : join domain/network)

Are you saying that the user is logged in to the network
- directly if in the office
- or via VPN if out of the office?

Is that correct?

Are you saying that the user is logged in to the network
- directly if in the office
- or via VPN if out of the office?

Is that correct?

answer : yes, you're totally correct

Ok - I will look at this for you during the next 48 hours

We do not need a dropdown for the UserName
- the user is logged in
- default the name to UserName

test in attached workbook with {CTRL} k

Please Login or Register  to view this content.

I want to make the code as simple as possible for you and give you exactly what you want

Let me explain better...

Peter is the user name
Peter opens workbook "Approval"
The name in the box is Peter - why would he choose a different name?

Next Peter enters a password
This triggers file "Credentials" to open
User name (Peter) checked in file "Credentials"
If user name is valid then Password is checked
If Password is correct then other details are added to sheet

Is the name in H7 (in "Approval") the same as Windows UserName or is it different?

I attach an example using dropdown for names as requested

Download attached workbooks
- open Approval_v1 and amend this line (in sheet VBA) to match file location

Please Login or Register  to view this content.

- click on Begin
- test with name "Mike" and password "Mike1"

Does it do what you want?

How it works
A = Approval, C = Credentials

- click on BEGIN to reset all values\ open C
- dropdown updated
- user selects name
- password box appears
- user enters password
- password is checked
- other values and signature fetched
- C is closed

Notes
- sheet in C containing passwords is hidden as a precaution (xlVeryHidden)
- images have been renamed pic_Mike etc to allow them to be copied without requiring LookUp
(image names must be totally consistent in structure)
- use made of worksheet-level named ranges to make referencing of cells easier in A
("TheDate","Designation","Password","Signature" & "UserName")

Dropdown - are you really sure about this?
To create a dropdown based on values in the other workbook requires 2 named ranges
- Data Validation with formula: =ListOfNames (a named range in A)
- ListOfNames with formula: =Credentials_v1.xlsm!NameList (a named range in C)
- NameList with formula: =OFFSET(Credentials!$B$2,0,0,COUNTA(Credentials!$B:$B)-1,1)

Workbook C is protected
- modify and read passwords are both "password"
- (because of above Named Range Link), if C is protected, then when A is opened Excel demands the user inputs password to allow links to be updated
- VBA cannot be used to answer that message without opening C before A
- problem avoided by breaking the link when file A is closed
- Do you intend C to be protected?

Avoid the above problem by not having a dropdown in A - it is unnecessary in my opinion
- tomorrow I will provide a much simpler solution so that you can choose

Originally Posted by kev_

I attach an example using dropdown for names as requested

Download attached workbooks
- open Approval_v1 and amend this line (in sheet VBA) to match file location

Please Login or Register  to view this content.

- click on Begin
- test with name "Mike" and password "Mike1"

Does it do what you want?

How it works
A = Approval, C = Credentials

- click on BEGIN to reset all values\ open C
- dropdown updated
- user selects name
- password box appears
- user enters password
- password is checked
- other values and signature fetched
- C is closed

Notes
- sheet in C containing passwords is hidden as a precaution (xlVeryHidden)
- images have been renamed pic_Mike etc to allow them to be copied without requiring LookUp
(image names must be totally consistent in structure)
- use made of worksheet-level named ranges to make referencing of cells easier in A
("TheDate","Designation","Password","Signature" & "UserName")

Dropdown - are you really sure about this?
To create a dropdown based on values in the other workbook requires 2 named ranges
- Data Validation with formula: =ListOfNames (a named range in A)
- ListOfNames with formula: =Credentials_v1.xlsm!NameList (a named range in C)
- NameList with formula: =OFFSET(Credentials!$B$2,0,0,COUNTA(Credentials!$B:$B)-1,1)

Workbook C is protected
- modify and read passwords are both "password"
- (because of above Named Range Link), if C is protected, then when A is opened Excel demands the user inputs password to allow links to be updated
- VBA cannot be used to answer that message without opening C before A
- problem avoided by breaking the link when file A is closed
- Do you intend C to be protected?

Avoid the above problem by not having a dropdown in A - it is unnecessary in my opinion
- tomorrow I will provide a much simpler solution so that you can choose

Hi kev,

thank you for your response and reply... :-)

I will download and test... i cannot wait to test it.... tqvm in advanced.


FYI,

1) from my side, i don't want the credentials to be password protected bcoz limited user can access to the files.

2) why i need the drop down list - feel free to choose but must with credentials. :-) . Approval can be anyone and they might be using desktop/laptop without joining domain. Only the network. Mostly approval who love travelling job.

3) i fwd to you simple form as example ....

Sorry if i did not provide you clear information. It is my fault.

Scenario:

a) User have to fill in information about their request and email the form for approval.

b) User have to request approval from their bosses. This is where i need the name to be drop-down list (my idea). After approval key-in the password, cell will display information according to the credentials.

c) Approval send back the form via email to the requester and requester (user) can attach in email and email to us directly. The form contains about request information and approval from their boss.

d) My intention was, user and approval do not have to print out. Just email the form. Paperless .... On my side, i can manage the approval list if any changes to the structure later on...

ok - will update thread later today

amended as follows
- "Credentials" is NOT password protected
- Data Validation Dropdown list is now created "live" (without using named ranges)
- all named ranges removed
- password userform removed
- key values are copied into an array
- array is looked up to return required values
(matching password to array value provides a case sensitive match)
- the coding is simplified as a result

Is that more like what you want?