dynamic combobox

I have a worksheet that lists a number of categories to populate a comobobox

category.List = Worksheets("category").Range("A2:A10").Value

This works great, however I want to be able to vary the number of rows listed here and have the combobox display the rows dynamicly.

I searched through the forums and found a post that looked like the answer.

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

Unfortunatley I get a compile error "Invalid character" at the first $

What does this mean?

Am I on the right track?

I'm using Office 97 if that makes a difference.

Hi john! If you look at the properties inspector for combo box, you'll find a 'ListFillRange' property. You can set this to a range where you want to get the list of values from, e.g. "A:A" - for column "A", "A1:A10" for cells A1-A10, etc... Unfortunately, empty values will also be included. So, if your range contains empty cells, your list will also have empty entries. If you don't want this, maybe you can try the code below.

Function NonEmpty receives a single column range, scans for non-empty values in the range and stores them in an array. The array returned, thus, has empty values removed (except for 1 - last).

Worksheet_Change (test sub) is the event hander for the worksheet where the list of values is located. I'm assuming you're using column A as the list. I'm also assuming you have the combo box (named ComboBox1) in the sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then   'if data has been added to (deleted from/modified in) column A - the combobox "reference" column, update the combo box
        Dim ComboList As Range
        Set ComboList = Range("A1:" & Range("A65536").End(xlUp).AddressLocal)

        If ComboList.Rows.Count = 1 Then
            Set ComboList = Range("A1:A2")
        End If

        ComboBox1.List = NonEmpty(ComboList)
    End If
End Sub

'Removes empty values...
Function NonEmpty(ByVal Target As Range) As Variant
    Dim NEArray() As Variant
    Dim NECount As Long
    Dim NEStart As Long
    Dim NEEnd As Long
    Dim NEIndex As Long
    
    ReDim NEArray(Target.Rows.Count)
    NEStart = LBound(NEArray)   '0 or 1 depending on Option Base
    NEEnd = UBound(NEArray)
    NECount = NEStart
    
    For NEIndex = NEStart To NEEnd
        If (Not IsEmpty(Target.Rows(NEIndex + 1 - NEStart))) Then
            NEArray(NECount) = Target.Rows(NEIndex + 1 - NEStart).Value
            NECount = NECount + 1
        End If
    Next NEIndex
    
    ReDim Preserve NEArray(NECount)
    
    NonEmpty = NEArray
End Function

Hope this helps...

Originally Posted by john_t_h

I have a worksheet that lists a number of categories to populate a comobobox

category.List = Worksheets("category").Range("A2:A10").Value

This works great, however I want to be able to vary the number of rows listed here and have the combobox display the rows dynamicly.

I searched through the forums and found a post that looked like the answer.

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

Unfortunatley I get a compile error "Invalid character" at the first $

What does this mean?

Am I on the right track?

I'm using Office 97 if that makes a difference.

Hi, thanks for the reply.

I actually figured out what my problem is. Being a VB novice (my coding background is PHP) I was trying to declare a variable then use it to fill the combobox

' this give me an error
catRange = OFFSET(category!$A$1,0,0,COUNTA(category!$A:$A,1))
category.List = Worksheets("category").Range("catRange").Value

When I included it all in one it works fine

' this works fine
category.List = Worksheets("category").Range("OFFSET(category!$A$1,0,0,COUNTA(category!$A:$A,1))").Value

Hi John

It looks like the post you found was referring to creating a dynamic
named range, probably for a data validation list.

This would work by creating a named range using:

=OFFSET(category!$A$1,0,0,COUNTA(category!$A:$A),1) < note minor change

in the Refers To field. Assuming your named range is called MyRange you
could then select a cell and use Data>Validation. Allow List, Source
=MyRange. This would give you a drop down in the cell which would
dynamically increase according to the data in column A.

To populate a combobox dynamically you could loop through the the
contents of column A and add items to the combobox. This example assumes
headings so starts in row 2:

Dim i As Long
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Me.ComboBox1.AddItem Cells(i, 1).Value
Next i

Hope this helps
Rowan

