Filtered ComboBox?

[Big Lar]

This UserForm displays calculated values from a datasheet with several hundred rows. Range A is the date.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I’m attempting to reduce the ComboBox list to only the data rows for the current month. So, if today is 2/15/11, ComboBox1 offers only Feb dates.

Do I need to add a "Select Month" Combobox?<o></o>
<o></o>
Any help with the code to accomplish this is appreciated.<o></o>
<o></o>

Private Sub UserForm_Initialize()<o:p></o:p>
Dim x&<o:p></o:p>
With Sheets("Sheet1")<o:p></o:p>
For x = 2 To .Cells(Rows.Count, "A").End(xlUp).Row<o:p></o:p>
  ComboBox1.AddItem (.Range("A" & x).Value)<o:p></o:p>
Next x<o:p></o:p>
End With<o:p></o:p>
End Sub
 
Private Sub ComboBox1_Change()
TextBox1.Value = (Format(Range("T" & ComboBox1.ListIndex + 2) + Range("U" & ComboBox1.ListIndex + 2) * 0.25, "$ #.00"))<o:p></o:p>
TextBox2.Value = (Format(Range("V" & ComboBox1.ListIndex + 2) + Range("W" & ComboBox1.ListIndex + 2) * 0.25, "$ #.00"))<o:p></o:p>
TextBox3.Value = (Format(Range("X" & ComboBox1.ListIndex + 2) + Range("Y" & ComboBox1.ListIndex + 2) * 0.25 + Range("Z" & ComboBox1.ListIndex + 2), "$ #.00"))<o:p></o:p>
 TextBox5.Value = (Format(Range("AE" & ComboBox1.ListIndex + 2), "$ #.00"))<o:p></o:p>
   TextBox6.Value = (Format(Range("AF" & ComboBox1.ListIndex + 2), "$ #.00"))<o:p></o:p>
    TextBox7.Value = (Format(Range("AD" & ComboBox1.ListIndex + 2), "$ #.00"))<o:p></o:p>
     TextBox8.Value = (Format(Range("AG" & ComboBox1.ListIndex + 2), "$ #.00"))<o:p></o:p>
      TextBox9.Value = (Format(Range("AH2"), "$ #.00"))
End Sub<o:p></o:p>

<o></o>

 

[Norie]

If it is the current month then you might not need a combobox at all.

If you want the user to be able to chose the month then you might.

By the way, how many textboxes do you have?

Have you looked into using a listbox to display all the data?

Private Sub UserForm_Initialize()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Dim x As Long

   With Sheets("Sheet1")<o:p></o:p>
       For x = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
           If Month(.Range("A" & x)) = Month(Now()) Then 
                 ComboBox1.AddItem (.Range("A" & x).Value)
           End If<o:p></o:p>
       Next x<o:p></o:p>
    End With
<o:p></o:p>
End Sub

 

[kunal_shrivastava]

Hi Big Lar,

Can you please share the sample data in your sheet?
This will help us to understand the problem correctly.
If my understanding is correct then below code will fill your combobox with only current month data:

Private Sub UserForm_Initialize()
Dim x&
With Sheets("Sheet1")
For x = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
If Month(Now()) = Month(Sheets("Sheet1").Range("A" & x)) Then
  ComboBox1.AddItem (.Range("A" & x).Value)
  End If
Next x
End With
End Sub

 

[Big Lar]

If you want the user to be able to chose the month then you might.

That’s probably a better idea.

By the way, how many textboxes do you have?

Currently, there are 9 textboxes displaying various calculated values from Sheet1 data

Have you looked into using a listbox to display all the data?

No…not sure how or why
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o>
<o></o>

Can you please share the sample data in your sheet?

