Click here to Skip to main content
15,846,211 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:

I have created two workbooks, one workbook contains a set of dropdown lists, another workbook contains the data.
I m trying to bind the data from WB2 to dropdown in WB1 using VBScript.
The problem is, the dropdown is not showing the data, instead, it is showing empty rows inside..
Here is my code:
 Private Sub Worksheet_Change(ByVal Target As Range)
   Dim wbSource
   Dim cboTemp As OLEObject
   Dim ws As Worksheet
   Set ws = ActiveSheet
   Set wbSource = Workbooks.Open("..\InputData.xlsx")
   Set cboTemp = ws.OLEObjects("ComboBox4")
   On Error Resume Next
   With cboTemp 
   .Visible = True
   .Left = Target.Left
   .Top = Target.Top
   .Width = Target.Width + 15
   .Height = Target.Height + 5
   .ListFillRange = "'[InputData.xlsx]Availability'!$B$2:$C$9"
   .LinkedCell = Target.Address
  End With
End Sub

Please help me in getting the values binded to dropdown list
Updated 27-Apr-13 0:25am

1 solution

Assuming this function actually gets called (you can confirm this by putting a breakpoint in there) then it largely works.

Your problem is with wbSource.Close - if you put a break point on cboTemp.Activate and then look at your combobox you will see that it is populated. As soon as you close the workbook "InputData" the source for the combobox disappears and the list is emptied.

You need to keep the source sheet open for as long as you want the data in the combobox
Share this answer
Maciej Los 28-Apr-13 17:37pm    

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900