Date format vba code...in excel form.....textbox?

Question

so what i want is that i have a text box ...in which i want that if the user adds the date as ddmmyy OR ddmmyyyy then once the user goes to next textbox or anything for that matter the date shud change to dd/mm/yyyy...........


also another thing i want to do is i have two combo boxes cmb2 and cmb3----they are both dependant...that means if the user selects team in cmb1, the names of all his team members appear in the cmb3....
so what i have dont is that the range source of cmb2 is populated from a sheet named Teams....column 1 ( which has the names of teams ...lets say john, ian etc)
and then i have made different sheet for each team ....for example the name of all the team members of john show in the first column of another sheet which is named john and so on ....

so to use this cascading combo boxes i am using following code:
Private Sub cmb1_Click()
With cmb1
PopulateName .List(.ListIndex)
End With
End Sub



Private Sub UserForm_Initialize()
cmb1.Value = 0
Dim rngSrc As Range
Set rngSrc = Sheets("Teams").Range("A1")
Do While rngSrc.Value <> ""
Me.cmb1.AddItem rngSrc.Value
Set rngSrc = rngSrc.Offset(1)
Loop
Me.cmb1.ListIndex = 0
End Sub

Private Sub PopulateName(strTeams As String)
Dim rngSrc As Range
Set rngSrc = Sheets(strTeams).Range("A1")
With Me.cmb2
.Clear
Do While rngSrc.Value <> ""
.AddItem rngSrc.Value
Set rngSrc = rngSrc.Offset(1)
Loop
.ListIndex = 0
End With
End Sub

but what i want is that by default when the form loads then then cmb 2 should be clear and when the user makes a selection cmb3 should also be clear and then he can make a selection....

i hope i make sense

Answer 1

Devilishblueyes makes some excellent points. He's obviously done this before and I must admit, I've learned a thing or two from him. Here is a trick for reformatting the date in your textbox if you still want to go that way: Use this code for the Exit event of your textbox (I have assumed that you have named it txtDate):



Private Sub txtDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)

  Dim objRegex As Object

  Dim objMatches As Object

  Dim strDDMM As String

  Dim strRegex

  strRegex = "^((0[1-9])|([12][0-9])|(3[01]))" & _

    "((0[1-9])|(1[012]))" & _

    "(19|20)?(\d\d)$"

  Set objRegex = CreateObject("VBScript.Regexp")

  With objRegex

    .Pattern = strRegex

    .Global = False

    Set objMatches = .Execute(txtDate.Value)

  End With

  If objMatches.Count > 0 Then

    With txtDate

      strDDMM = Left(.Value, 2) & "/" & Mid(.Value, 3, 2) & "/"

      If Len(.Value) = 6 Then ' user entered ddmmyy

        If Val(Right(.Value, 2)) > 50 Then

          strDDMM = strDDMM & "19"

        Else

          strDDMM = strDDMM & "20"

        End If

        .Value = strDDMM & Right(.Value, 2)

      Else ' user entered ddmmyyyy

        .Value = strDDMM & Right(.Value, 4)

      End If

    End With

    Else ' user entered an invalid date

      MsgBox "Invalid Date Entered" & vbCrLf & _

        "Must enter in one of these forms:" & vbCrLf & _

        "ddmmyy OR ddmmyyyy", vbOKOnly + vbCritical, _

        "Date Entry Error"

      Cancel = True

  End If

End Sub



