VBA VLook Up Type Mismatch

[The_Gigster]

Hi

I have a range on a worksheet that I wish to restrict to a number of users based on their application name but, based on the following if the User Name is not in the range, a type mismatch is returned:

Private Sub Workbook_Open()




Dim strUserName As String


strUserName = Application.UserName
Range("AZ1") = strUserName


If strUserName = (Application.VLookup(strUserName, Sheet1.Range("$BA$1:$BA$9"), 1, False)) Then
Columns("G:L").Select
Selection.EntireColumn.Hidden = False
Range("A1").Select
Else
Range("A1").Select

End If


End Sub

Any help will be greatly appreciated. Thanks in advance.

 

[wigi]

Hello

Did you put strUserName in cel AZ1 on the correct sheet ?

Since you use cell AZ1 to store the username, why not have the VLOOKUP formula in say cell AZ2 ?
Then VBA-code is easier and can just read the result of the formula.

Private Sub Workbook_Open()

    Dim strUserName           As String


    strUserName = Application.UserName
    Sheet1.Range("AZ1").Value = strUserName


    If Sheet1.Range("AZ2").Value = strUserName Then
        Columns("G:L").Hidden = False
    End If
    
    Range("A1").Select


End Sub

 

[The_Gigster]

Hello

Thank you for taking the time to respond. I understand what you've done there - but when the user name is not in the range given, how do I create the "else" without an Type Mismatch 13 error.

The Gigster

 

[Norie]

This will unhide columns G to L if the username is found in BA1:BA9 on Sheet1.

Private Sub Workbook_Open()
Dim strUserName As String
Dim Res As Variant

    strUserName = Application.UserName
    
    Range("AZ1") = strUserName
    
    Res = Application.Match(strUserName, Sheet1.Range("BA1:BA9"), 0)
    
    If Not IsError(Res) Then
        Columns("G:L").Hidden = False
    End If
    
    Range("A1").Select

End Sub

 

[The_Gigster]

Many thanks for the advice - that's worked a treat. Appreciate it.