Strange combobox problem

[tpoynton]

Greetings - I am having an issue that I can not duplicate in the workbook posted here, so I'll need to explain...

I am having a problem in my file, it seems, that when the change event of the combobox is fired, the listindex is set to -1. This has worked previously for quite some time in this file, so I'm not sure what has changed. I had (finally) moved from creating the file to developing documentation for it, and then I ran across this problem...ugh!

to get help with this, I put together a sample workbook, attached, which contains identical procedures for populating the comboboxes and getting the listindex in a subsequent procedure. The only exception is that the 'print' procedure that is called when clicking the create report button has a bunch of variables that are declared before trying to get the combobox listindex values - nothing other than declaring variables, I promise

If I comment out the change events for the comboboxes (which simply format the value as a date again), all works well in my main file. Again, in this example, it does work...so I'm wondering what I should be looking for to fix this?

here's the main code. full code is in the attachment.

[vba]
Option Explicit
Private Sub UserForm_Activate()
OptionButton_Range.Value = True
ComboBox_Start.AddItem "Placeholder"
ComboBox_End.AddItem "Placeholder"
Dim rDateRange As Range
With ThisWorkbook.Sheets("Data")
Set rDateRange = .Range(.Cells(1, 1), .Cells(.UsedRange.Rows.Count, 1))
End With
If WorksheetFunction.CountA(rDateRange) < 2 Then
MsgBox "Not enough data to create a report"
Unload Me
Exit Sub
End If
'this is in the attached sample
Call UniqueInRangeCbox(rDateRange, Me.ComboBox_Start)
With ComboBox_End
.RowSource = vbNullString
.RowSource = ComboBox_Start.RowSource
End With
Set rDateRange = Nothing
End Sub
'-------another sub--------
Private Sub OptionButton_Range_Change()
If OptionButton_Range.Value = True Then
ComboBox_Start.Enabled = True
ComboBox_End.Enabled = True
Else
ComboBox_End.Enabled = False
ComboBox_Start.Enabled = False
End If
End Sub
'-------another sub--------
Private Sub CmdBtn_Print_Click()
'MsgBox "Cbox Start ListIndex = " & ComboBox_Start.ListIndex & vbNewLine & _
"Cbox End ListIndex = " & ComboBox_End.ListIndex

If OptionButton_Range.Value = True Then
If ComboBox_Start.ListIndex = -1 Or ComboBox_End.ListIndex = -1 Then
MsgBox "You need to select start and end dates to create a report"
Exit Sub
ElseIf ComboBox_End.ListIndex < ComboBox_Start.ListIndex Then
MsgBox "You need to select an end date after the start date"
Exit Sub
End If
End If
'added for testing
MsgBox "Cbox Start ListIndex = " & ComboBox_Start.ListIndex & vbNewLine & _
"Cbox End ListIndex = " & ComboBox_End.ListIndex
End Sub
'-------another sub--------
Private Sub ComboBox_End_Change()
With ComboBox_End
.Value = Format(.Value, "m/d/yyyy")
End With
End Sub
'-------another sub--------
Private Sub ComboBox_Start_Change()
With ComboBox_Start
.Value = Format(.Value, "m/d/yyyy")
End With
End Sub
'-------another sub--------
Private Sub CmdBtn_Exit_Click()
Unload Me
End Sub
[/vba]

Probably unrelated, but another strange thing...setting curline to 0 on a multiline textbox produces an error for me. If you click the second button and do not get an error, then it does not affect you. This issue has been described here - unfortunately, the original microsoft board was not available, it seems.

[p45cal]

re: "that when the change event of the combobox is fired, the listindex is set to -1."
and
"(which simply format the value as a date again)"

Yes, in this case your code sets the listindex to -1. You use the code:
.Value = Format(.Value, "m/d/yyyy")
and to stop the invalid property value message from popping up you've set the combobox's MatchRequired property to False. The code actually does change the value of the combobox to a value not in the list (despite appearances to the contrary) so the list index can no longer be a value in the list, so it is set to -1. Incidentally, because your code changed the combobox value, it calls itself repeatedly - there's a suggestion in the commented out code as how to stop that (There's a global variable EventBlocked declared at the top of the module.).

Solution. You want to keep dates looking like dates, instead of becoming numbers in the combobox once you've selected them.
Instead of setting the Combobox RowSource property to an address string, set the list using the List property. Now there will no longer be any need to change the appearance of the date in the combobox. When a combobox's dropdown values get assigned this way and they're dates, it so happens that Excel transforms them to American style dates which is what you want. However, should you have wanted another date format in the comboxes, that is also possible and have shown in the code how to do that optionally. I would set the MatchRequired property to True (manually, as a one off). See code in attached.

Regarding the curline problem, my Excel 2003 help file says:
"The CurLine property is valid when the control has the focus."
So adding:
TextBox1.SetFocus
before:
TextBox1.CurLine = 0
solves it here.

[tpoynton]

Thank you very much p45cal; it's going to take me a few days to work through your suggestions because I'm slow, the kids are home, and I'm currently on a mac (I hate the VBE on the mac). I did take a quick peek...I have to admit, I've never populated anything with .list.

RE: the textbox issue, it's an aside and I appreciate your input; I did have setfocus set in my main file, and I apologize for the oversight...again, I'll need to get back on the windows machine to see if your file helps for some other reason.

Much appreciated! tim