sorry
but it gives me error in
=OFFSET(LookupLists!$A$2, 0, 0, COUNTA(LookupLists!$A$A)-1,1)
and at run time gives range error
Discussion on:
View:
Show:
=OFFSET(LookupLists!$A$2, 0, 0, COUNTA(LookupLists!$A:$A)-1,1) is working. The colon was missing in the COUNTA range
Nice catch -- thank you for helping out. I've corrected the expression in the article.
How do you copy the value selected by the combo box to a data range?
You can either assign the value to a variable or you can make a direct assignation to a specific cell (by address or name):
Range("C5") = Me.cboColor.Value
... and a simpler way of populating the list is the enter the range name (ColorList) in the "RowSource" property of the combo box. You can also assign it using VBA:
cboColor.RowSource = "ColorList"
Range("C5") = Me.cboColor.Value
... and a simpler way of populating the list is the enter the range name (ColorList) in the "RowSource" property of the combo box. You can also assign it using VBA:
cboColor.RowSource = "ColorList"
The solution is, for me, a bit complex. The RefersTo formula to define the list is somewhat less than transparent. It is also important that column A of the Sheet ColorLists is not used for anything other than the list items because the expression COUNTA(ColorLists!$A:$A) would no longer count only the list entries.
When I have done somethign similar I have, as DuhGreek suggests, used the RowSource property to define the combo box items. The range ColorList can be defined simply as ColorLists!$A$2:$A$6. A data entry rule is required that new list entries are made by using Insert Cells somewhere between the first and last entries of the list so that the range automatically adjusts to the new number of entries.
When I have done somethign similar I have, as DuhGreek suggests, used the RowSource property to define the combo box items. The range ColorList can be defined simply as ColorLists!$A$2:$A$6. A data entry rule is required that new list entries are made by using Insert Cells somewhere between the first and last entries of the list so that the range automatically adjusts to the new number of entries.
That would definitely work -- it requires more code, which is something I tend to avoid when offering solutions, but in the long run might be more manageable as you say -- thanks for sharing this solution!
I am trying to follow the explanation but when i am trying to run the userform, i am getting this:
Run-time error '9'
Subscript out of range.
Help?
Run-time error '9'
Subscript out of range.
Help?
try changing sheet name to Lookuplist. . it is mentioned in the article to set the sheet name containing look up list to Lookup list.
This is a great thread with a lot of helpful ideas. The list I'm using to populate my userform combo box has duplicate items in it. I'd like the userform list to pull a list of unique values from that list. Anyone know how to add that twist?
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
