Problem with VLookup Using Application.WorksheetFunction.VLookup

[MarkMueller]

I am working on a small Excel Project that builds a UserForm with Selected Data from a Workbook that has 4 sheets of which 2 sheets are important Sheet 2 (Data) and Sheet 3 (Lists).
The (Data) sheet has an area that is populated based on input from the UserForm called: frmTraining. And uses a Criteria Range and Extract Range to highlight the Listbox. Controles at the bottom of the Listbox provide the ability to Input New Trainers, Update Trainer Information, and/or Add New Training.

So I am attempting to lookup the Course Code then automatically populate the Course Name Associated with the Course Code during [Adding New Trainers], I have added a section (Sub) for the Course Name Textbox and within the cmdAdd (Sub), and cmdTraining (Sub). Highlighted the VLookup sections with "-------------" Top and Bottom.

So when the Form loads and I select, from the drop-down [Course Code], the Course Name is not populated. The cmdTraining (Sub) has a different Lookup than the cmdAdd and Reg7 (Sub) I was testing different options.

So I guess I really need to understand what is happening and why? There is obviously a better solution, but I do not know what it is and I have been trying for a week.

Receiving the following error 1004, unable to get VLookup property of the WorksheetFunction class, from the Reg7 (Sub).

 

[MarkMueller]

Missed where you had the cod, why is it in the Reg7 change event, rather than the reg6 change event?

I guess, I thought that the change was occurring in Reg7 and not Reg6. Reg6 is the initial check to see if Reg7 has similar detail. in another Sub, I attempted to do the same thing but highlighted the specific criteria via using Right and Left text commands. Code below:

'add value to the next row in the database
nextrow = Reg1.Value ' FName
nextrow.Offset(0, 1) = Reg2.Value ' LName
nextrow.Offset(0, 2) = Reg3.Value ' Grade
nextrow.Offset(0, 3) = Reg4.Value ' StaffID
nextrow.Offset(0, 4) = Reg5.Value ' Role
nextrow.Offset(0, 5) = Reg6.Value ' Course Code
' This section Below may not be working - added Msgbox.
' Set ws = ActiveSheet
If (Right(Reg6, 3)) = (Left(Reg7, 3)) Then
Reg7.Value = Application.VLookup(Reg7.Value, Sheet3.Range("H:I"), 8, 0)
' ++++++ Message Box for Reg 7 ++++++++
MsgBox (" +++ Variable ++++ = " & Reg7)
End If
nextrow.Offset(0, 6) = Reg7.Value ' Course Name
nextrow.Offset(0, 7) = Reg8.Value ' Version

 

[MarkMueller]

I think I have become confused as to what is occurring, fairly new at Excel VBA, the code above is from the cmdADD Sub and relates to the Add New Trainers (Command Button). Still do not know the correct syntax for the Sub Reg7. Sorry for not being very helpful, I think.

 

[Fluff]

Try putting the code in the reg6 click event like

Private Sub Reg6_Click()
Reg7.Value = Application.VLookup(Reg6.Value, Sheet3.Range("LookCourse"), 2, 1)
MsgBox Reg7
MsgBox Reg6
End Sub

 

[MarkMueller]

Try putting the code in the reg6 click event like

Private Sub Reg6_Click()
Reg7.Value = Application.VLookup(Reg6.Value, Sheet3.Range("LookCourse"), 2, 1)
MsgBox Reg7
MsgBox Reg6
End Sub

That is just about it. Thanks a lot.

 

[MarkMueller]

Just so much going on in this project. I appreciate your help.

 

[Fluff]

You're welcome & thanks for the feedback.