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).

 

[Fluff]

How is that combo being populated?

 

[MarkMueller]

The concept is to use the UserForm to represent what is occurring with the Training Workbook via the Data and Lists Worksheets. The Form utilizes controls at the bottom to provide the ability to Add New Trainers (Command Button) of which is Coded, Update Training (Command Button) does changes entered within the Text Box's above. So initially I use the Role drop-down to select a role and then click Lookup Staff (Command Button). Data from Sheet2 (Criteria selects) then Extract lists the data within the Forms Listbox. Double-clicking a selection from items within the Listbox then populates the textboxes below the Listbox.

 

[Fluff]

That does not answer my question.

 

[MarkMueller]

So, all Comboboxe's are loaded from the Lists sheet as name ranges and then the name range is added to the RowSouce in the items properties.

 

[MarkMueller]

That does not answer my question.

Look at the response above, I was typing the info (Not answering your question, for additional detail).

 

[MarkMueller]

Also attached the Lists Sheet via an Image in my initial posting. e.g. it is the first image. Hope that helps, if not let me know what I need to provide!

 

[Fluff]

In that case, as long as you are selecting an item from the list & not free-typing, I can see no reason why you would get the error.

 

[MarkMueller]

' Update Reg7 (Course Name in Control Area), based on Lookup from entry into Reg6 Control (DropDown)
Private Sub Reg7_Change()
Reg7.Value = Application.WorksheetFunction.VLookup(Reg6.Value, Sheet3.Range("LookCourse"), 2, 1)
MsgBox Reg7
MsgBox Reg6
End Sub

(Reg7 Represent the Course Name), (Reg6 Represents the Course Code)
The above Sub is creating the error: Run-time error 91: Object variable or With block variable not set.

 

[MarkMueller]

In that case, as long as you are selecting an item from the list & not free-typing, I can see no reason why you would get the error.

Selecting from drop-down only.

 

[Fluff]

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