Microsoft Excel - Load cell values based on combobox selected item from another workbook

Asked By KLNP on 13-Jun-14 12:41 PM
I have (workbook "Transaction") userform combobox in spreadsheet. I have loaded the items to combobox from another workbook called Master. Now I have to get the values loaded based on combobox selected item from workbook "Master" to workbook " Transaction". I need excel vba code for this. 
Harry Boughen replied to KLNP on 14-Jun-14 08:45 PM
Hi KLNP,
It would be nice to have some idea of what and how the data to be selected is defined.  A sample layout for each workbook with the relevant bits highlighted could be a start.
Regards
Harry
KLNP replied to Harry Boughen on 16-Jun-14 03:09 AM
Dear Sir,
    I have loade the values to combobox from Master Workbook like this.

Public Sub combobox21_Change()
Application.ScreenUpdating = False
Dim wbsource As Workbook
Dim ws As Worksheet
Dim i As Integer
Dim j As Integer
Dim rngsource As Range
Dim rngsource1 As Range
Set wbsource = Workbooks.Open("c:\users\pc\my documents\Master.xlsm")
Sheets("Code Master").Select
Worksheets("Code Master").Activate
Set rngsource = Range("B1:B" & Sheets("Code Master").Cells(Rows.Count, "B").End(xlUp).Row)
Sheet1.ComboBox1.Clear
j = 2
For i = 1 To rngsource.Rows.Count

    Sheet1.ComboBox1.AddItem (rngsource.Item(j))
    j = j + 1
Next i
 
 
wbsource.Close

 
end Sub

Now I want to load the cell values from Master  to transaction workbook (sheet name called " sheet1") based on the combobox selection.

Please help me.

Regards


KLNP replied to Harry Boughen on 16-Jun-14 03:10 AM
Dear Sir,
    I have loade the values to combobox from Master Workbook like this.

Public Sub combobox21_Change()
Application.ScreenUpdating = False
Dim wbsource As Workbook
Dim ws As Worksheet
Dim i As Integer
Dim j As Integer
Dim rngsource As Range
Dim rngsource1 As Range
Set wbsource = Workbooks.Open("c:\users\pc\my documents\Master.xlsm")
Sheets("Code Master").Select
Worksheets("Code Master").Activate
Set rngsource = Range("B1:B" & Sheets("Code Master").Cells(Rows.Count, "B").End(xlUp).Row)
Sheet1.ComboBox1.Clear
j = 2
For i = 1 To rngsource.Rows.Count

    Sheet1.ComboBox1.AddItem (rngsource.Item(j))
    j = j + 1
Next i
 
 
wbsource.Close

 
end Sub

Now I want to load the cell values from Master  to transaction workbook (sheet name called " sheet1") based on the combobox selection.

Please help me.

Regards


Harry Boughen replied to KLNP on 16-Jun-14 06:24 AM
OK,
So you have all of the values contained in columnB as items in a dropdown and you select one.  Am I right in assuming that you wish to transfer all of the data from the corresponding row in the source book to the target book?  Or do you only require particular cells from that row?
Regards
Harry
KLNP replied to Harry Boughen on 16-Jun-14 07:28 AM
Dear Sir,
   I want particular cells from source to be loaded to particular cell of target book.

Regards
Harry Boughen replied to KLNP on 16-Jun-14 08:16 AM
Can you indicate which cells from the source to which cells in the target?
Harry
KLNP replied to Harry Boughen on 16-Jun-14 08:44 AM
Dear Sir,
     The target workbook (Sheet1) will have the value at A7 from the source workbook I7, B7 target workbook will have the value of source workbook J7. Whenever I change the value of combobox the value of A7 and B7 have to be changed. Like wise I have around 25 values to be loaded to target workbook on selection of combobox item. Please help me.


Regards
Harry Boughen replied to KLNP on 16-Jun-14 04:25 PM
Does that mean that the range in the source file is I?:AG? where ? is the row number or are the source cells scattered across a wider range of columns?
Is the row in the target sheet always row 7?
Regards
Harry
KLNP replied to Harry Boughen on 16-Jun-14 11:47 PM
Dear Sir,
    The row is same. I7 to AB7. The data contains in the same row.

Harry Boughen replied to KLNP on 17-Jun-14 03:00 AM
Which cell is the combobox linked to?
Harry
KLNP replied to Harry Boughen on 17-Jun-14 05:07 AM
Dear Sir,
Combobox is in the cell J6 in target workbook.
Harry Boughen replied to KLNP on 17-Jun-14 05:43 AM
Hello again,
This is the bare bones of something that might do what you want.  You will need to add the necessary file paths etc to point to your master workbook.  This is entered as a combobox change event macro.

Private Sub ComboBox1_Change()

Range("A7").Resize(1, 20).Value = Sheets("Sheet2").Range("I1").Offset(ComboBox1.ListIndex, 1).Resize(1, 20).Value

End Sub

Regards
Harry
KLNP replied to Harry Boughen on 18-Jun-14 10:19 AM
Dear Sir,
    Thanks for your kind help. It is working. Thank you once again

Regards