<o></o>
<o></o>
Excel 2003<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000; COLOR: #ffffff; FONT-WEIGHT: bold">Date</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000; COLOR: #ffffff; FONT-WEIGHT: bold">Counter</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000; COLOR: #ffffff; FONT-WEIGHT: bold">Prior</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000; COLOR: #ffffff; FONT-WEIGHT: bold">Diff</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000; COLOR: #ffffff; FONT-WEIGHT: bold">$1Bills</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000; COLOR: #ffffff; FONT-WEIGHT: bold">$5Bills</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000; COLOR: #ffffff; FONT-WEIGHT: bold">$10Bills</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000; COLOR: #ffffff; FONT-WEIGHT: bold">$20Bills</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000; COLOR: #ffffff; FONT-WEIGHT: bold">Total</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000; COLOR: #ffffff; FONT-WEIGHT: bold">Over/Under</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">04/03/11</TD><TD style="TEXT-ALIGN: right">543824</TD><TD style="TEXT-ALIGN: right">543551</TD><TD style="TEXT-ALIGN: right">273</TD><TD style="TEXT-ALIGN: right">93</TD><TD style="TEXT-ALIGN: right">22</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">273</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">04/04/11</TD><TD style="TEXT-ALIGN: right">544036</TD><TD style="TEXT-ALIGN: right">543824</TD><TD style="TEXT-ALIGN: right">212</TD><TD style="TEXT-ALIGN: right">91</TD><TD style="TEXT-ALIGN: right">18</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">211</TD><TD style="TEXT-ALIGN: right">-1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">04/05/11</TD><TD style="TEXT-ALIGN: right">545852</TD><TD style="TEXT-ALIGN: right">544036</TD><TD style="TEXT-ALIGN: right">1816</TD><TD style="TEXT-ALIGN: right">816</TD><TD style="TEXT-ALIGN: right">60</TD><TD style="TEXT-ALIGN: right">50</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">1816</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">04/06/11</TD><TD style="TEXT-ALIGN: right">545952</TD><TD style="TEXT-ALIGN: right">545852</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">0</TD></TR></TBODY></TABLE>

Sheet1​

<o></o>
</o>
<o></o>
<o></o>
<o></o>
<o></o>

 

[Norie]

Is that the actual data?

What happened to columns T-Y and AD-AH?

The reason I ask is because I could post code to populate a listbox for either setup.

ie the data being in columns A-J and it being in columns mentioned above.

They code would be quite different for each setup though and I'd prefer to post something for the 'real' setup.

 

[Big Lar]

Norie,

No, it's just the first 10 columns. The data goes to "AI"

