+ Reply to Thread
Results 1 to 12 of 12

dynamic combobox

Hybrid View

  1. #1
    Registered User
    Join Date
    01-12-2004
    Posts
    9

    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.

  2. #2
    Registered User
    Join Date
    08-24-2005
    Location
    Philippines
    Posts
    75

    Smile

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

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

  3. #3
    Registered User
    Join Date
    01-12-2004
    Posts
    9
    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

  4. #4
    Rowan
    Guest

    Re: dynamic combobox

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


  5. #5
    Registered User
    Join Date
    01-12-2004
    Posts
    9
    Quote 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?

  6. #6
    Rowan
    Guest

    Re: dynamic combobox

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


  7. #7
    Registered User
    Join Date
    01-12-2004
    Posts
    9
    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

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

  8. #8
    Rowan
    Guest

    Re: dynamic combobox

    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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1