Using a named range in VBA 1

[avandale]

Hi Guys,

I'm using a VBA form in Excel to access staff absence files. At the momnent, I'm using the ".add item" piece of code to add a name for each person, so that they can be accessed using a drop list on the form.

Is it possible to reference a named range in Excel, so that when the named range is updated, so too will the drop down list info?

Any help would be really appreciated!!!!

 

[SQLBI]

Hi,

Create your list on a sheet in your workbook and name it.

Then, in the ComboBox properties set the RowSource property to your named range.

Hope this helps.


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[combo]

You can assign at once values in named range "TheList" to list:
[tt]Private Sub UserForm_Initialize()
Dim v As Variant
v = Range("TheList&quot
ComboBox1.List = v[/tt]
...

It is also possible to link the list with sheet, set control's property 'RowSource' to: =TheList

combo

 

[avandale]

Thansk Leighmoore,

It worked like a charm!!!