Dynamic list in combo box for user forms

R

Riptake

New Member
Joined
Jan 10, 2012
Messages
46
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
 
Sort by date Sort by votes
Hi Jerry. So I copied, modified and pasted the above syntax. It pulls in the Column Name perfectly. Thanks for that!!. But I have another issue, which has been prevalent from the beginning. For some reason, the drop-down list on the combo box does not update as I check/uncheck the "data has headers" Checkbox.

This is what I used:

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 CheckBox1 = True Then 'has headers: A1 Value
lRow = 1
Set rList = Range(Cells(lRow, lColFirst), _
Cells(lRow, lColLast))
With ComboBox1
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)
ComboBox1.AddItem "Column " & sColLtr
Next i
End If
End Sub

As of now, the drop-down list shows only column names no matter whether the check box is True or False. When I initially started this project, I was facing the same issue, except the list of items in the com box was different i.e. specified text.

Any ideas/modifications?


Thanks aplenty.
 
Upvote 0
Riptake said:
Hi Jerry. So I copied, modified and pasted the above syntax. It pulls in the Column Name perfectly. Thanks for that!!. But I have another issue, which has been prevalent from the beginning. For some reason, the drop-down list on the combo box does not update as I check/uncheck the "data has headers" Checkbox.
Click to expand...

Nihad,

Your code is in the Userform Initialize Procedure, so it's not being triggered by changes to the Checkbox.

In addition to copying the code there, you'll need to add a statement like you previously had to clear any existing List Items. I deleted that line because it was unnecessary for Userform Initialize.
 
Upvote 0
Jerry. That was soo simple!! I feel like a total moron for not thinking about it earlier lol.... Thank you so much for all your help Sir. Really appreciate it!!!
 
Upvote 0
Hi Jerry, sorry to be bothering you with all the queries, but the task seemed to much simpler when I initially started it. I require two further hints/answers regarding the syntax on column selection.

1) You had previously mentioned that the above syntax will work well with the data within the specified Range (A2:C2) etc. If I were to use the macro for ANY selection of columns, whether they are between Columns A2 - C2 or even Columns X2 - X11 , and list the same properties, how would I code the cell references within this macro?

2) When I wrote the syntax for the OK-Command Button, I basically hard-coded what text to look for within the Combo Boxes, and generate an output. How would I write a syntax for the Ok-Command Button to look for a specific header as the header may change based on my selection. (The same is true, if there are no headers available. Right now, I simply listed "Column A", "Column B" etc.)

Thanks for all your help.
 
Upvote 0
Riptake said:
Hi Jerry, sorry to be bothering you with all the queries, but the task seemed to much simpler when I initially started it. I require two further hints/answers regarding the syntax on column selection.

1) You had previously mentioned that the above syntax will work well with the data within the specified Range (A2:C2) etc. If I were to use the macro for ANY selection of columns, whether they are between Columns A2 - C2 or even Columns X2 - X11 , and list the same properties, how would I code the cell references within this macro?

2) When I wrote the syntax for the OK-Command Button, I basically hard-coded what text to look for within the Combo Boxes, and generate an output. How would I write a syntax for the Ok-Command Button to look for a specific header as the header may change based on my selection. (The same is true, if there are no headers available. Right now, I simply listed "Column A", "Column B" etc.)

Thanks for all your help.
Click to expand...

I won't be able to help with this until tonight- but if you wanted to get started on this, what you'll want to do is get these values based on the Selected Range:
lRow = Selection.Row
lColFirst = Selection.Column
lColLast = Selection.Column + Selection.Columns-1

I'll follow up with you later unless another helper is available to continue.
 
Upvote 0
Riptake said:
1) You had previously mentioned that the above syntax will work well with the data within the specified Range (A2:C2) etc. If I were to use the macro for ANY selection of columns, whether they are between Columns A2 - C2 or even Columns X2 - X11 , and list the same properties, how would I code the cell references within this macro?
Click to expand...


You could replace the hard-coded assignments of the variables with the code in blue font to define your Left, Right, and Top boundaries of your Sort Range.
Rich (BB code): 
    Dim lColFirst As Long, lColLast As Long
    Dim lRow As Long, i As Long
    
    With Selection
        lRow = .Row
        lColFirst = .Column
        lColLast = .Column + .Columns.Count - 1
    End With


Riptake said:
2) When I wrote the syntax for the OK-Command Button, I basically hard-coded what text to look for within the Combo Boxes, and generate an output. How would I write a syntax for the Ok-Command Button to look for a specific header as the header may change based on my selection. (The same is true, if there are no headers available. Right now, I simply listed "Column A", "Column B" etc.)
Click to expand...

I'd suggest your OK button code read the ListIndex Property of the Combobox, the Value of the Checkbox and the Selected Range in the ActiveSheet.
With those 3 pieces of data, your OK button procedure should have all the information it needs to do the sort.
 
Upvote 0
Hi Jerry, Sorry for the late reply. I kinda figured a way to add in the cell referencing. Here is the complete macro if any one needs it for a reference. Thanks for all your help Jerry. Cheers.

