Dynamically populate values in Listbox in VBA

Question

Thanks in Advance.

I have a listbox(say lstbox) in "Sheet1" and Range of values in column A in "Sheet2". The listbox should populate values dynamically from Column A in Sheet2. I have tried with named reference also but it is not working. Any help on this is very appreciated

show some of your macro code for more precise help please... — MacroMarc
– MacroMarc, Commented Oct 31, 2015 at 21:13
pls "record" a macro where you change the input-range and then try to change it byyour needs. if you still ned help, let us know :) — Dirk Reichel
– Dirk Reichel, Commented Oct 31, 2015 at 21:13

amg · Accepted Answer · 2015-11-01 14:10:58Z

1

First you need to create a dynamic name range by going in to "Formulas" Tab in which click on "Name Manager" and insert on "New" button.

Enter the Name for range (I have given the name as TestValue) and then use Following Formula at "Refers To area" as "=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)" without Quotation Mark.

After that go to sheet 1 and add ListBox from ActiveX control. Right click on it go to "View Code".

Erase every thing in it and type the below code.

Private Sub ListBox1_GotFocus()
    ListBox1.ListFillRange = "TestValue"
End Sub

That's it. When you click on list box it will populate it.

answered Nov 1, 2015 at 14:10

amg

1416 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamically populate values in Listbox in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related