Message box for multiple values

[martinsimart]

Hi

I want to adapt the below code so that if I entered a name from a choice of say 10 names it would bring up a message box
with their managers name. The code below works for one name but how do i add to it?

Sub manager()
If Range("A1").Value = "Martin" Then
MsgBox "Lee", Title:="Manager is"

End If
End Sub


Thanks

Mart

 

[jproffer]

If it really is just 10 names, it would probably be easier to copy what you have rather than set up a Vlookup table.

Or maybe better yet:

Select case Range("A1").Value
    Case "Bob"
        msgbox "Bob's Manager",,"Manager Is:"
    Case "Joe"
        msgbox "Joe's Manager",,"Manager Is:"
    Case "Tom"
        msgbox "Tom's Manager",,"Manager Is:"
    Case "Tim"
        msgbox "Tim's Manager",,"Manager Is:"
End Select

or somethin like that.

 

[mumps]

You would need to have a list of names, let's say in column A and their corresponding manager's name in column B. Do you have that data?

 

[martinsimart]

Hi

The answer from jproffer works a treat

I only have appox eight team members for 7 managers so it's not a problem entering them (I suppose there would be another way if you had lots of manes)

Many thanks

 

[jproffer]

Yes, if you had 10000 names, I wouldn't have even suggested a Case Select. If you did, then mumps way would be better. Make a table, then have VBA do a VLookup to find the manager.

And BTW, that way would work, but I try to keep my "things I need to hide from the end user" to a minumum...such as V Lookup tables in hidden worksheets, etc.