Private Sub CheckBox1_Click()

Dim rList As Range
Dim sColLtr As String
Dim lColFirst As Long, lColLast As Long
Dim lRow As Long, i As Long
Dim RangeStart As Variant
Dim ColStart As Integer
Dim ColCount As Integer
Dim RowCount As Integer
Dim RowStart As Integer

ColCount = Selection.Columns.Count
RangeStart = Selection.Address(ReferenceStyle:=xlR1C1)
If ColCount > 1 Then
ColStart = Mid(RangeStart, InStr(1, RangeStart, "C") + 1, InStr(1, RangeStart, ":") - InStr(1, RangeStart, "C") - 1)
Else
ColStart = Mid(RangeStart, InStr(1, RangeStart, "C") + 1, Len(RangeStart) - (InStr(1, RangeStart, "C")))
End If

RowCount = Selection.Rows.Count

RowStart = Mid(RangeStart, InStr(1, RangeStart, "R") + 1, InStr(1, RangeStart, "C") - InStr(1, RangeStart, "R") - 1)

lColFirst = ColStart
lColLast = ColStart + ColCount - 1

'Sort List 1

If CheckBox1 = True Then 'has headers: Header Names

ComboBox1.Clear

lRow = RowStart
Set rList = Range(Cells(lRow, lColFirst), _
Cells(lRow, lColLast))
With ComboBox1
If rList.Count = 1 Then
.AddItem rList
Else
.List = Application.Transpose(rList.Value)
End If
End With

Else 'no headers: Column Names

ComboBox1.Clear

For i = lColFirst To lColLast
sColLtr = Split(Cells(1, i).Address, "$")(1)
ComboBox1.AddItem "Column " & sColLtr
Next i
End If

'Sort List 2

If CheckBox1 = True Then 'has headers: Header Names

ComboBox2.Clear

lRow = RowStart
Set rList = Range(Cells(lRow, lColFirst), _
Cells(lRow, lColLast))
With ComboBox2
If rList.Count = 1 Then
.AddItem rList
Else
.List = Application.Transpose(rList.Value)
End If
End With

Else 'no headers: Column Names

ComboBox2.Clear

For i = lColFirst To lColLast
sColLtr = Split(Cells(1, i).Address, "$")(1)
ComboBox2.AddItem "Column " & sColLtr
Next i
End If

'Sort List 3

If CheckBox1 = True Then 'has headers: Header Names

ComboBox3.Clear

lRow = RowStart
Set rList = Range(Cells(lRow, lColFirst), _
Cells(lRow, lColLast))
With ComboBox3
If rList.Count = 1 Then
.AddItem rList
Else
.List = Application.Transpose(rList.Value)
End If
End With

Else 'no headers: Column Names

ComboBox3.Clear

For i = lColFirst To lColLast
sColLtr = Split(Cells(1, i).Address, "$")(1)
ComboBox3.AddItem "Column " & sColLtr
Next i
End If

End Sub

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
Dim RangeStart As Variant
Dim ColStart As Integer
Dim ColCount As Integer
Dim RowCount As Integer
Dim RowStart As Integer

ColCount = Selection.Columns.Count
RangeStart = Selection.Address(ReferenceStyle:=xlR1C1)
If ColCount > 1 Then
ColStart = Mid(RangeStart, InStr(1, RangeStart, "C") + 1, InStr(1, RangeStart, ":") - InStr(1, RangeStart, "C") - 1)
Else
ColStart = Mid(RangeStart, InStr(1, RangeStart, "C") + 1, Len(RangeStart) - (InStr(1, RangeStart, "C")))
End If

RowCount = Selection.Rows.Count

RowStart = Mid(RangeStart, InStr(1, RangeStart, "R") + 1, InStr(1, RangeStart, "C") - InStr(1, RangeStart, "R") - 1)

lColFirst = ColStart
lColLast = ColStart + ColCount - 1

'Sort List 1

If CheckBox1 = True Then 'has headers: Header Names
lRow = RowStart
Set rList = Range(Cells(lRow, lColFirst), _
Cells(lRow, lColLast))
With ComboBox1
If rList.Count = 1 Then
.AddItem rList
Else
.List = Application.Transpose(rList.Value)
End If
End With

Else 'no headers: Column Names
For i = lColFirst To lColLast
sColLtr = Split(Cells(1, i).Address, "$")(1)
ComboBox1.AddItem "Column " & sColLtr
Next i
End If

'Sort List 2

If CheckBox1 = True Then 'has headers: Header Names
lRow = RowStart
Set rList = Range(Cells(lRow, lColFirst), _
Cells(lRow, lColLast))
With ComboBox2
If rList.Count = 1 Then
.AddItem rList
Else
.List = Application.Transpose(rList.Value)
End If
End With

Else 'no headers: Column Names
For i = lColFirst To lColLast
sColLtr = Split(Cells(1, i).Address, "$")(1)
ComboBox2.AddItem "Column " & sColLtr
Next i
End If

'Sort List 3

