Shared Workbook, Matching data in...

[TTom]

SHARED WORKBOOK, MATCHING DATA IN...
I've been struggling with this and need some help. I'm not getting
path correct, thus, the marco hangs before I can test other parts.
Any input is greatly appreciated and hopefully useful to others.

This look a bit long, but it's pretty straight forward...

I have a workbook named: "Login.xls"
In it I have two worksheets named: "MyInput" and "MyData"

In "Input" I display a userform with two textbox; one to take a
'username', one to take a 'Password'. I have a macro that will
pass the typed 'Username' to MyData(R1,C1) and the 'Password'
to MyData(R1,C2) when an "OK" button in userform is clicked.
It might be possible to grab values from directly this macro, and
use to do the match I require?
(see my userform macro for "OK" at end of my post)

I have a "Shared Workbook" whose name is: Names.xls
The shared workbook is in a folder on our user network.
Currently it is drive mapped to Z:
Thus, the path to this is Z:\COMPANY\DATA\Names.xl
It has a password to open: "pw"
I has a worksheet named: "MyList"
within this sheet:

Column(1) is a list of conditions as Integers
Column(2) is a list of Usernames as Strings
Column(3) is a list of Passwords as Strings


Let us first assign in the macro two variables:
DIM v As Integer
DIM c As Integer

I now need assistance to do the following -

Search Rows in shared workbook
Z:\COMPANY\DATA\Names.xl in worksheet "MyList"

If value in Column(1) = "" or 0,
there is no data in this row nor any that follow,
ie: end of the list!

If a 'Username' match is found in Column(2) look for
'Password' match in Column(3) of same row
If both are a match Then
v = 1
c = value in Column(1), Goto a

If 'Username' match is found, but 'Password' does not match Then
v = 2
c = value in Column(1), Goto a

If neither Username or Password is matched
v = 3
c = 100, Goto a


'Goto a' goes to next part of the sub having values for 'v' and 'c'
I use 'v' and 'c' because 'c' value tells me about 'other conditions.

I found that formulas in Excel can access the shared workbook
without opening it (at least as far as the user is concerned)
I'd like the same in the macro (unseen).

I will have a followup question about paths for networks vs.
mapped drives, also how to 'send data' to shared workbook,
but one thing at a time, right? :wink:

***FYI***
Here is my userform 'OK' button marco
(Used after Username and Password have been entered)


Private Sub cmdOK_Click()
Dim ws As Worksheet
Set ws = Worksheets("MyInput")
Set DB1 = Worksheets("MyData")

'check for a Username textbox entry
If Trim(Me.txtUsername.Value) = "" Then
Me.txtUsername.SetFocus
MsgBox "Please enter your Username"
Exit Sub
End If

'copy the data to Sheet named MyData, Row 1, Cols 1 & 2
MyData.Cells(1, 1).Value = Me.txtUsername.Value
MyData.Cells(1, 2).Value = Me.txtPassword.Value


'clear the data
Me.txtUsername.Value = ""
Me.txtPassword.Value = ""
Me.txtUsername.SetFocus

'close the form
Unload Me

End Sub

**** Thanks, TTom ****