combine 3 combo boxes values into one string  
Author Message
pippen





PostPosted: Visual Basic for Applications (VBA), combine 3 combo boxes values into one string Top

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! :)





Microsoft ISV Community Center Forums1  
 
 
Jan Hyde





PostPosted: Visual Basic for Applications (VBA), combine 3 combo boxes values into one string Top

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


 
 
pippen





PostPosted: Visual Basic for Applications (VBA), combine 3 combo boxes values into one string Top

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..

 
 
ADG





PostPosted: Visual Basic for Applications (VBA), combine 3 combo boxes values into one string Top

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.
 
 
pippen





PostPosted: Visual Basic for Applications (VBA), combine 3 combo boxes values into one string Top

Thanks for your tips.

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.

 
 
terrym21





PostPosted: Visual Basic for Applications (VBA), combine 3 combo boxes values into one string Top

So where can I find the Microsoft Date and time picker control
 
 
Derek Smyth





PostPosted: Visual Basic for Applications (VBA), combine 3 combo boxes values into one string Top

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.