If CheckBox1 = True Then 'has headers: Header Names
lRow = RowStart
Set rList = Range(Cells(lRow, lColFirst), _
Cells(lRow, lColLast))
With ComboBox3
If rList.Count = 1 Then
.AddItem rList
Else
.List = Application.Transpose(rList.Value)
End If
End With

Else 'no headers: Column Names
For i = lColFirst To lColLast
sColLtr = Split(Cells(1, i).Address, "$")(1)
ComboBox3.AddItem "Column " & sColLtr
Next i
End If

End Sub
Private Sub CommandButton1_Click()

'Sort List 1

Dim order1op As XlSortOrder
Dim headerlop As XlYesNoGuess

Dim RangeStart As Variant
Dim ColStart As Integer
Dim ColCount As Integer
Dim RowCount As Integer
Dim RowStart As Integer
Dim firstcolumn As Integer
Dim lastcolumn As Integer

ColCount = Selection.Columns.Count
RangeStart = Selection.Address(ReferenceStyle:=xlR1C1)
If ColCount > 1 Then
ColStart = Mid(RangeStart, InStr(1, RangeStart, "C") + 1, InStr(1, RangeStart, ":") - InStr(1, RangeStart, "C") - 1)
Else
ColStart = Mid(RangeStart, InStr(1, RangeStart, "C") + 1, Len(RangeStart) - (InStr(1, RangeStart, "C")))
End If

RowCount = Selection.Rows.Count

RowStart = Mid(RangeStart, InStr(1, RangeStart, "R") + 1, InStr(1, RangeStart, "C") - InStr(1, RangeStart, "R") - 1)

firstcolumn = ColStart
lastcolumn = ColStart + ColCount - 1


If OptionButton1 Then
order1op = xlAscending
Else
order1op = xlDescending
End If

If OptionButton3 Then
order2op = xlAscending
Else
order2op = xlDescending
End If

If OptionButton5 Then
order3op = xlAscending
Else
order3op = xlDescending
End If

If CheckBox1 Then
For i = 0 To (lastcolumn - 1)
If Cells(RowStart, firstcolumn + i) = ComboBox1 Then
aCell = Cells(RowStart, firstcolumn + i).Address
Exit For
End If

Next i
headerlop = xlYes
Else
aCell = Right(ComboBox1, Len(ComboBox1) - InStr(1, ComboBox1, " "))
aCell = aCell & RowStart
headerlop = xlNo
End If

If CheckBox1 Then
For i = 0 To (lastcolumn - 1)
If Cells(RowStart, firstcolumn + i) = ComboBox2 Then
bCell = Cells(RowStart, firstcolumn + i).Address
Exit For
End If

Next i
headerlop = xlYes
Else
bCell = Right(ComboBox2, Len(ComboBox2) - InStr(1, ComboBox2, " "))
bCell = bCell & RowStart
headerlop = xlNo
End If

If CheckBox1 Then
For i = 0 To (lastcolumn - 1)
If Cells(RowStart, firstcolumn + i) = ComboBox3 Then
cCell = Cells(RowStart, firstcolumn + i).Address
Exit For
End If

Next i
headerlop = xlYes
Else
cCell = Right(ComboBox3, Len(ComboBox3) - InStr(1, ComboBox3, " "))
cCell = cCell & RowStart
headerlop = xlNo
End If

Set oneRange = Selection

If ComboBox1 <> "" And ComboBox2 <> "" And ComboBox3 <> "" And (OptionButton1 Or OptionButton2) _
And (OptionButton3 Or OptionButton4) And (OptionButton5 Or OptionButton6) Then

oneRange.Sort Key1:=Range(aCell), Key2:=Range(bCell), Key3:=Range(cCell), Order1:=order1op, Order2:=order2op, _
Order3:=order3op, Header:=headerlop

ElseIf ComboBox1 <> "" And ComboBox2 <> "" And ComboBox3 = "" And (OptionButton1 Or OptionButton2) _
And (OptionButton3 Or OptionButton4) Then

oneRange.Sort Key1:=Range(aCell), Key2:=Range(bCell), Order1:=order1op, Order2:=order2op, Header:=headerlop

ElseIf ComboBox1 <> "" And ComboBox2 = "" And ComboBox3 = "" And (OptionButton1 Or OptionButton2) Then

oneRange.Sort Key1:=Range(aCell), Order1:=order1op, Header:=headerlop

End If

UserForm1.Hide

End Sub
Private Sub CommandButton2_Click()

Unload Me

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Combo Box range where user adds items to a worksheet and they are added to combo box
Replies
3
Views
272
Micron
M
I
  • Solved
Remove value from drop down list then enters wrong value in worksheet cell
Replies
2
Views
103
ipbr21054
I
K
  • Solved
VBA in User Form. Xlookup formula for Dependent Combo Box.
Replies
8
Views
547
Kscoof
K
A
  • Solved
Form Control Manipulation From A Module
Replies
5
Views
152
Ark68
A
M
  • Question Question
Userform help: Transfer selection from drop down list to worksheet range
Replies
3
Views
97
HighAndWilder
H

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top