Listbox Additem + ListFillRange

[f2e4]

I have a listbox with linked to a dynamic range "staff_list"

Is it possible to add additional items as well as the above range.


So far I have tried adding the above range to the ListFillRange property and then then following code:


Private Sub Workbook_open()

With Sheets("Reports").ListBox1
.AddItem "Croydon"
.AddItem "London - SCH"
.AddItem "South East Region"
End With

End Sub


I keep getting a Run-time error 70: permission denied. The microsoft site says it is because I already have a range assigned and cannot use additem.


Anyone got any ideas?

[lucas]

I'm confused. Why would you add items on workbook open with code if you have a dynamic range that populates the listbox? Why not just add them to the dynamic range and be done?

[f2e4]

I wish i could

The whole point of this task was to see if I could use one Listbox rather than 2

Listbox 1 = List of people
Listbox 2 = 3 Locations

I just wanted to get rid of the 2nd Listbox and add the 3 locations to Listbox1.

I can't add them to my dynamic range because it is a list of staff names only and must stay that way.

The only way I can see of adding the 3 locations is through additem.

[lucas]

post an example.....there surely is a way to do this. Maybe we can copy the list of names to a different dynamic range and add the locations there and populate the listbox from that.....

[f2e4]

post an example.....there surely is a way to do this. Maybe we can copy the list of names to a different dynamic range and add the locations there and populate the listbox from that.....OK thanks for the help.

See attached file (added some notes as well)


Also:

I noticed my list of staff members also contains which location they are at.

Myabe we could use that range (consolidated down to 3 rather than all the duplicated) instead of having a seperate dynamic range for the locations listed in a separate sheet

[lucas]

Myabe we could use that range (consolidated down to 3 rather than all the duplicated) instead of having a seperate dynamic range for the locations listed in a separate sheet

Do you mean that you don't wish to show the location next to each name? If so then how exactly do you think this will look.....a list of names in one column and then just the 3 locations in a column by themselves....not associated with the names??

[f2e4]

Think i'm not wording this right...

I want 1 list containing the 3 locations at the top then the staff list below

[lucas]

I'm not trying to hijack your project but if I could make a suggestion....what if you have your list as it is and a textbox where the second listbox is and when they click on a name the textbox will reflect the location?

[f2e4]

The main reason I wanted 1 listbox is because, at the moment, whatever value is selected from either the staff or location listbox, a different range is created and used in a chart.

I just thought that there must be some way of creating one listbox rather than having 2 separate ones.

The sheet I attached was just an extract from a very large spreadsheet which has 3 charts and another listbox that all change depending on the value selected from either the staff or location listboxes.

[lucas]

If that's the case then you will probably need to create another sheet that is a copy of staff_list and add the locations with code. use that to populate your listbox.