dynamic combobox

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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

Hope this helps...

Originally Posted by john_t_h

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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

When I included it all in one it works fine

Please Login or Register  to view this content.

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)

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

I'm not sure what I'm doing wrong

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

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

When I insert the row the only option in the data validation box is "cat"

Instead of picking up the range and assigning it to "cat" it just returns "cat"

???

Originally Posted by Rowan

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

One character off!

.Add Type:=xlValidateList, Formula1:="=cat"

(That extra equal sign will mean that you're not creating a list.)



john_t_h wrote:
>
> I'm not sure what I'm doing wrong
>
> ActiveWorkbook.Names.Add Name:="cat", RefersTo:= _
> "=OFFSET(category!$A$1,0,0,COUNTA(category!$A:$A),1 )"
>
> 'Add data validation
> With ws.Cells(iRow, 4).Validation
> Delete
> Add Type:=xlValidateList, Formula1:="cat"
> IgnoreBlank = True
> End With
>
> When I insert the row the only option in the data validation box is
> "cat"
>
> Instead of picking up the range and assigning it to "cat" it just
> returns "cat"
>
> ???
>
> Rowan Wrote:
> > 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
>
> --
> john_t_h
> ------------------------------------------------------------------------
> john_t_h's Profile: http://www.excelforum.com/member.php...fo&userid=4826
> View this thread: http://www.excelforum.com/showthread...hreadid=461416

--

Dave Peterson

I managed to get this to work a couple of times but for all others I get this error:

Run-time error '-2147417848 (80010108)

Automation error
The object invoked has disconnected from its clients

I can not see what I did differently from when it works and when the error appears.

Any ideas?

Originally Posted by Dave Peterson

One character off!

.Add Type:=xlValidateList, Formula1:="=cat"

(That extra equal sign will mean that you're not creating a list.)



john_t_h wrote:
>
> I'm not sure what I'm doing wrong
>
> ActiveWorkbook.Names.Add Name:="cat", RefersTo:= _
> "=OFFSET(category!$A$1,0,0,COUNTA(category!$A:$A),1 )"
>
> 'Add data validation
> With ws.Cells(iRow, 4).Validation
> Delete
> Add Type:=xlValidateList, Formula1:="cat"
> IgnoreBlank = True
> End With
>
> When I insert the row the only option in the data validation box is
> "cat"
>
> Instead of picking up the range and assigning it to "cat" it just
> returns "cat"
>
> ???
>
> Rowan Wrote:
> > 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
>
> --
> john_t_h
> ------------------------------------------------------------------------
> john_t_h's Profile: http://www.excelforum.com/member.php...fo&userid=4826
> View this thread: http://www.excelforum.com/showthread...hreadid=461416

--

Dave Peterson

No.

The only time that I got that error, I had closed the wrong workbook and tried
to refer to it later in the code.

But I've seen lots of posts where this occurs. I don't recall seeing a nice
solution.

You may want to search google.

john_t_h wrote:
>
> I managed to get this to work a couple of times but for all others I get
> this error:
>
> > Run-time error '-2147417848 (80010108)
> >
> > Automation error
> > The object invoked has disconnected from its clients
> I can not see what I did differently from when it works and when the
> error appears.
>
> Any ideas?
>
> Dave Peterson Wrote:
> > One character off!
> >
> > .Add Type:=xlValidateList, Formula1:="=cat"
> >
> > (That extra equal sign will mean that you're not creating a list.)
> >
> >
> >
> > john_t_h wrote:
> > >
> > > I'm not sure what I'm doing wrong
> > >
> > > ActiveWorkbook.Names.Add Name:="cat", RefersTo:= _
> > > "=OFFSET(category!$A$1,0,0,COUNTA(category!$A:$A),1 )"
> > >
> > > 'Add data validation
> > > With ws.Cells(iRow, 4).Validation
> > > Delete
> > > Add Type:=xlValidateList, Formula1:="cat"
> > > IgnoreBlank = True
> > > End With
> > >
> > > When I insert the row the only option in the data validation box is
> > > "cat"
> > >
> > > Instead of picking up the range and assigning it to "cat" it just
> > > returns "cat"
> > >
> > > ???
> > >
> > > Rowan Wrote:
> > > > 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
> > >
> > > --
> > > john_t_h
> > >
> > ------------------------------------------------------------------------
> > > john_t_h's Profile:
> > http://www.excelforum.com/member.php...fo&userid=4826
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=461416
> >
> > --
> >
> > Dave Peterson
>
> --
> john_t_h
> ------------------------------------------------------------------------
> john_t_h's Profile: http://www.excelforum.com/member.php...fo&userid=4826
> View this thread: http://www.excelforum.com/showthread...hreadid=461416

--

Dave Peterson