Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP using a range variable
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP using a range variable
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 -- Regards, Tom Ogilvy "Henry Hayden" wrote in message ... 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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP using a range variable
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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP variable range cell reference | Excel Worksheet Functions | |||
Vlookup using variable path name for range value | Excel Worksheet Functions | |||
Macro to copy a specified range to a variable range | Excel Programming | |||
setting a range variable equal to the value of a string variable | Excel Programming | |||
Problem trying to us a range variable as an array variable | Excel Programming |