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.


--
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

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 DataValidation. 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.

Rowan Wrote:

This would work by creating a named range using:

=OFFSET(category!$A$1,0,0,COUNTA(category!$A:$A),1 ) < note mino
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 write
the row the worksheet, users can still go in and corrupt the data b
over-typing. The whole reason to use a UserForm was to maintai
consistaincy in the values to allow sorting and reporting.

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

This is a cutdown version of my form submit code (at home now and m
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

--
john_t_
-----------------------------------------------------------------------
john_t_h's Profile: http://www.excelforum.com/member.php...nfo&userid=482
View this thread: http://www.excelforum.com/showthread.php?threadid=46141

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


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 stil
use OFFSET(category!$A$1,0,0,COUNTA(category!$A:$A),1) to allow th
list to be dynamic?

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

Thanks

Rowan Wrote:
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 offse
formula
used for the combobox. You could either do this manually (it onl
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 for
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 origina
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?



--
john_t_
-----------------------------------------------------------------------
john_t_h's Profile: http://www.excelforum.com/member.php...nfo&userid=482
View this thread: http://www.excelforum.com/showthread.php?threadid=46141

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

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.


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

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.


--
T-®ex
------------------------------------------------------------------------
T-®ex's Profile: http://www.excelforum.com/member.php...o&userid=26572
View this thread: http://www.excelforum.com/showthread...hreadid=461416

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


Code:
--------------------
' 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

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


--
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