|
|
combine 3 combo boxes values into one string |
|
Author |
Message |
pippen
|
Posted: 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 Forums3
|
|
|
|
|
Jan Hyde
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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.
|
|
|
|
|
|
|