Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

VBA: Combobox list depends upon selection of optionbutton

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA: Combobox list depends upon selection of optionbutton

    On Sheet1 I have 3 Option buttons called Optionbutton 1-3. Each optionbutton is representing a header on sheet2.

    Below the header on sheet 2 is the database.

    As the optionbutton 1-3 are linked, meaning just one at time can be ticked.

    When selecting an optionbutton, the list below the header, that the optionbutton represent, fills in the combobox.

    Sheet2:
    A B C
    Header1 Header2 Header3
    1
    2
    3
    :


    Following is typed in Name Define box:

    Optionbutton1=Offset(Sheet2!$A$2;0;0;Counta(Sheet2!$A:$A)-1;1)

    Optionbutton2=Offset(Sheet2!$B$2;0;0;Counta(Sheet2!$B:$B)-1;1)

    Optionbutton3=Offset(Sheet2!$C$2;0;0;Counta(Sheet2!$C:$C)-1;1)


    In VBA:

    Private Sub Optionbutton1_Click()
    ' Declare Sel as the optionbutton
    ' variable
    ' Sel = 1 (Header1)
    ' Sel = 2 (Header2)
    ' Sel = 3 (Header3)

    Dim Sel As Integer

    Sel = 1
    'Put value 1 in cell A1 on sheet1
    Range("$A$1").Value = Sel

    End Sub

    Private Sub Optionbutton2_Click()

    Dim Sel As Integer

    Sel = 2
    'Put value 2 in cell A1 on sheet1
    Range("$A$1").Value = Sel

    End Sub

    Private Sub Optionbutton3_Click()

    Dim Sel As Integer

    Sel = 3
    'Put value 3 in cell A1 on sheet1
    Range("$A$1").Value = Sel

    End Sub

    In cell D2 on sheet1 following formula is
    to get the address intervall depending upon the selection of optionbuttons:
    =ADDRESS(2;A1)&":"&ADDRESS(112;A1)


    Q1: How can I fill the combobox with the list depending upon what optionbutton is selected, VBA code or code for ListFillRange?

    Q2: How can I sort the list before it is filled in the combobox, vba-code?

    Q3: When selecting from the list in the combobox the row filtered data should be copied from sheet2 to sheet1, vba-code?

    Thanks in advance

  • #2
    This is one way via code. NOTE: I am using the Control Toolbox objects not forms toolbox.

    Option Explicit
    Dim FillRange As Range
    Dim c As Range


    Private Sub optOne_Click()
    Set FillRange = Range(Range("D1"), Range("D1").End(xlDown))
    FillRange.Sort Key1:=Range("D1"), Order1:=xlAscending
    cboList.Clear
    For Each c In FillRange
    cboList.AddItem c
    Next
    End Sub

    Private Sub optThree_Click()
    Set FillRange = Range(Range("F1"), Range("F1").End(xlDown))
    FillRange.Sort Key1:=Range("F1"), Order1:=xlAscending
    cboList.Clear
    For Each c In FillRange
    cboList.AddItem c
    Next
    End Sub

    Private Sub optTwo_Click()
    Set FillRange = Range(Range("E1"), Range("E1").End(xlDown))
    FillRange.Sort Key1:=Range("E1"), Order1:=xlAscending
    cboList.Clear
    For Each c In FillRange
    cboList.AddItem c
    Next
    End Sub

    Not sure about your third question. Are you wanting just the selected item from the combobox to be copied to the second sheet or the entire row of the selected item?

    Comment


    • #3
      Many thank's for the answer. I did edit the problem text to be a little more precise what I mean.

      Thanks again, will check it out immediately! =)

      Comment


      • #4
        Answer to question:
        Yes, I want the entire row to be copied from sheet 2 to sheet1.

        Comment


        • #5
          This is what I did to get the data into the combobox:
          ----------------------------------------------------
          Sheet1:
          Optionbutton1 => Select Header1
          Optionbutton2 => Select Header2
          Optionbutton3 => Select Header3

          Combobox1 => Show columnlist (that is below Header1 or Header 2 or Header3) depending of optionbuttons selection.
          -----------------------------------------------------

          Sheet2:
          A B C
          1 Header1 Header2 Header3
          2
          3
          :
          -------------------------------------------------

          Following is typed in Name Define box:

          Optionbutton_1=Offset(Sheet2!$A$2;0;0;Counta(Sheet2!$A:$A)-1;1)

          Optionbutton_2=Offset(Sheet2!$B$2;0;0;Counta(Sheet2!$B:$B)-1;1)

          Optionbutton_3=Offset(Sheet2!$C$2;0;0;Counta(Sheet2!$C:$C)-1;1)


          In VBA:

          Private Sub Optionbutton1_Click() ComboBox1.ListFillRange="Optionbutton_1"
          ComboBox1.ListIndex=0
          End Sub

          Private Sub Optionbutton2_Click() ComboBox1.ListFillRange="Optionbutton_2"
          ComboBox1.ListIndex=0
          End Sub

          Private Sub Optionbutton3_Click() ComboBox1.ListFillRange="Optionbutton_3"
          ComboBox1.ListIndex=0
          End Sub

          I will now go a head with checking how to remove duplicate and sort in the combobox, and parse the address of the sorted unique items. And then when selection is made in the combobox the filtred rows are copied to sheet1.

          If anybody has an idea how to do that please let me know.

          Comment

          Trending

          Collapse

          There are no results that meet this criteria.

          Working...
          X