VBScripting in Excel

Question

4.00/5 (1 vote)

See more:

Hi,

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:

VB

 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
  cboTemp.Activate
  wbSource.Close
End Sub

Please help me in getting the values binded to dropdown list

Posted 26-Apr-13 22:47pm

Member 10013526

Updated 26-Apr-13 23:25pm

Kenneth Haugland

v4

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2013-04-28T08:55:00

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