combine 3 combo boxes values into one string

Hi all,

I have a table of data which can filter out my desired information after i selected a range of date, for example 1-Jan-2006 to 15-Jan-2006, in a combo box. I can only choose date being stated in the combo box, not much flexibility.
Now, i want to change the format of the combo box. Instead of one combo box which show the date, i want three combo boxes which show "Year", "Month" and "Day". I can choose any year, any month and any day i want and it will filter out. I am thinking of a solution by combining the values of the three combo boxes into one string, one line "1-Jan-2006", and start to filtering. Is this the only way to do it or is there any better ways to do this
Below is my existing VBA code:

Private Sub OkButton_Click()
If ComboBox2.Text <> "" Then
FilterWeek Format(Sheets("WeekDate").Cells(ComboBox2.ListIndex + 2, 2), "dd-mmm-yyyy"), _
Format(Sheets("WeekDate").Cells(ComboBox2.ListIndex + 2, 3), "dd-mmm-yyyy")
End If
End Sub

Sub FilterWeek(strCriteria1 As String, strCriteria2 As String)
With Sheet7
.AutoFilterMode = False
.Range("A1:J1").AutoFilter
.Range("A1:J1").AutoFilter Field:=3, Criteria1:=">=" & strCriteria1, _
Operator:=xlAnd, Criteria2:="<=" & strCriteria2
End With
End Sub

Thanks for anyone who willing to spend your time to help me solving this problem. Thanks! :)

Answer this question

combine 3 combo boxes values into one string

DimSum
Create a new userform and on the toolbox right click anywhere in the tab control, select 'Additional Controls' from the menu that appears, and in the list that appears select Calander Control.
GeorgeMohr
Try using the Microsoft Date and time picker control, this will let you select any valid date, with your code you will need to check the date is valid i.e disallow 31 June etc.
Ijaz Ali
So where can I find the Microsoft Date and time picker control
Mark Terry
It sounds like your asking how to concatenate strings

If so then perhaps your trying to acomplish something like this:

ComboBoxDay.Text & " - " & ComboBoxMonth.Text & " - " & ComboBoxYear.Text
bmartling
Hi.. Thanks for ur tips.
But other than this, is there any way that i can accomplish the question i asked This is just an extra question, for further information. :) thanks anyway..
David S. Anderson
Thanks for your tips.

ADG wrote:
Try using the Microsoft Date and time picker control, this will let you select any valid date, with your code you will need to check the date is valid i.e disallow 31 June etc.
combine 3 combo boxes values into one string

Answer this question