simple VBA issue

[icestationzbra]

greetings,

i have a simple issue at hand, unfortunately, i am not able to come u
with a solution for that.

i have a workbook which has userid-pswd protection. there are 3 kind
of users that access that workbook, viz, users with low, medium an
high level of access to the workbook. depending on the access level
certain sheets and/or certain areas may or may not be accessible t
them.

here is the picture:

USER PSWD ACCESS

A 1 L
B 2 M
C 3 H
D 4 L
E 5 M
F 6 H

i am keeping all this information on a sheet in the workbook, thi
sheet is hidden unless its a H access level user accessing th
workbook. i am using sheet.range("") to match the userid with the pswd
some sundry info that i thought might be useful to provide.

i check for the user and pswd in a function, that returns to me th
userid. now, i am not able to figure out where to check for the acces
level. that is, i am not able to come up with an algorithm.

should i write another function? should i check for the access level i
the same function as the one that authorizes?

any help will be duly appreciated.

thanks,

mac

 

[JE McGimpsey]

One way:

accessLevel = Application.VLookup(userid, _
Sheets("Hidden Sheet").Range("A:C"), 3, False)

 

[icestationzbra]

hi JEM,

thanks for the reply.

for sometime, i have been trying to implement your idea.

this is the piece of code that i am using for testing:

*****

Dim accessLevel As String

accessLevel = Application.VLookup(strUID, Sheet2.Range("aa:ac"), 3
False)

MsgBox "access " & accessLevel

*****

strUID is the string variable that i am using to store the id of th
user logged in. i use this variable elsewhere on several other pages.

when i use strUID in the above snippet, i get an error "Type Mismatch"


until i supplant the "strUID" with one of the actual IDs that i have i
the list, i am not getting the accessLevel msgbox to pop up.

once i get the msgbox to pop up with the msg, i am hoping i would b
able to use it in streamlining access elsewhere.

if there is something too basic that i am missing, i am not able t
figure out. but i will keep working on it. in case you can find time
please help me out.

thanks,

mac

 

[JE McGimpsey]

You'll get a type mismatch error if the value isn't found, unless
accessLevel is a variant (i.e., an error can't be assigned to a Long).
Application.VLookup returns Error 2042 if the value isn't found.

I prefer:

Dim vResult As Variant
vResult = Application.VLookup(strUID, Sheet2.Range("AA:AC"), 3, False)

If IsError(vResult) Then
'not found - do something else
Else
accessLevel = CLng(vResult) 'or CDbl, etc.
End If

One thing that might be causing this problem is if your lookup values
and strUID are different types.

If strUID is a string and the values in column AA are numbers, you could
use

vResult = Application.VLookup(CDbl(strUID), _
Sheet2.Range("AA:AC"), 3, False)

 

[icestationzbra]

hi JEM,

try as i might, i was not able to get it to work. no matter what
declared as the type (variant et al), it still gave me a Type Mismatc
error.

i think, i may be missing something.

i came up with a workaround. there is a text box where in the use
enters the userid. i took that and used it in the vlookup statemen
that you gave me. it works like a dream. it does exactly what
intended it to do.

thank you very much for providing me with help.

regards,

mac