tpoynton
08-12-2009, 11:36 AM
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.
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
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 (http://www.eggheadcafe.com/software/aspnet/33410274/textboxcurline0-gives-r.aspx) - unfortunately, the original microsoft board was not available, it seems.
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.
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
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 (http://www.eggheadcafe.com/software/aspnet/33410274/textboxcurline0-gives-r.aspx) - unfortunately, the original microsoft board was not available, it seems.