CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2009
    Posts
    12

    Unhappy ComboBox in VBA Excel

    Hi, i am new to VBA Excel. I need help with the code for combobox.
    I have a list of data in excel sheet,with the data in the cells as

    A1: Name B1: Sex
    A2: Alice B2: F
    A3: Alan B3: M
    A4: Ben B4: M
    A5: Jane B5: F
    A6: Tony B6: M

    I insert a combobox to the Userform, with 3 selection, 'All', 'F' and 'M'.
    If i select 'F', i want to get

    A1: Name B1: Sex
    A2: Alice B2: F
    A3: Jane B3: F

    If i select 'M', i want to get
    A1: Name B1: Sex
    A2: Alan B2: M
    A3: Ben B3: M
    A4: Tony B4: M

    If i select 'All', all the data remains the same.

    How can i achieve that?

  2. #2
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: ComboBox in VBA Excel

    Either create two lists, or else apply a FILTER to the list, except that it looks like you've combined the fields anyways.

    First option looks better
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Mar 2009
    Posts
    12

    Question Re: ComboBox in VBA Excel

    Oh...what to u mean by creating 2 list? I need the code for the combobox...

  4. #4
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: ComboBox in VBA Excel

    An array list. You need to download the link in my signature of 101 Samples for VS2008. http://code.msdn.microsoft.com/vbsamples/

    Here's what I meant
    Code:
    Dim ListOfMale as List(of String)
    ListOfMale.AddItem "David M"
    EDIT: If you're using VBA not Visual Studio, then let us know...
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  5. #5
    Join Date
    Mar 2009
    Posts
    12

    Smile Re: ComboBox in VBA Excel

    I'm using VBA tks... =x

  6. #6
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: ComboBox in VBA Excel

    so, where is the data coming from? I don't have your worksheet
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  7. #7
    Join Date
    Mar 2009
    Posts
    12

    Smile Re: ComboBox in VBA Excel

    I've solved using the filter method.

    Private Sub UserForm_Initialize()
    ComboBox1.AddItem "All"
    ComboBox1.AddItem "F"
    ComboBox1.AddItem "M"

    Private Sub ComboBox1_Change()
    Select Case ComboBox1.Value
    Case "All"
    Range("A1").AutoFilter
    Case "F"
    Range("A1").AutoFilter Field:=2, Criteria1:="F"
    Case "M"
    Range("A1").AutoFilter Field:=2, Criteria1:="M"
    End Select
    End Sub

    Thanks. ^^

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured