Display data in listbox depending on the criteria chosen in the two combo boxes.

D

dhfernz

New Member
Joined
Mar 31, 2011
Messages
15
Hello,

I am fairly new to VBA and i need help with a script to do the following. I have this following data;

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 63"]
<colgroup><col width="63"></colgroup><tbody>[TR]
[TD="class: xl65, width: 63"]Date
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 50"]
<colgroup><col width="50"></colgroup><tbody>[TR]
[TD="class: xl65, width: 50"]Channel[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 70"]
<colgroup><col width="70"></colgroup><tbody>[TR]
[TD="class: xl65, width: 70"]Event Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 58"]
<colgroup><col width="58"></colgroup><tbody>[TR]
[TD="class: xl65, width: 58"]Job Type[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 88"]
<colgroup><col width="88"></colgroup><tbody>[TR]
[TD="class: xl65, width: 88"]Description[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 63"]
<colgroup><col width="63"></colgroup><tbody>[TR]
[TD="class: xl65, width: 63"]Initiated by[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 52"]
<colgroup><col width="52"></colgroup><tbody>[TR]
[TD="class: xl65, width: 52"]Resource[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 63"]
<colgroup><col width="63"></colgroup><tbody>[TR]
[TD="class: xl65, width: 63"]28-Jun-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 50"]
<colgroup><col width="50"></colgroup><tbody>[TR]
[TD="class: xl65, width: 50"]Gol[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Bhai
[/TD]
[TD]Press
[/TD]
[TD]Tune in
[/TD]
[TD]AC
[/TD]
[TD]VP
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 63"]
<colgroup><col width="63"></colgroup><tbody>[TR]
[TD="class: xl65, width: 63"]28-Jun-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 50"]
<colgroup><col width="50"></colgroup><tbody>[TR]
[TD="class: xl65, width: 50"]Gol[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Bhai
[/TD]
[TD]Press
[/TD]
[TD]Tune in
[/TD]
[TD]AC
[/TD]
[TD]SW
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 63"]
<colgroup><col width="63"></colgroup><tbody>[TR]
[TD="class: xl65, width: 63"]28-Jun-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 50"]
<colgroup><col width="50"></colgroup><tbody>[TR]
[TD="class: xl65, width: 50"]Wor[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Master
[/TD]
[TD]Back
[/TD]
[TD]Event Brand
[/TD]
[TD]NJ
[/TD]
[TD]SS
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 63"]
<colgroup><col width="63"></colgroup><tbody>[TR]
[TD="class: xl65, width: 63"]28-Jun-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 50"]
<colgroup><col width="50"></colgroup><tbody>[TR]
[TD="class: xl65, width: 50"]Mov[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Tomorrow
[/TD]
[TD]Other
[/TD]
[TD]Tune in
[/TD]
[TD]SG
[/TD]
[TD]AK
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 63"]
<colgroup><col width="63"></colgroup><tbody>[TR]
[TD="class: xl65, width: 63"]01-Jul-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 50"]
<colgroup><col width="50"></colgroup><tbody>[TR]
[TD="class: xl65, width: 50"]FX[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Cat
[/TD]
[TD]Col Cat
[/TD]
[TD]Coffee
[/TD]
[TD]RW
[/TD]
[TD]HA
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 63"]
<colgroup><col width="63"></colgroup><tbody>[TR]
[TD="class: xl65, width: 63"]01-Jul-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 50"]
<colgroup><col width="50"></colgroup><tbody>[TR]
[TD="class: xl65, width: 50"]Hot[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Hoard
[/TD]
[TD]Hoard
[/TD]
[TD]Launch in Kol
[/TD]
[TD]AP
[/TD]
[TD]AK
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 63"]
<colgroup><col width="63"></colgroup><tbody>[TR]
[TD="class: xl65, width: 63"]01-Jul-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 50"]
<colgroup><col width="50"></colgroup><tbody>[TR]
[TD="class: xl65, width: 50"]Plu
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Barn
[/TD]
[TD]Other
[/TD]
[TD]Umb Brand
[/TD]
[TD]AK
[/TD]
[TD]AJ
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 63"]
<colgroup><col width="63"></colgroup><tbody>[TR]
[TD="class: xl65, width: 63"]01-Jul-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 50"]
<colgroup><col width="50"></colgroup><tbody>[TR]
[TD="class: xl65, width: 50"]Ok[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Mad
[/TD]
[TD]Post
[/TD]
[TD]Coffee Day
[/TD]
[TD]RW
[/TD]
[TD]RKV
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 63"]
<colgroup><col width="63"></colgroup><tbody>[TR]
[TD="class: xl65, width: 63"]01-Jul-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 50"]
<colgroup><col width="50"></colgroup><tbody>[TR]
[TD="class: xl65, width: 50"]Ok[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Bahu
[/TD]
[TD]Press
[/TD]
[TD]Tune in
[/TD]
[TD]RW
[/TD]
[TD]DS
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 63"]
<colgroup><col width="63"></colgroup><tbody>[TR]
[TD="class: xl65, width: 63"]10-Jul-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 50"]
<colgroup><col width="50"></colgroup><tbody>[TR]
[TD="class: xl65, width: 50"]Gol[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Shol
[/TD]
[TD]Brand
[/TD]
[TD]Stand
[/TD]
[TD]SG
[/TD]
[TD]AK
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 63"]
<colgroup><col width="63"></colgroup><tbody>[TR]
[TD="class: xl65, width: 63"]10-Jul-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 50"]
<colgroup><col width="50"></colgroup><tbody>[TR]
[TD="class: xl65, width: 50"]Gol[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Shol
[/TD]
[TD]Brand
[/TD]
[TD]Tune in
[/TD]
[TD]SG
[/TD]
[TD]AK
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 63"]
<colgroup><col width="63"></colgroup><tbody>[TR]
[TD="class: xl65, width: 63"]10-Jul-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Plu
[/TD]
[TD]Naam
[/TD]
[TD]Hoard
[/TD]
[TD]Launch
[/TD]
[TD]AC
[/TD]
[TD]HA
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 63"]
<colgroup><col width="63"></colgroup><tbody>[TR]
[TD="class: xl65, width: 63"]10-Jul-13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Plu
[/TD]
[TD]Nand
[/TD]
[TD]Hoard
[/TD]
[TD]Launch
[/TD]
[TD]AC
[/TD]
[TD]SS
[/TD]
[/TR]
</tbody>[/TABLE]

What i need is the following
1) Unique and sorted (ascending order) values from Column B to be assigned to ComboBox1
2) Depending on what is chosen in Combobox1, corresponding unique values from Column C to be assigned to Combobox2
3) There is a command button which says "Display Data"
4) On pressing the command button "Display Data", depending on the values chosen in ComboBox1 and ComboBox2, the Listbox displays corresponding data from columns D to G in a list box
5) There can be a possibility that no option is chosen in ComboBox2, even then the listbox should display all relevant data in the list box from columns D to G depending on the option chosen in ComboBox1
6) On selecting (or double clicking) the relevant row item from the list box, all the values of the row chosen (column A to G) should be displayed in the text boxes which can be edited.

Appreciate your help in advance.

Warm Regards

Derick
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Hello Derick

This relates to item #1. Are you using a user form or sheet controls?

Code: 
Private Sub UserForm_Click()
Dim cell As Range
[i1] = [b1]                                                         ' header for filter
[b:b].AdvancedFilter xlFilterCopy, [i1:i2], [k1], 1                 ' unique values
Sorter
For Each cell In Range("k2:k" & Range("k" & Rows.Count).End(xlUp).Row)
    Me.ComboBox1.AddItem cell
Next
End Sub


Sub Sorter()
Dim sh As Worksheet
Set sh = ActiveSheet
sh.Sort.SortFields.Clear
sh.Sort.SortFields.Add [k2], xlSortOnValues, xlAscending, , xlSortNormal
With sh.Sort
    .SetRange Range("K2:K" & Range("k" & Rows.Count).End(xlUp).Row)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub
 
Upvote 0
Worf said:
Hello Derick

This relates to item #1. Are you using a user form or sheet controls?

Code: 
Private Sub UserForm_Click()
Dim cell As Range
[i1] = [b1]                                                         ' header for filter
[b:b].AdvancedFilter xlFilterCopy, [i1:i2], [k1], 1                 ' unique values
Sorter
For Each cell In Range("k2:k" & Range("k" & Rows.Count).End(xlUp).Row)
    Me.ComboBox1.AddItem cell
Next
End Sub


Sub Sorter()
Dim sh As Worksheet
Set sh = ActiveSheet
sh.Sort.SortFields.Clear
sh.Sort.SortFields.Add [k2], xlSortOnValues, xlAscending, , xlSortNormal
With sh.Sort
    .SetRange Range("K2:K" & Range("k" & Rows.Count).End(xlUp).Row)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub
Click to expand...


Hello Worf,

Many thanks for this solution to #1. To answer your question, I'm using userform. I'm looking for a complete solution.

Thanks once again.

Derick
 
Upvote 0
I'm looking for a complete solution
Click to expand...

I got that. My week will be busy so we will have to do this one item at a time.
See below code for item #2 and how my test range looks like.

Code: 
Dim cell As Range

Private Sub ComboBox1_Change()
[i1] = [b1]
[i2] = Me.ComboBox1
[m:n].ClearContents
[b:c].AdvancedFilter xlFilterCopy, [i1:i2], [m1], 0                 ' not unique
[p1] = [c1]
[n:n].AdvancedFilter xlFilterCopy, [p1:p2], [r1], 1                 ' unique
Me.ComboBox2.Clear
For Each cell In Range("r2:r" & Range("r" & Rows.Count).End(xlUp).Row)
    Me.ComboBox2.AddItem cell
Next
End Sub

Private Sub UserForm_Click()
[i:r].ClearContents
[i1] = [b1]                                                         ' header for filter
[b:b].AdvancedFilter xlFilterCopy, [i1:i2], [k1], 1                 ' unique values
Sorter
Me.ComboBox1.Clear
For Each cell In Range("k2:k" & Range("k" & Rows.Count).End(xlUp).Row)
    Me.ComboBox1.AddItem cell
Next
End Sub

Plan1

*ABCDEFGHIJKLMNOPQR
*****
******
*******
*******
*********
*********
*********

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:75px;"><col style="width:64px;"><col style="width:64px;"><col style="width:56px;"><col style="width:56px;"><col style="width:64px;"><col style="width:64px;"><col style="width:28px;"><col style="width:64px;"><col style="width:28px;"><col style="width:64px;"><col style="width:33px;"><col style="width:64px;"><col style="width:64px;"><col style="width:32px;"><col style="width:56px;"><col style="width:34px;"><col style="width:52px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]date[/TD]
[TD="align: center"]channel[/TD]
[TD="align: center"]event[/TD]
[TD="align: center"]job[/TD]
[TD="align: center"]descr[/TD]
[TD="align: center"]initiated[/TD]
[TD="align: center"]resource[/TD]

[TD="align: center"]channel[/TD]

[TD="align: center"]channel[/TD]

[TD="align: center"]channel[/TD]
[TD="align: center"]event[/TD]

[TD="align: center"]event[/TD]

[TD="align: center"]event[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]02/03/2016[/TD]
[TD="align: center"]ch1[/TD]
[TD="align: center"]ev1[/TD]
[TD="align: center"]job1[/TD]
[TD="align: center"]desc1[/TD]
[TD="align: center"]in1[/TD]
[TD="align: center"]res1[/TD]

[TD="align: center"]ch3[/TD]

[TD="align: center"]ch1[/TD]

[TD="align: center"]ch3[/TD]
[TD="align: center"]ev1[/TD]

[TD="align: center"]ev1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]02/03/2016[/TD]
[TD="align: center"]ch1[/TD]
[TD="align: center"]ev2[/TD]
[TD="align: center"]job1[/TD]
[TD="align: center"]desc1[/TD]
[TD="align: center"]in1[/TD]
[TD="align: center"]res2[/TD]

[TD="align: center"]ch2[/TD]

[TD="align: center"]ch3[/TD]
[TD="align: center"]ev1[/TD]

[TD="align: center"]ev2[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]02/03/2016[/TD]
[TD="align: center"]ch1[/TD]
[TD="align: center"]ev3[/TD]
[TD="align: center"]job1[/TD]
[TD="align: center"]desc1[/TD]
[TD="align: center"]in2[/TD]
[TD="align: center"]res2[/TD]

[TD="align: center"]ch3[/TD]

[TD="align: center"]ch3[/TD]
[TD="align: center"]ev1[/TD]

[TD="align: center"]ev3[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]02/03/2016[/TD]
[TD="align: center"]ch1[/TD]
[TD="align: center"]ev3[/TD]
[TD="align: center"]job1[/TD]
[TD="align: center"]desc2[/TD]
[TD="align: center"]in3[/TD]
[TD="align: center"]res3[/TD]

[TD="align: center"]ch3[/TD]
[TD="align: center"]ev1[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]02/03/2016[/TD]
[TD="align: center"]ch1[/TD]
[TD="align: center"]ev3[/TD]
[TD="align: center"]job1[/TD]
[TD="align: center"]desc2[/TD]
[TD="align: center"]in3[/TD]
[TD="align: center"]res4[/TD]

[TD="align: center"]ch3[/TD]
[TD="align: center"]ev2[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]02/03/2016[/TD]
[TD="align: center"]ch1[/TD]
[TD="align: center"]ev3[/TD]
[TD="align: center"]job2[/TD]
[TD="align: center"]desc2[/TD]
[TD="align: center"]in3[/TD]
[TD="align: center"]res4[/TD]

[TD="align: center"]ch3[/TD]
[TD="align: center"]ev3[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
Worf said:
I got that. My week will be busy so we will have to do this one item at a time.
See below code for item #2 and how my test range looks like.

Code: 
Dim cell As Range

Private Sub ComboBox1_Change()
[i1] = [b1]
[i2] = Me.ComboBox1
[m:n].ClearContents
[b:c].AdvancedFilter xlFilterCopy, [i1:i2], [m1], 0                 ' not unique
[p1] = [c1]
[n:n].AdvancedFilter xlFilterCopy, [p1:p2], [r1], 1                 ' unique
Me.ComboBox2.Clear
For Each cell In Range("r2:r" & Range("r" & Rows.Count).End(xlUp).Row)
    Me.ComboBox2.AddItem cell
Next
End Sub

Private Sub UserForm_Click()
[i:r].ClearContents
[i1] = [b1]                                                         ' header for filter
[b:b].AdvancedFilter xlFilterCopy, [i1:i2], [k1], 1                 ' unique values
Sorter
Me.ComboBox1.Clear
For Each cell In Range("k2:k" & Range("k" & Rows.Count).End(xlUp).Row)
    Me.ComboBox1.AddItem cell
Next
End Sub

Plan1

*ABCDEFGHIJKLMNOPQR
*****
******
*******
*******
*********
*********
*********

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]date[/TD]
[TD="align: center"]channel[/TD]
[TD="align: center"]event[/TD]
[TD="align: center"]job[/TD]
[TD="align: center"]descr[/TD]
[TD="align: center"]initiated[/TD]
[TD="align: center"]resource[/TD]

[TD="align: center"]channel[/TD]

[TD="align: center"]channel[/TD]

[TD="align: center"]channel[/TD]
[TD="align: center"]event[/TD]

[TD="align: center"]event[/TD]

[TD="align: center"]event[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]02/03/2016[/TD]
[TD="align: center"]ch1[/TD]
[TD="align: center"]ev1[/TD]
[TD="align: center"]job1[/TD]
[TD="align: center"]desc1[/TD]
[TD="align: center"]in1[/TD]
[TD="align: center"]res1[/TD]

[TD="align: center"]ch3[/TD]

[TD="align: center"]ch1[/TD]

[TD="align: center"]ch3[/TD]
[TD="align: center"]ev1[/TD]

[TD="align: center"]ev1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]02/03/2016[/TD]
[TD="align: center"]ch1[/TD]
[TD="align: center"]ev2[/TD]
[TD="align: center"]job1[/TD]
[TD="align: center"]desc1[/TD]
[TD="align: center"]in1[/TD]
[TD="align: center"]res2[/TD]

[TD="align: center"]ch2[/TD]

[TD="align: center"]ch3[/TD]
[TD="align: center"]ev1[/TD]

[TD="align: center"]ev2[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]02/03/2016[/TD]
[TD="align: center"]ch1[/TD]
[TD="align: center"]ev3[/TD]
[TD="align: center"]job1[/TD]
[TD="align: center"]desc1[/TD]
[TD="align: center"]in2[/TD]
[TD="align: center"]res2[/TD]

[TD="align: center"]ch3[/TD]

[TD="align: center"]ch3[/TD]
[TD="align: center"]ev1[/TD]

[TD="align: center"]ev3[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]02/03/2016[/TD]
[TD="align: center"]ch1[/TD]
[TD="align: center"]ev3[/TD]
[TD="align: center"]job1[/TD]
[TD="align: center"]desc2[/TD]
[TD="align: center"]in3[/TD]
[TD="align: center"]res3[/TD]

[TD="align: center"]ch3[/TD]
[TD="align: center"]ev1[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]02/03/2016[/TD]
[TD="align: center"]ch1[/TD]
[TD="align: center"]ev3[/TD]
[TD="align: center"]job1[/TD]
[TD="align: center"]desc2[/TD]
[TD="align: center"]in3[/TD]
[TD="align: center"]res4[/TD]

[TD="align: center"]ch3[/TD]
[TD="align: center"]ev2[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]02/03/2016[/TD]
[TD="align: center"]ch1[/TD]
[TD="align: center"]ev3[/TD]
[TD="align: center"]job2[/TD]
[TD="align: center"]desc2[/TD]
[TD="align: center"]in3[/TD]
[TD="align: center"]res4[/TD]

[TD="align: center"]ch3[/TD]
[TD="align: center"]ev3[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...


Hello Worf,

Point noted. Executed the codes for one and two and they work well. Just that for #1 i inserted the code in the initialize sub instead of userform_click and It works fine.

For #2, i forgot to mention and my sincere apologies for this. I want the list that is populated for ComboBox2 also to be sorted in ascending order.

Looking forward to code #3

Once again, thanks a ton for your help.

Warm Regards

Derick
 
Upvote 0
Items #3 and #4 for your testing:

Code: 
Dim cell As Range


Private Sub ComboBox1_Change()
[i1] = [b1]
[i2] = Me.ComboBox1
[m:n].ClearContents
[b:c].AdvancedFilter xlFilterCopy, [i1:i2], [m1], 0                 ' not unique
[p1] = [c1]
[n:n].AdvancedFilter xlFilterCopy, [p1:p2], [r1], 1                 ' unique
Sorter "r"
Me.ComboBox2.Clear
For Each cell In Range("r2:r" & Range("r" & Rows.Count).End(xlUp).Row)
    Me.ComboBox2.AddItem cell
Next
End Sub


Private Sub CommandButton1_Click()                                  ' display data
[t:ab].ClearContents
[t1] = [b1]
[u1] = [c1]
[t2] = Me.ComboBox1
[u2] = Me.ComboBox2
[b:g].AdvancedFilter xlFilterCopy, [t1:u2], [w1], 0
Me.ListBox1.ColumnCount = 4
Me.ListBox1.RowSource = Range("y1:ab" & Range("y" & Rows.Count).End(xlUp).Row).Address
End Sub


Sub Sorter(c$)
Dim sh As Worksheet
Set sh = ActiveSheet
sh.Sort.SortFields.Clear
sh.Sort.SortFields.Add Range(c & "2"), xlSortOnValues, xlAscending, , xlSortNormal
With sh.Sort
    .SetRange Range(c & "2:" & c & Range(c & Rows.Count).End(xlUp).Row)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub


Private Sub UserForm_Initialize()
[i:r].ClearContents
[i1] = [b1]                                                         ' header for filter
[b:b].AdvancedFilter xlFilterCopy, [i1:i2], [k1], 1                 ' unique values
Sorter "k"                                                          ' column to be sorted
Me.ComboBox1.Clear
For Each cell In Range("k2:k" & Range("k" & Rows.Count).End(xlUp).Row)
    Me.ComboBox1.AddItem cell
Next
End Sub
 
Upvote 0
Item #6 :

Code: 
Dim cell As Range


Private Sub ComboBox1_Change()
[i1] = [b1]
[i2] = Me.ComboBox1
[m:n].ClearContents
[b:c].AdvancedFilter xlFilterCopy, [i1:i2], [m1], 0                 ' not unique
[p1] = [c1]
[n:n].AdvancedFilter xlFilterCopy, [p1:p2], [r1], 1                 ' unique
Sorter "r"
Me.ComboBox2.Clear
For Each cell In Range("r2:r" & Range("r" & Rows.Count).End(xlUp).Row)
    Me.ComboBox2.AddItem cell
Next
End Sub


Private Sub CommandButton1_Click()                                  ' display data
[t:ac].ClearContents
[t1] = [b1]
[u1] = [c1]
[t2] = Me.ComboBox1
[u2] = Me.ComboBox2
[a:g].AdvancedFilter xlFilterCopy, [t1:u2], [w1], 0
Me.ListBox1.ColumnCount = 4
Me.ListBox1.RowSource = Range("z1:ac" & Range("y" & Rows.Count).End(xlUp).Row).Address
End Sub


Sub Sorter(c$)
Dim sh As Worksheet
Set sh = ActiveSheet
sh.Sort.SortFields.Clear
sh.Sort.SortFields.Add Range(c & "2"), xlSortOnValues, xlAscending, , 0
With sh.Sort
    .SetRange Range(c & "2:" & c & Range(c & Rows.Count).End(xlUp).Row)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub


Private Sub ListBox1_Click()        ' assumes text box names as TextBox1, TextBox2,...
Dim i%
For i = 1 To 7                      ' A to G
    Me.Controls("TextBox" & i) = [v1].Offset(Me.ListBox1.ListIndex, i)
Next
End Sub


Private Sub UserForm_Initialize()
[i:r].ClearContents
[i1] = [b1]                                                         ' header for filter
[b:b].AdvancedFilter xlFilterCopy, [i1:i2], [k1], 1                 ' unique values
Sorter "k"                                                          ' column to be sorted
Me.ComboBox1.Clear
For Each cell In Range("k2:k" & Range("k" & Rows.Count).End(xlUp).Row)
    Me.ComboBox1.AddItem cell
Next
End Sub
 
Upvote 0
Worf said:
Item #6 :

Code: 
Dim cell As Range


Private Sub ComboBox1_Change()
[i1] = [b1]
[i2] = Me.ComboBox1
[m:n].ClearContents
[b:c].AdvancedFilter xlFilterCopy, [i1:i2], [m1], 0                 ' not unique
[p1] = [c1]
[n:n].AdvancedFilter xlFilterCopy, [p1:p2], [r1], 1                 ' unique
Sorter "r"
Me.ComboBox2.Clear
For Each cell In Range("r2:r" & Range("r" & Rows.Count).End(xlUp).Row)
    Me.ComboBox2.AddItem cell
Next
End Sub


Private Sub CommandButton1_Click()                                  ' display data
[t:ac].ClearContents
[t1] = [b1]
[u1] = [c1]
[t2] = Me.ComboBox1
[u2] = Me.ComboBox2
[a:g].AdvancedFilter xlFilterCopy, [t1:u2], [w1], 0
Me.ListBox1.ColumnCount = 4
Me.ListBox1.RowSource = Range("z1:ac" & Range("y" & Rows.Count).End(xlUp).Row).Address
End Sub


Sub Sorter(c$)
Dim sh As Worksheet
Set sh = ActiveSheet
sh.Sort.SortFields.Clear
sh.Sort.SortFields.Add Range(c & "2"), xlSortOnValues, xlAscending, , 0
With sh.Sort
    .SetRange Range(c & "2:" & c & Range(c & Rows.Count).End(xlUp).Row)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub


Private Sub ListBox1_Click()        ' assumes text box names as TextBox1, TextBox2,...
Dim i%
For i = 1 To 7                      ' A to G
    Me.Controls("TextBox" & i) = [v1].Offset(Me.ListBox1.ListIndex, i)
Next
End Sub


Private Sub UserForm_Initialize()
[i:r].ClearContents
[i1] = [b1]                                                         ' header for filter
[b:b].AdvancedFilter xlFilterCopy, [i1:i2], [k1], 1                 ' unique values
Sorter "k"                                                          ' column to be sorted
Me.ComboBox1.Clear
For Each cell In Range("k2:k" & Range("k" & Rows.Count).End(xlUp).Row)
    Me.ComboBox1.AddItem cell
Next
End Sub
Click to expand...

Hello Worf,

The forum was down yesterday hence could not send you a message. The codes for #1 to 6 are working fine. A few thoughts and i request your help;
a) I plan to include a Command Button, called "Clear" to clear the selected items in the two combo boxes and the listbox. The combobox drop down should however show the options.
b) A Command Button named "Copy" to copy the visible data in the listbox along with the headers to another sheet
c) A Command Button named "Update" to update the excel sheet with the modified data in the userform. Right now, the data is being displayed form the temp cells and not the main sheet. If i need to update the values in the main sheet how do i do that?
d) The listbox_click sub displays the data from column [v], but that cell is empty and hence textbox1 which actually should display the date, displays the channel, Textbox2, which should display the channel, displays the Event and so on and Textbox7 is actually blank

Also, columns I to AB are being used for storing data to be used in the userform. How can the code be modified such that, we use another sheet to store this temporary data instead of using the main sheet?

I guess, this is all i need for this project. You have been more than helpful Worf and thank you so much!

Warm Regards

Derick
 
Upvote 0
Hi

Can we have a new field (column) at the data table containing sequential numbers (1, 2, 3…) to identify the records (rows)?
This would ease the programming task.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Listbox Filter using a combobox
Replies
1
Views
3K
DanteAmor
DanteAmor
G
  • Question Question
Multiple Dependency combo box Excel 2010 VBA.
Replies
0
Views
684
Gskr3499
G
V
  • Question Question
VBA Code for selected items in listbox displayed in Excel on a row
Replies
5
Views
3K
VLange
V
B
  • Question Question
HELP - Advanced Userform 3 level dependent drop down
Replies
1
Views
2K
MickG
M
V
  • Question Question
VBA coding to autofilter the data ( with some criteria), when the data starts from row 3
Replies
0
Views
438
Vishnu Kariappa
V

Forum statistics

Threads
1,223,911
Messages
6,175,322
Members
452,635
Latest member
laura12345

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