Listbox

[BlueDNA]

Hi all,

Just a few questions i was hoping to get some help with. Any suggestions would be appreciated.

1. Is it possible to assign all the cells in a range to be of list box type in VBA code? e.g. Range A1:A10 = listbox("item 1" , "Item 2"..."Item X")
2. Is it possible to set a column property so that when you hide it (Columns("B").Hidden = True) - copying and pasting records does not copy into the hidden columns.

Thanks again.

David

[matthewspatrick]

Is it possible to assign all the cells in a range to be of list box type in VBA code? e.g. Range A1:A10 = listbox("item 1" , "Item 2"..."Item X")

Is this listbox on a UserForm, or a worksheet? If the latter, is it from the Forms or Controls toolbar?

Is it possible to set a column property so that when you hide it (Columns("B").Hidden = True) - copying and pasting records does not copy into the hidden columns.

Not easily, except by protecting the worksheet and making sure that the cells in that hidden column are locked.

[BlueDNA]

Is this listbox on a UserForm, or a worksheet? If the latter, is it from the Forms or Controls toolbar?

I would prefer to have it in a worksheet. What would be more realistic?

Not easily, except by protecting the worksheet and making sure that the cells in that hidden column are locked.

ok ill give that a go. thanks!

[BlueDNA]

I also took a look at this:

[VBA]
Sub AddList()
[A1] = "test1"
[A2] = "test2"
[A3] = "test3"
[A4] = "test4"
[A5] = "test5"
Dim lb
With Worksheets(1)
Set lb = .Shapes.AddFormControl(xlListBox, [G3].Left, [G3].Top, [G3].Width, [G3].Height)
lb.ControlFormat.ListFillRange = "A1:A5"
End With
End Sub
[/VBA]
but its not exactly what im looking for. This essentially places a list box label on top of a cell? I would like to actually define a drop down for a range of cells - (entire column).

[geekgirlau]

What about using data validation instead? This will allow you to create a list within a cell that is populated by a range.

[BlueDNA]

Not easily, except by protecting the worksheet and making sure that the cells in that hidden column are locked.

Ok i know what you mean. Locking only works when u have protected a worksheet. And even then, it doesnt go down to only column locking. That sort of sucks...

[BlueDNA]

What about using data validation instead? This will allow you to create a list within a cell that is populated by a range.

Hi,

I know, but i would like to have the drop down items in code rather than in a worksheet - if you know what i mean.

I was hoping there was another alternative.

[lucas]

If you use a range your list can be on a different sheet and that sheet can be hidden....

[BlueDNA]

What about using data validation instead? This will allow you to create a list within a cell that is populated by a range.

Yeah i guess this is the only way to do it.

If you use a range your list can be on a different sheet and that sheet can be hidden....

Good point!

I was just thinking along the lines of, if the listbox items changed in future, u would need to respecify the range to all the affected cells - not really as dynamic as i would like. Unless im wrong?

[geekgirlau]

Yes, you are wrong .

Let's assume that the range for your data validation list is on a hidden sheet called "Lookup", in cells A1 to A22. What you do is create a dynamic range name - Insert | Name | Define. Type in the name for the range, and set the "Refers to" formula as

=OFFSET(Lookup!$A$1,0,0,COUNTA(Lookup!$A:$A),1)

This range will now cover all the populated cells in your list. If you add or remove items from the list, the size of the range name adjusts accordingly. Your data validation lists should all have their "Source" set to this dynamic range name.

[BlueDNA]

nice! cool!!

what about if i was referring the source to another sheet in another file?

How would the code change?

i.e. [VBA][filename.xls]sheetname.xlx'!$A:$A[/VBA]

Thanks for all your help!

[geekgirlau]

Unfortunately data validation will not accept a range name from another workbook, but it's a good question!

[BlueDNA]

yeah i just tried...wouldnt work. bugger...

[BlueDNA]

Yes, you are wrong .

Let's assume that the range for your data validation list is on a hidden sheet called "Lookup", in cells A1 to A22. What you do is create a dynamic range name - Insert | Name | Define. Type in the name for the range, and set the "Refers to" formula as

=OFFSET(Lookup!$A$1,0,0,COUNTA(Lookup!$A:$A),1)

This range will now cover all the populated cells in your list. If you add or remove items from the list, the size of the range name adjusts accordingly. Your data validation lists should all have their "Source" set to this dynamic range name.

Im a having a bit of trouble sorting this one out.

Sheet name: Lookup
Cells A1:A5: 1,2,3,4,5
Insert | Name | Define -> Range name = "NewRange"

Sheet name: Sheet2
Entire Column A
Data | Validation | Allow: Lists
Source: = ? how do you point to the dynamic range "NewRange"?

Sorry!!

[geekgirlau]

Type "=NewRange" as the source

[BlueDNA]

Type "=NewRange" as the source

Tried that already, but it seems to just interpret it as a string, not a defined name.

Edit: NVM, sheet name and Lookup wasnt consistent! my bad!

[BlueDNA]

Thanks geek girl for the help!!

Appreciate it!

Just 2 more little things.

1. Is it possible to set a default value and somehow underline the field with red if a an item typed in the cell does not exist in the drop down items specified? Perhaps a macro to do this?
2. How do i truncate/discard empty cells from appearing in the drop down (last item)? Some of the lookups are actually including one empty cell at the end of the range.

[geekgirlau]

1. What about using the Error Alert in the data validation instead?
2. If you've used OFFSET to define the range, you shouldn't have any blanks in there. Otherwise you need to redefine the range to exclude the blank cells; everything in the range will appear in your list.