Dynamic list in combo box for user forms

[Riptake]

Hi guys, I have created a userform where I have a combo box and an options box. I want the combo box to reference a specific list of values. And if the check box is selected, I want the same combo box to reference a different list of values. This is the present syntax I am using. For some reason, The value of the check box does not alter the list in my macro. Any ideas??

Private Sub Userform_Initialize()

If chkHeader.Value = True Then

cmbSort1.Clear

With cmbSort1
.AddItem Range("A1")
.AddItem Range("B1")
.AddItem Range("C1")

End With

ElseIf chkHeader.Value = False Then
With cmbSort1
.AddItem Range("A2")
.AddItem Range("B2")
.AddItem Range("C2")
End With

End If

End Sub

 

[Jerry Sullivan]

Hi Riptake,

The problem was with your use of If...Then...ElseIf...End.
Since there are only two conditions, you just use If...Then...Else...End

The code can be rewritten as shown below.
The example shows the option to use of .List instead of .AddItem if you have more than one item.

Private Sub Userform_Initialize()
    If chkHeader Then
        With cmbSort1
            .AddItem Range("A1")
            .AddItem Range("B1")
            .AddItem Range("C1")
        End With
    Else
        cmbSort1.List = _
            Application.Transpose(Range("A2:C2").Value)
    End If
End Sub

 

[Riptake]

Thank You Jerry for the reply. That should do the trick.

I also needed to know if there is a syntax that would allow me to reference column names in the combo box drop down list instead of the the Specific cell I mentioned. For example, in the first part of the macro, I stated, if option box = True, then With cmbBox.Value .AddItem Range("a2") .Additem Range ("B2") etc.... What I really wanted to list was the column name i.e. "Column A", "Column B" etc....

Thanks for all the help....

 

[Jerry Sullivan]

Thank You Jerry for the reply. That should do the trick.

I also needed to know if there is a syntax that would allow me to reference column names in the combo box drop down list instead of the the Specific cell I mentioned. For example, in the first part of the macro, I stated, if option box = True, then With cmbBox.Value .AddItem Range("a2") .Additem Range ("B2") etc.... What I really wanted to list was the column name i.e. "Column A", "Column B" etc....

Thanks for all the help....

Are you saying that instead of A1:C1 you really want your combobox list to be A1: Last Item in Column A?

 

[Riptake]

Well, what I meant to ask was a syntax that would allow me to list the name of the columns i.e. Column A, Column B, Column C. as choices in the combo box.

To give you a little background on the project, what I am trying to do is replicate the Sort function of Excel 2003, which allows the user to sort according to the Columns or Headers, which ever, based on the selection of a Check Box "My data has headers".. Thus the first part of my syntax:

.AddItem Range("A2")
.AddItem Range("B2")
.AddItem Range("C2")

references the specific headers in the data I am using. On the other hand, If the Check Box remains unselected, then I want the drop down list in the combo box to show only Column A, Column B, Column C as choices i.e. data has no headers.

I'm sorry for being so ambiguous. I'm a beginner with VB.

Thanks.

 

[Jerry Sullivan]

Okay- that helps me understand what you're doing.

What would be the basis for which Columns are in the sort range
(or would you prefer just getting this to work with Column A, B, C for now)?

 

[Riptake]

For now I only have data in 3 columns (A, B and C), so that should be fine. Thanks.

 

[Jerry Sullivan]

For now I only have data in 3 columns (A, B and C), so that should be fine. Thanks.

You can try the code below. It uses variables for the Columns and Rows to help get you started in a good direction for your next step.

Private Sub Userform_Initialize()
    Dim rList As Range
    Dim sColLtr As String
    Dim lColFirst As Long, lColLast As Long
    Dim lRow As Long, i As Long
    
    lColFirst = 1
    lColLast = 3
    
    If chkHeader Then 'has headers: A1 Value
        lRow = 1
        Set rList = Range(Cells(lRow, lColFirst), _
            Cells(lRow, lColLast))
        With cmbSort1
            If rList.Count = 1 Then
                .AddItem rList
            Else
                .List = Application.Transpose(rList.Value)
            End If
        End With
    
    Else 'no headers: Column A
        For i = lColFirst To lColLast
            sColLtr = Split(Cells(1, i).Address, "$")(1)
            cmbSort1.AddItem "Column " & sColLtr
        Next i
    End If
End Sub

 

[Riptake]

Jerry, as I read through your syntax, I realize how much more I have to learn about VBA Anyway, thank you so very much for all your guidance. I'll play around with the macro and let you know of the outcome.

Best,

-Nihad

 

[Jerry Sullivan]

You're welcome, Nihad.

I don't mean to overwhelm you with the syntax - the task you describe can be done much more simply for Range A1:C2 only.

But quickly you'll be wanting to do Range D8:Z100, and that simple code wouldn't extend very easily.

Just ask if you have any questions on the syntax, or need further help on your later steps.