VLOOKUP using a range variable

[Henry Hayden]

I have a worksheet into which I'm importing a userlist generated by
another application. This list contains user IDs that will determine
who can open the spreadsheet application.

As part of the workbook's OPEN event, the users must log in to the app.
I'm getting the user ID via an InputBox, and I want to verify that the
value is within column 1 of sheet 2.

VLOOKUP seems to cough, however, on the variable reference. This value
(the variable) will be different, since the app will run on a number of
servers with mirrored network layouts. So, when the sheet loads, the
userlist will shrink and grow over time.

I'd like to know how I can get this done. It doesn't really matter to
me if the procedure involves VLOOKUP or any other function- I just need
to be able to allow or deny access to the application. I'd appreciate
any push in the right direction.


Henry
DPM MEllon



*** Sent via Developersdex http://www.developersdex.com ***

 

[Tom Ogilvy]

Private Sub Workbook_Open()

ID = Inputbox("Enter User ID")
res = Application.Match(ID,thisworkbook _
.Worksheets(1).Columns(1),0)
if not iserror(res) then
msgbox "Welcome " & ID
else
msgbox "Access is not authorized for you"
This workbook.Close SaveChanges:=False
End if
End Sub

 

[Henry Hayden]

Thanks, Tom. I tried this very quickly at work earlier this afternoon
and fo the first time I didn't get a compile- or run-time error. Now I
have to look at the MATCH() function to see how it is applied. I tested
searching for a value that I could clearly see was in the list but it
was not found. I don't yet know what the return values should be. I'll
take a look tomorrow morning and see how it goes. Thanks again- it's
been a long day.


Henry


*** Sent via Developersdex http://www.developersdex.com ***