PDA

View Full Version : Listbox



BlueDNA
04-06-2006, 08:03 PM
Hi all,

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


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 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. :hi:

David

matthewspatrick
04-07-2006, 06:13 AM
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
04-09-2006, 04:11 PM
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
04-09-2006, 04:18 PM
I also took a look at this:


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

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
04-09-2006, 04:44 PM
What about using data validation instead? This will allow you to create a list within a cell that is populated by a range.

BlueDNA
04-09-2006, 04:58 PM
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
04-09-2006, 05:20 PM
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. :think:

lucas
04-09-2006, 05:36 PM
If you use a range your list can be on a different sheet and that sheet can be hidden....

BlueDNA
04-09-2006, 05:47 PM
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
04-09-2006, 06:07 PM
Yes, you are wrong :tongue2: .

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
04-09-2006, 06:29 PM
nice! cool!! :cloud9:

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

How would the code change?

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

Thanks for all your help!

geekgirlau
04-09-2006, 06:40 PM
Unfortunately data validation will not accept a range name from another workbook, but it's a good question!

BlueDNA
04-09-2006, 06:41 PM
yeah i just tried...wouldnt work. bugger...

BlueDNA
04-09-2006, 06:58 PM
Yes, you are wrong :tongue2: .

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
04-09-2006, 09:04 PM
Type "=NewRange" as the source

BlueDNA
04-09-2006, 09:09 PM
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! :banghead:

BlueDNA
04-09-2006, 09:44 PM
Thanks geek girl for the help!!

Appreciate it!

Just 2 more little things.


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?
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.:cool:

geekgirlau
04-11-2006, 06:31 PM
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.