VBA Password Validation and Vlookup

[nikzulfaizan]

Dear Experts,

I have problem and I am beginner.
Appreciate if someone can help me ...

I have two workbook as below.

A) Workbook Name : Request.xls

Description :
1) Where user have to download the files and fill in information before request any assistance.
2) Form have 2 section. 1st for user to fill in and 2nd section for their bosses to approve before submit via email. (Paperless ).

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]SECTION 1[/TD]
[TD]User Information[/TD]
[/TR]
[TR]
[TD]Name:[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dept:[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Request :[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SECTION 2[/TD]
[TD]Approval[/TD]
[/TR]
[TR]
[TD]Signature:[/TD]
[TD]< grab images from workbook credentials.xls >[/TD]
[/TR]
[TR]
[TD]Name:[/TD]
[TD]< dropdown list : grab from workbook credentials.xls >[/TD]
[/TR]
[TR]
[TD]Dept:[/TD]
[TD]< grab info from workbook credentials.xls ><get information="" from="" workbook="" credentials.xls=""></get>[/TD]
[/TR]
[TR]
[TD]Div. :[/TD]
[TD]< grab info from workbook credentials.xls ><get iformation="" from="" workbook="" credentials.xls=""></get>[/TD]
[/TR]
[TR]
[TD]Date:[/TD]
[TD]< auto date stamp >[/TD]
[/TR]
</tbody>[/TABLE]


B) Workbook Name : Credentials.xls

Description :
1) Manage by myself (share folder on network)
2) Cell info : as table below

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Dept[/TD]
[TD]Div[/TD]
[TD]Signature[/TD]
[TD]Password[/TD]
[/TR]
[TR]
[TD]Alan[/TD]
[TD]Dept1[/TD]
[TD]Div1[/TD]
[TD]Image1[/TD]
[TD]Alan1[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]Dept2[/TD]
[TD]Div2[/TD]
[TD]Image2[/TD]
[TD]Mike1[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Dept3[/TD]
[TD]Div3[/TD]
[TD]Image3[/TD]
[TD]Bob1[/TD]
[/TR]
[TR]
[TD]Pete[/TD]
[TD]Dept4[/TD]
[TD]Div4[/TD]
[TD]Image4[/TD]
[TD]Pete1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

i can add new name and information where possible / needed

Scenario :

1) User have to download the files. (on network / share folder)
2) User have to fill in the form / request.
3) After fill in, user have to get approval from his/her boss before submit to me via email.
4) Under Approval Section , his/her boss need to click at cell name (dropdown list)
5) Dropdown list information grab from Cell name in workbook credentials. Any new name added, cell "name" get the latest updates.
5) After selection of name (dropdown list) , popup UserForm that request for password to validate the user is the right person or not.
6) IF TRUE , name, signature image, dept, div from workbook credentials appear in selected cell - workbook Approval
7) IF FALSE, error popup and clear cell (approval section only)
8) After done, user have to email me the form via email.

I attached herewith sample vba that i found and useful but i do not know how to do it based on my scenario.

Option Explicit
Const Mike As String = "Mike1"
Const Alan As String = "Alan1"
Const Bob As String = "Bob1"
Const Pete As String = "Pete1"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim pwd As String
Dim Oops As Boolean

Application.EnableEvents = False

For Each cell In Target
If Not Intersect(cell, Range("B7")) Is Nothing And cell <> "" Then
pwd = Application.InputBox("Password for " & cell & ":", _
"Enter Password", Type:=2)
Select Case cell.Value
Case "Mike"
If pwd <> Mike Then Oops = True
Case "Bob"
If pwd <> Bob Then Oops = True
Case "Alan"
If pwd <> Alan Then Oops = True
Case "Pete"
If pwd <> Pete Then Oops = True
End Select

If Oops Then
MsgBox "Bad password"
cell = ""
End If
End If
Next cell

Application.EnableEvents = True
End Sub