CODEWITHSUNDEEP
excelexcel-2010vba
Mr_Thomas
Mr_Thomas

Reputation: 869

Populate UserForm 'Could not set the Value property'

I'm having a problem populating a userform. I found some code online that does exactly what I want and the 'example' file works perfectly. When I modify it to my needs, it gives me an error message on the following line:

frmModifyData.Skill.Value = Application.VLookup(cmbItemName.Value, Sheets("Enrolled").Range(vrange), 1, False)

Here's the entire code I'm working with:

Dim NotNow As Boolean
Private Sub cmdCancel_Click()
    Unload Me
End Sub

Private Sub cmdOkay_Click()
NotNow = True

N = Application.Match(Me.cmbItemName.Value, Range("AB:AB"), 0)

Cells(N, 1).Value = Me.frmEnterData.Skill.Text
Cells(N, 2).Value = Me.frmEnterData.txtCLASS.Text
Cells(N, 3).Value = Me.frmEnterData.LastName.Text

NotNow = False
End Sub

Private Sub cmbItemName_Change()
If NotNow Then Exit Sub

vrange = "FirstField"


'LINE WITH THE PROBLEM
frmModifyData.Skill.Value = Application.VLookup(cmbItemName.Value, Sheets("Enrolled").Range(vrange), 1, False)
'END OF LINE WITH THE PROBLEM (though it could affect the two lines of code below...)


frmModifyData.txtCLASS.Value = Application.VLookup(cmbItemName.Value, Sheets("Enrolled").Range(vrange), 2, False)
frmModifyData.LastName.Value = Application.VLookup(cmbItemName.Value, Sheets("Enrolled").Range(vrange), 3, False)

End Sub

Private Sub UserForm_Initialize()

frmModifyData.cmbItemName.RowSource = "FirstField"
End Sub

'FirstField' is a named range that is defined this way

=OFFSET(Enrolled!$AB$3,0,0,COUNTA(Enrolled!$AB:$AB)-1,3)

Column AB holds the "Full Name" of the user. This is what I'm using to find an individual. Once I pick a name using a drop-down box on the userform, it gives me the message Could not set the Value property. Invalid property value.

How do I fix this so it works?

Upvotes: 0

Views: 976

Answers (1)

Tim Williams
Tim Williams

Reputation: 166755

Try breaking your code down a little and make sure your vlookup is working...

Dim v

v = Application.VLookup(cmbItemName.Value, Sheets("Enrolled").Range(vrange), 1, False)

If Not IsError(v) Then
    frmModifyData.Skill.Value = v
Else
    Msgbox cmbItemName.Value & " was not found!"
End If

Upvotes: 1

Related Questions