Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: VBA: Combobox list depends upon selection of optionbutton

  1. #1
    Join Date
    4th December 2003
    Posts
    4
    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    1st February 2003
    Location
    Memphis, TN
    Posts
    891
    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?

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    4th December 2003
    Posts
    4
    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! =)

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    4th December 2003
    Posts
    4
    Answer to question:
    Yes, I want the entire row to be copied from sheet 2 to sheet1.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    4th December 2003
    Posts
    4
    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Combobox Visibility After Drop List Selection
    By hippy in forum EXCEL HELP
    Replies: 1
    Last Post: June 21st, 2008, 11:30
  2. Replies: 10
    Last Post: February 21st, 2008, 09:49
  3. Replies: 3
    Last Post: February 5th, 2008, 15:42
  4. Replies: 11
    Last Post: August 18th, 2006, 23:11
  5. Replies: 3
    Last Post: March 8th, 2006, 17:17

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