Userform Combo Box Search and Pull Results

[gilly01625]

Hey,

I have the below userform which is being used to create an invoice. My aim is the have the user select a reference number, which is linked to a record of worked hours from a worksheet, in the combo box at the start of each item (items 1 through 10) - once a reference number has been selected, the text boxes in said row are populated with data linked to the reference (description, unit/hours, pay, and total).

Is this possible?

Thanks

 

[gilly01625]

Can anyone help please?

 

[mumps]

It is hard to work with pictures. Also, it would be helpful if we could see the sheet containing the record of worked hours. It would be easier to help if you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[gilly01625]

It is hard to work with pictures. Also, it would be helpful if we could see the sheet containing the record of worked hours. It would be easier to help if you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Apologies - new to forums. Here is a link to my spreadsheet - Dropbox

Here is what I am trying to achieve:
The user inputs a range of data using 'frmWork' - the information from this userform is added to worksheet 'Work' when the user presses 'btnAdd' on the userform.
The user then opens 'frmInvoice', and using combo boxes ('cmbItemRef1', 'cmbItemRef2' etc) they can then select a reference number ('OLUK-WREF-0001', 'OLAUS-WREF-0002' etc) which links to the data inputted on 'frmWork' now displayed in a table on worksheet 'Work'.
Once a reference number has been selected in a combo box, the data corresponding to that reference number is pulled onto the userform ('frmInvoice'). For example, the user selected reference 'OLUK-WREF-0001' which has been populated into the table on row 7, the textbox 'txtUnitHours1' pulls its data from cell 'R7' on worksheet 'Work', textbox 'txtPay1' pulls its data from cell 'AA7' on worksheet 'Work' etc.

I hope that makes more sense.

Thanks for any help you can provide

 

[mumps]

When I open the frmInvoice userform, the REFERENCE combo boxes are all empty so there are no reference numbers to choose from. Also, where is the DESCRIPTION located on the Work sheet?

 

[gilly01625]

When I open the frmInvoice userform, the REFERENCE combo boxes are all empty so there are no reference numbers to choose from. Also, where is the DESCRIPTION located on the Work sheet?

I haven't added the code to populate the combo boxes yet - was waiting to see if anyone had any ideas first - i have written code to populate, just not added yet.

The description text box is for user input - no data being pulled for this one.

 

[mumps]

Please upload a version of your file containing the code to populate the combo boxes.

 

[gilly01625]

Please upload a version of your file containing the code to populate the combo boxes.

Here is the updated file - Dropbox

Thanks

 

[mumps]

Place this macro in the userform code module. The macro refers to the first combobox cmbItemRef1. Simply copy/paste this macro another 9 times changing the number "1" (in red) in the combobox name (two occurrences) and the three textbox names to the numbers 2 to 10 so you have 10 macros, one for each combobox.

Private Sub cmbItemRef1_Change()
    Application.ScreenUpdating = False
    Dim ref As Range
    Set ref = Sheets("Work").Range("D:D").Find(cmbItemRef1.Value, LookIn:=xlValues, lookat:=xlWhole)
    txtUnitHours1 = ref.Offset(, 14).Value
    txtPay1 = ref.Offset(, 22).Value
    txtTotal1 = ref.Offset(, 23).Value
    Application.ScreenUpdating = True
End Sub

 

[gilly01625]

Place this macro in the userform code module. The macro refers to the first combobox cmbItemRef1. Simply copy/paste this macro another 9 times changing the number "1" (in red) in the combobox name (two occurrences) and the three textbox names to the numbers 2 to 10 so you have 10 macros, one for each combobox.

Private Sub cmbItemRef1_Change()
    Application.ScreenUpdating = False
    Dim ref As Range
    Set ref = Sheets("Work").Range("D:D").Find(cmbItemRef1.Value, LookIn:=xlValues, lookat:=xlWhole)
    txtUnitHours1 = ref.Offset(, 14).Value
    txtPay1 = ref.Offset(, 22).Value
    txtTotal1 = ref.Offset(, 23).Value
    Application.ScreenUpdating = True
End Sub

Thats perfect! Thank you so much