john_t_h wrote:
> I have a worksheet that lists a number of categories to populate a
> comobobox
>
>
> Code:
> --------------------
> category.List = Worksheets("category").Range("A2:A10").Value
> --------------------
>
>
> This works great, however I want to be able to vary the number of rows
> listed here and have the combobox display the rows dynamicly.
>
> I searched through the forums and found a post that looked like the
> answer.
>
>
> Code:
> --------------------
> catRange = OFFSET(category!$A$1,0,0,COUNTA(category!$A:$A,1))
> --------------------
>
>
> Unfortunatley I get a compile error "Invalid character" at the first
> *$*
>
> What does this mean?
>
> Am I on the right track?
>
> I'm using Office 97 if that makes a difference.
>
>

Originally Posted by Rowan

This would work by creating a named range using:

=OFFSET(category!$A$1,0,0,COUNTA(category!$A:$A),1) < note minor change

Thanks Rowan,

Minor change you suggested got rid a blank entry in the combobox

Now that this working great it occured to me that once the form writes the row the worksheet, users can still go in and corrupt the data by over-typing. The whole reason to use a UserForm was to maintain consistaincy in the values to allow sorting and reporting.

Is there a way when the value it inserted from the Userform, the worksheet cell is formated as a combobox. Obviously the original form value would be displayed, but I would like the users to be able to select one of the other values at a later date.

This is a cutdown version of my form submit code (at home now and my real one is at work)

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.ComboBox1.Value

'clear the data
Me.ComboBox1.Value = ""

End Sub

Is there something I can do at this ws.Cells(iRow, 1).Value = Me.ComboBox1.Value stage to provide the cell format?

Hi John

I would add data validation to the cell so that users can only select
values from the list as it appears on the form.

First you would need to add a named range using the same offset formula
used for the combobox. You could either do this manually (it only needs
to be done once) or via code eg:

ActiveWorkbook.Names.Add Name:="theList", RefersToR1C1:= _
"=OFFSET(category!R1C1,0,0,COUNTA(category!C1),1)"

You click event could then look like this;

Code:
--------------------
Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'Add data validation
With ws.Cells(iRow, 1).Validation
.Delete
.Add Type:=xlValidateList, Formula1:="=theList"
.IgnoreBlank = True
End With

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.ComboBox1.Value

'clear the data
Me.ComboBox1.Value = ""

End Sub

--------------------

Hope this helps
Rowan

john_t_h wrote:
> Rowan Wrote:
>
>>This would work by creating a named range using:
>>
>>=OFFSET(category!$A$1,0,0,COUNTA(category!$A:$A),1) < note minor
>>change
>>
>>
>
>
> Thanks Rowan,
>
> Minor change you suggested got rid a blank entry in the combobox
>
> Now that this working great it occured to me that once the form writes
> the row the worksheet, users can still go in and corrupt the data by
> over-typing. The whole reason to use a UserForm was to maintain
> consistaincy in the values to allow sorting and reporting.
>
> Is there a way when the value it inserted from the Userform, the
> worksheet cell is formated as a combobox. Obviously the original form
> value would be displayed, but I would like the users to be able to
> select one of the other values at a later date.
>
> This is a cutdown version of my form submit code (at home now and my
> real one is at work)
>
>
> Code:
> --------------------
> Private Sub CommandButton1_Click()
> Dim iRow As Long
> Dim ws As Worksheet
> Set ws = Worksheets("Sheet1")
>
> 'find first empty row in database
> iRow = ws.Cells(Rows.Count, 1) _
> .End(xlUp).Offset(1, 0).Row
>
> 'copy the data to the database
> ws.Cells(iRow, 1).Value = Me.ComboBox1.Value
>
> 'clear the data
> Me.ComboBox1.Value = ""
>
> End Sub
>
> --------------------
>
>
> Is there something I can do at this ws.Cells(iRow, 1).Value =
> Me.ComboBox1.Value stage to provide the cell format?
>
>

Hi Rowan,

Could you expand this part for me

ActiveWorkbook.Names.Add Name:="theList", RefersToR1C1:= _
"=OFFSET(category!R1C1,0,0,COUNTA(category!C1),1)"

ActiveWorkbook - no problems
Names - I'm guessing the worksheet name
Add Name - Where does this come from?
:="theList", Assigning the range to this variable
RefersToR1C1 - Where does this come from?
OFFSET(category!R1C1,0,0,COUNTA(category!C1),1) - Issume I can still use OFFSET(category!$A$1,0,0,COUNTA(category!$A:$A),1) to allow the list to be dynamic?

