none
combobox columns RRS feed

  • Question

  • I've been looking all over for help with a problem and I can't seem to find an example of what I'm trying to do.

    I have a userform with a combobox:

    Private Sub UserForm_Initialize()

      cbRtlShelfLifeCode.ColumnCount = 1

      'Load data into ComboBox

      cbRtlShelfLifeCode.List() = Array("", "CR1", "CR2", "CR3", _
      "FR1", "FR2", "FR3", "FR4", "FR5", "FR6", "FR7", "FR8", _
      "PO1", "PO2", "PO3", _
      "PR1", "PR2", "PR3", "PR4", "PR5", "PR6", _
      "SP1", "SP2", "SP3")

    End Sub

    I need to add columns to the above values. For example,

    Code | Optimal | Warning | Critical | DNS
    CR1  | 0           | 35         | 41        | 51
    CR2  | 0           | 70         | 80        | 90

    Upon selecting "CR1" I need the other columns to populate bookmarked fields on the form.

    1. How do I add these columns to the combobox?
    2. How do I get the columns to populate the bookmarked fields?

    Any help is appreciated! Is there a site somewhere that deals with this?

    Thanks!

    Friday, March 11, 2011 3:30 PM

All replies

  • The .List attribute of the combobox object has row and column properties

    cbRtlShelfLifeCode.List([rownum], [colnum])

    with the rownum of the first item being 0 and the colnum of the first column also being 0

    As the first item in the array with which you are populating the listbox is blank, to add the values for CR1, you would use

    With cbRtlShelfLifeCode
       .List(1,1)= 0
       .List(1,2)=35
       .list(1,3)=41
       .list(1,4)=51
    etc

    But it would be a lot easier to set up all the data in an Excel spreadsheet, starting in column A1 and then set the .List of the combobox to the .Range("A1").CurrentRegion.Value

    See http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

    To get the values of the individual columns for the selection item, you would use

    cbRtlShelfLifeCode.List(cbRtlShelfLifeCode.ListIndex, colnum)


    Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "JohnLute" wrote in message news:459fa7d3-52ea-4dc8-99ca-84acd9203d59@communitybridge.codeplex.com...

    I've been looking all over for help with a problem and I can't seem to find an example of what I'm trying to do.

    I have a userform with a combobox:

    Private Sub UserForm_Initialize()

      cbRtlShelfLifeCode.ColumnCount = 1

      'Load data into ComboBox

      cbRtlShelfLifeCode.List() = Array("", "CR1", "CR2", "CR3", _
      "FR1", "FR2", "FR3", "FR4", "FR5", "FR6", "FR7", "FR8", _
      "PO1", "PO2", "PO3", _
      "PR1", "PR2", "PR3", "PR4", "PR5", "PR6", _
      "SP1", "SP2", "SP3")

    End Sub

    I need to add columns to the above values. For example,

    Code | Optimal | Warning | Critical | DNS
    CR1  | 0           | 35         | 41        | 51
    CR2  | 0           | 70         | 80        | 90

    Upon selecting "CR1" I need the other columns to populate bookmarked fields on the form.

    1. How do I add these columns to the combobox?
    2. How do I get the columns to populate the bookmarked fields?

    Any help is appreciated! Is there a site somewhere that deals with this?

    Thanks!


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Saturday, March 12, 2011 10:40 AM
  • Hi Doug! Thanks fro the reply. Greg Maxey has a great site and I've often visited there. I agree - the Excel trick would be easier but I need to keep this built into the Word doc.

    I'm a little lost about where to plug in the with statement. I tried the following and compacted - no problems. But when I fired the code from the combobox the debugger popped on the line with the ">" preceding it. Run-time error '380': Could not set the property. Invalid property value.

    Private Sub UserForm_Initialize()

      cbRtlShelfLifeCode.ColumnCount = 2

      'Load data into ComboBox

      cbRtlShelfLifeCode.List() = Array("", "CR1", "CR2", "CR3", _
      "FR1", "FR2", "FR3", "FR4", "FR5", "FR6", "FR7", "FR8", _
      "PO1", "PO2", "PO3", _
      "PR1", "PR2", "PR3", "PR4", "PR5", "PR6", _
      "SP1", "SP2", "SP3")
     
     
    With cbRtlShelfLifeCode
    >  .List(1, 1) = 0
       .List(1, 2) = 35
       .List(1, 3) = 41
       .List(1, 4) = 51
    End With

    End Sub

    Any clarification you might have would be greatly appreciated!

    Thursday, March 24, 2011 3:17 PM