If the CaseOrgNum data in the worksheet is really a number, then you'll want to
look using a number.

In excel, 123 is different than '123 (text).

And if it's not found, you could add a check.



Private Sub txtCaseOrg_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim CaseOrgNum as Variant
Dim CaseOrgName as Variant 'could be an error

CaseOrgNum = txtCaseOrg.Text
'Sheets("CaseOrgs").Activate

if isnumeric(caseorgnum) then
'clng() maybe????
CaseOrgName = Application.VLookup(cdbl(CaseOrgNum), _
worksheets("caseorgs").Range("A1:B20"), 2, False)

'and just in case it isn't found
if iserror(caseorgname) then
msgbox "not a match, the board goes back
else
MsgBox CaseOrgName
end if
else
msgbox "not a number!"
end if

End Sub

Jeremys Dad wrote:

I'm just starting to play about with Excel VBA and have hit a rough
spot. I can't quite seem to grasp the proper coding for Vlookup.

The workbook contains 6 worksheets, one of which contains data on our
staff members. I'm trying to capture the name of the staff member
based on their staff number (we call it Case Org). Using the exit
event of a text box, I hope to get the staff member's name entered into
a cell.

Using Jeff Walkenbach's excellent "Dummies" guide, I can't seem to get
my code to work. I get any number of different errors as I have moved
things about. Any help would be appreciated.

As I read it, the following should work....

Private Sub txtCaseOrg_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim CaseOrgNum as Variant
Dim CaseOrgName as String

CaseOrgNum = txtCaseOrg.Text
Sheets("CaseOrgs").Activate

CaseOrgName = Application.VLookup(CaseOrgNum, Range("A1:B20"), 2,
False)

MsgBox CaseOrgName
End Sub

Thanks!

--

Dave Peterson