Also is the data validation limited to 8 rows? I thought I read somewhere that it was??

Thanks

Originally Posted by Rowan

Hi John

I would add data validation to the cell so that users can only select
values from the list as it appears on the form.

First you would need to add a named range using the same offset formula
used for the combobox. You could either do this manually (it only needs
to be done once) or via code eg:

ActiveWorkbook.Names.Add Name:="theList", RefersToR1C1:= _
"=OFFSET(category!R1C1,0,0,COUNTA(category!C1),1)"

You click event could then look like this;

Code:
--------------------
Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'Add data validation
With ws.Cells(iRow, 1).Validation
.Delete
.Add Type:=xlValidateList, Formula1:="=theList"
.IgnoreBlank = True
End With

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.ComboBox1.Value

'clear the data
Me.ComboBox1.Value = ""

End Sub

--------------------

Hope this helps
Rowan

john_t_h wrote:
> Rowan Wrote:
>
>>This would work by creating a named range using:
>>
>>=OFFSET(category!$A$1,0,0,COUNTA(category!$A:$A),1) < note minor
>>change
>>
>>
>
>
> Thanks Rowan,
>
> Minor change you suggested got rid a blank entry in the combobox
>
> Now that this working great it occured to me that once the form writes
> the row the worksheet, users can still go in and corrupt the data by
> over-typing. The whole reason to use a UserForm was to maintain
> consistaincy in the values to allow sorting and reporting.
>
> Is there a way when the value it inserted from the Userform, the
> worksheet cell is formated as a combobox. Obviously the original form
> value would be displayed, but I would like the users to be able to
> select one of the other values at a later date.
>
> This is a cutdown version of my form submit code (at home now and my
> real one is at work)
>
>
> Code:
> --------------------
> Private Sub CommandButton1_Click()
> Dim iRow As Long
> Dim ws As Worksheet
> Set ws = Worksheets("Sheet1")
>
> 'find first empty row in database
> iRow = ws.Cells(Rows.Count, 1) _
> .End(xlUp).Offset(1, 0).Row
>
> 'copy the data to the database
> ws.Cells(iRow, 1).Value = Me.ComboBox1.Value
>
> 'clear the data
> Me.ComboBox1.Value = ""
>
> End Sub
>
> --------------------
>
>
> Is there something I can do at this ws.Cells(iRow, 1).Value =
> Me.ComboBox1.Value stage to provide the cell format?
>
>

Hi John

This method adds a named range to the workbooks Names collection. So
Activeworkbook.Names is the collection of all names in the book which
includes named ranges, print_area and a few others.

ActiveWorkbook.Names.Add therefore adds a new name to this collection
and in this example I used "theList" as the name.

The RefersToR1C1 is using the R1C1 notation to set the range for the
named range and hence "=OFFSET(category!R1C1,0,0,COUNTA(category!C1),1)".

You could also use the A1 notation:

ActiveWorkbook.Names.Add Name:="theList", RefersTo:= _
"=OFFSET(category!$A$1,0,0,COUNTA(category!$A:$A),1)"

You are right that the drop down validation is restricted to 8 visible
options. The rest can be accessed with the scroll bar. I don't think
this can be changed.

Regards
Rowan

john_t_h wrote:
> Hi Rowan,
>
> Could you expand this part for me
>
>
> Code:
> --------------------
> ActiveWorkbook.Names.Add Name:="theList", RefersToR1C1:= _
> "=OFFSET(category!R1C1,0,0,COUNTA(category!C1),1)"
> --------------------
>
>
> ActiveWorkbook - no problems
> Names - I'm guessing the worksheet name
> Add Name - Where does this come from?
> :="theList", Assigning the range to this variable
> RefersToR1C1 - Where does this come from?
> OFFSET(category!R1C1,0,0,COUNTA(category!C1),1) - Issume I can still
> use OFFSET(category!$A$1,0,0,COUNTA(category!$A:$A),1) to allow the
> list to be dynamic?
>
> Also is the data validation limited to 8 rows? I thought I read
> somewhere that it was??
>
> Thanks