Here's the Header and 1st four rows of data.
Excel 2003<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH><TH>N</TH><TH>O</TH><TH>P</TH><TH>Q</TH><TH>R</TH><TH>S</TH><TH>T</TH><TH>U</TH><TH>V</TH><TH>W</TH><TH>X</TH><TH>Y</TH><TH>Z</TH><TH>AA</TH><TH>AB</TH><TH>AC</TH><TH>AD</TH><TH>AE</TH><TH>AF</TH><TH>AG</TH><TH>AH</TH><TH>AI</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Date</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">ECounter</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Prior</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Ediff</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">E$1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">E$5</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">E$10</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">E$20</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">ETotal</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Over/Under</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">WCounter</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">WPrior</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">WDiff</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">West$1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">W$5</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">W$10</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">W$20</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">WTotal</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Over/Under</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Bays$1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Bays25?</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Vac$1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Vac25?</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Vend$1coin</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Vend25?</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">VendBills</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">EHops</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">WHops</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">$1CoinExch</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">PaidOut</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">DepositBills </TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">DepositCoin </TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">DepositTotal </TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">TotalDeposits </TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">$4,282.50 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">04/03/11</TD><TD style="TEXT-ALIGN: right">543824</TD><TD style="TEXT-ALIGN: right">543551</TD><TD style="TEXT-ALIGN: right">273</TD><TD style="TEXT-ALIGN: right">93</TD><TD style="TEXT-ALIGN: right">22</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">273</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">376483</TD><TD style="TEXT-ALIGN: right">375942</TD><TD style="TEXT-ALIGN: right">541</TD><TD style="TEXT-ALIGN: right">196</TD><TD style="TEXT-ALIGN: right">35</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">541</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">612</TD><TD style="TEXT-ALIGN: right">87</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">137</TD><TD style="TEXT-ALIGN: right">271</TD><TD style="TEXT-ALIGN: right">202</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">$ 612.00 </TD><TD style="TEXT-ALIGN: right">$ 21.75 </TD><TD style="TEXT-ALIGN: right">$ 633.75 </TD><TD style="TEXT-ALIGN: right">$ 2,053.00 </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">04/04/11</TD><TD style="TEXT-ALIGN: right">544036</TD><TD style="TEXT-ALIGN: right">543824</TD><TD style="TEXT-ALIGN: right">212</TD><TD style="TEXT-ALIGN: right">91</TD><TD style="TEXT-ALIGN: right">18</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">211</TD><TD style="TEXT-ALIGN: right">-1</TD><TD style="TEXT-ALIGN: right">376981</TD><TD style="TEXT-ALIGN: right">376483</TD><TD style="TEXT-ALIGN: right">498</TD><TD style="TEXT-ALIGN: right">199</TD><TD style="TEXT-ALIGN: right">34</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">499</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">143</TD><TD style="TEXT-ALIGN: right">791</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">106</TD><TD style="TEXT-ALIGN: right">250</TD><TD style="TEXT-ALIGN: right">567</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">$ 143.00 </TD><TD style="TEXT-ALIGN: right">$ 197.75 </TD><TD style="TEXT-ALIGN: right">$ 340.75 </TD><TD style="TEXT-ALIGN: right">$ 2,393.75 </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">04/05/11</TD><TD style="TEXT-ALIGN: right">545852</TD><TD style="TEXT-ALIGN: right">544036</TD><TD style="TEXT-ALIGN: right">1816</TD><TD style="TEXT-ALIGN: right">816</TD><TD style="TEXT-ALIGN: right">60</TD><TD style="TEXT-ALIGN: right">50</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">1816</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">377081</TD><TD style="TEXT-ALIGN: right">376981</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">99</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">104</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">500</TD><TD style="TEXT-ALIGN: right">400</TD><TD style="TEXT-ALIGN: right">500</TD><TD style="TEXT-ALIGN: right">400</TD><TD style="TEXT-ALIGN: right">500</TD><TD style="TEXT-ALIGN: right">400</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">908</TD><TD style="TEXT-ALIGN: right">52</TD><TD style="TEXT-ALIGN: right">420</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">$1,500.00 </TD><TD style="TEXT-ALIGN: right">$300.00 </TD><TD style="TEXT-ALIGN: right">$1,800.00 </TD><TD style="TEXT-ALIGN: right">$1,800.00 </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">04/06/11</TD><TD style="TEXT-ALIGN: right">545952</TD><TD style="TEXT-ALIGN: right">545852</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">377100</TD><TD style="TEXT-ALIGN: right">377081</TD><TD style="TEXT-ALIGN: right">19</TD><TD style="TEXT-ALIGN: right">19</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">19</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">25</TD><TD style="TEXT-ALIGN: right">50</TD><TD style="TEXT-ALIGN: right">25</TD><TD style="TEXT-ALIGN: right">50</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">50</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">69</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">$50.00 </TD><TD style="TEXT-ALIGN: right">$25.00 </TD><TD style="TEXT-ALIGN: right">$75.00 </TD><TD style="TEXT-ALIGN: right">$75.00 </TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

Sheet1​

 

[Norie]

So the columns in the original code are the ones you want on the userform?

Here's a start, it doesn't do any filtering but it does populate the listbox with all the data - including the date from column A.

Just add a listbox to a blank userform and run it (the form not the code).

Option Explicit
 
Private Sub UserForm_Initialize()
Dim arrcols
Dim I As Long
Dim J As Long
Dim rng As Range
 
    ListBox1.ColumnCount = 9
 
    ListBox1.ColumnWidths = Replace(String(9, "X"), "X", "50pt;")
 
    For I = 2 To Range("A" & Rows.Count).End(xlUp).Row

        ListBox1.AddItem Range("A" & I).Text
 
        ListBox1.List(ListBox1.ListCount - 1, 1) = Format(Range("T" & I) + Range("U" & I) * 0.25, "Currency")
 
        ListBox1.List(ListBox1.ListCount - 1, 2) = Format(Range("V" & I) + Range("W" & I) * 0.25, "Currency")
 
        ListBox1.List(ListBox1.ListCount - 1, 3) = Format(Range("X" & I) + Range("Y" & I) * 0.25 + Range("Z" & I), "Currency")

        arrcols = Array("AE", "AF", "AD", "AG", "AH")
 
        For J = LBound(arrcols) To UBound(arrcols)
 
            Set rng = Range(arrcols(J) & I)
            
            ListBox1.List(ListBox1.ListCount - 1, J + 4) = Format(rng.Value, "Currency")
            
        Next J
        
    Next I
    
End Sub

If you are interested in this approach I can post code for a combobox, textboxes to edit etc.