This forces the user to enter properly formatted dates. If you want to put a little more effort into the regular expression you could even account for Actual dates and leap years, etc. (I just didn't want to invest quite that much time here). Note that I don't know what range of dates your textbox will be taking so I assumed they would be in the range 1951-2050.



Now for your comboboxes:



.ListIndex = 0



makes the first item in the combobox the default "selected" item. If you don't want that then simply delete or comment out that line and the comboboxes will be blank until the user makes a selection.



Hope that helps...

Answer 2

Vba Date Formats

Answer 3

Ok, I think I get what you are trying to do. I've done stuff similar to that a number of times before myself.



From what I understand if I get this right is you want to have combo1 filled when the form initializes. Then then a selection is made from combo1, then the list for combo2 is populated. Then when a selection from combo2 is made, then the list for combo3 is populated.



Here's some suggestions I'd make:



1. Populate your list slightly differently. Use the Intersect() method to get all of the names. You may be missing a larger portion of the names if you do it the way you are doing. I'd do it something like this:



Dim rng1 As Range

Dim rng2 As Range



Set rng1 = Intersect(ActiveSheet.UsedRange, Range("A:A"))



For Each rng2 In rng1



If rng2.Value <> "" Then

cmb1.AddItem rng2.Value

End If



Next rng2





With your code, when it gets to the first blank cell it will quit adding names to the list. This makes sure the macro grabs every filled cell in that column. You might also put another if in there to avoid adding the header cell. Like you could put something like:



If rng2.Value <> "" And _

rng2.Value <> Cells(ActiveSheet.UsedRange.Row, 1) Then



This skips the first row in the Used range. The header row might not always be in Row 1 if the data starts on say row 3.



2. I recommend disabling combos 2 & 3 until the user makes the appropriate selections in Combos 1 and 2. Use the Change event for the combo box to trigger populating the next combo box and enabling it. You don't want combos 2 and 3 enabled until the appropriate time or the user could mess things up. Here's what it should somewhat look like:



Private Sub cmb1_Change()

cmb2.Clear

cmb3.Clear



If cmb1.ListIndex = -1 Then

MsgBox "Please pick a value from the list.", vbOKOnly + vbExclamation, "Entry Error"

cmb2.Enabled = False

cmb3.Enabled = False

Exit Sub

Else

cmb2.Enabled = True

cmb3.Enabled = False

'Code to populate combo2 here

End If



End Sub



Private Sub cmb2_Change()

cmb3.Clear



If cmb2.ListIndex = -1 Then

MsgBox "Please pick a value from the list.", vbOKOnly + vbExclamation, "Entry Error"

cmb3.Enabled = False

Exit Sub

Else

cmb3.Enabled = False

'Code to populate combo3 here

End If



End Sub





Here's basically what they do. First you want to clear the current lists of the next combo boxes. If the user makes a change to combo1 you want to clear the lists for combos 2 and 3. If the user makes a change to combo 2 then clear the list for combo 3. This makes sure the list doesn't have multiple lists added to it. And it avoids potential errors.



Secondly, you want to do the ListIndex check to make sure the value isn't -1. If it is -1 then that means the user typed in a value that isn't in the List for that combo box. You want to allow the user to type in values, but you also want to make sure that the value is a value in the list. This check does that.



Then you want to enable and disable combo boxes. If the user makes a change to combo1 you want to make sure combo2 is enabled as long as the ListIndex does not equal -1. And you want to make sure that combo3 is not enabled. You only want the very next combo enabled if the user makes the right selection.



You have to try thinking of every possible thing the user might do. I might make selections for all three then I go back and decide...WAIT!!! I need to change combo1. So I need Combo1 enabled and when I make a change to Combo 1, then I need to disable and clear comb3.



I try making my VBA programs as user friendly as I can and I try accounting for every possible "monkey wrench in the clockworks" change they might make so I avoid some error happening.



As far as what you want to do with the text boxes, that is not the best idea. It can be done but you are potentially evaluating a long string of text. I'd rather force the user to enter dates in an inputbox or into a cell where you can control what they enter much more easily and format it more easily. Think about it. What if they wrote this in the textbox:



"On 12/12/2009 I headed to work."



How do you change that?



Or what if they put in...



"On December 12, 2009 I headed to work."



Then in European style they put the day first. And you are evaluating strings which are not easy to evaluate. And there are tons of styles the text could be entered in. Limit what they can put in. Or even use something like a DatePicker control.

Answer 4

MyStr = Format(MyDate, "d-mmm-yy") Have fun.