+ Reply to Thread
Results 1 to 6 of 6

VBA Userform ComboBox: Content of ComboBox based off of Conditional Statement

  1. #1
    Registered User
    Join Date
    04-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    33

    VBA Userform ComboBox: Content of ComboBox based off of Conditional Statement

    I am looking to manipulate the content in the dropdown menu of a ComboBox. If x is 1, I want the content of the dropdown menu to be only "A". If x = 2, I want the content of the ComboBox to be "A" and "B". Etc. Any help????


    Or.... is there a smarter way of doing this???


    Private Sub UserForm_Initialize()
    Dim x As Integer
    x = Application.WorksheetFunction.CountIfs(Range("b3:b2002"), ComboBox2.Text)

    With ComboBox1
    If x >= 1 Then
    ComboBox1.List = Array("A")
    ElseIf x >= 2 Then
    ComboBox1.List = Array("A", "B")
    ElseIf x >= 3 Then
    ComboBox1.List = Array("A", "B", "C")
    End If
    .ListIndex = -1
    End With

    With ComboBox2
    .AddItem "1"
    .AddItem "2"
    .AddItem "3"
    End With

    End Sub
    Last edited by Brianandstewie; 05-16-2013 at 12:48 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: VBA Userform ComboBox: Content of ComboBox based off of Conditional Statement

    its in the wrong place, it needs to be on combox2's change and you're using the wrong formula, just COUNTIF will do, that's the multiple criteria one. Leave the population of ComboBox2 where it is though. When using with, you don't need to put the object you are saying WITH, so you would just put .List=xxxxxx

  3. #3
    Registered User
    Join Date
    04-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: VBA Userform ComboBox: Content of ComboBox based off of Conditional Statement

    I did as suggest (Thanks by the way); however, this does not solve my issue.

  4. #4
    Registered User
    Join Date
    04-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: VBA Userform ComboBox: Content of ComboBox based off of Conditional Statement

    Actually, nathansav, you were correct.

    The solution was a combination of what you adviced.

    However, there was a technicality that I noticed.


    Private Sub ComboBox2_Change()
    Dim x As Integer
    x = Application.WorksheetFunction.CountIf(Range("b3:b2002"), ComboBox2.Value)

    With ComboBox1
    If x = 1 Then
    .List = Array("A")
    ElseIf x = 2 Then
    .List = Array("A", "B")
    ElseIf x = 3 Then
    .List = Array("A", "B", "C")
    End If
    .ListIndex = -1
    End With

    End Sub

    Private Sub UserForm_Initialize()
    With ComboBox2
    .AddItem "1"
    .AddItem "2"
    .AddItem "3"
    End With
    End Sub
    Last edited by Brianandstewie; 05-16-2013 at 02:59 PM.

  5. #5
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: VBA Userform ComboBox: Content of ComboBox based off of Conditional Statement

    you're still using the wrong formula

  6. #6
    Registered User
    Join Date
    04-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: VBA Userform ComboBox: Content of ComboBox based off of Conditional Statement

    You're correct, it is the Countif rather than Countifs. Thank you.

+ 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