Fairly simple query re lists of values 1

[DPlank]

It's bound to be so simple. I just can't get this to work.

I have a hidden sheet which contains a list of Usernames and their access level. I'm trying to pull the access level by searching the list with the Username.

I've got code as follows

uName = Application.UserName
Application.ScreenUpdating = False
Worksheets("Sheet3").Activate
With ActiveSheet.Range("A2 : A102")
Set Namefind = .Find(uName)
    If Namefind Is Nothing Then
    MsgBox ("User not known to system")
    ActiveWorkbook.Close
    End If
'otherwise user must have been found
ActiveCell(Namefind).Select
access_level = ActiveCell.Offset(columnoffset:=2).Value
MsgBox ("Welcome, " & uName & ". You have " & access_level & " access.")
End With
End Sub

I get an empty value for the access level in all cases. Where is the mistake? I've stepped through this, checked the TT FAQs and even tried VBA Help itself. No joy.

Can anyone explain what I'm doing wrong?
Cheers!
David

[joinedupwriting]
"whaddaya mean, 'invalid tag'?!?"
[/joinedupwriting]

 

[SkipVought]

Hi,

This does work...

Sub nnn()
    uName = Application.UserName
    Application.ScreenUpdating = False
    With Worksheets("Sheet3").Range("A2 : A102")
        Set NameFind = .Find(uName)
            If NameFind Is Nothing Then
            MsgBox ("User not known to system")
            ActiveWorkbook.Close
            End If
        'otherwise user must have been found
        access_level = NameFind.Offset(columnoffset:=2).Value
        MsgBox ("Welcome, " & uName & ". You have " & access_level & " access.")
    End With
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[DPlank]

Excellent.

Thanks - I knew it'd be something simple. In fact it took me a minute to find your change!

Have a star!

David

[joinedupwriting]
"whaddaya mean, 'invalid tag'?!?"
[/joinedupwriting]