Little bug in my userform

[Stift]

I've got a small 'bug' in my userform but dunno how to fix it.
Further everything is working perfectly.

When all the cells are filled and/or red background then I get th
error message: Could not set ListIndex property. Invalid propert
value.

here's the code:

Code
-------------------

Private Sub UserForm_Initialize()

Dim Cell As Range
Dim Counter As Long

Dim ListRange As Range
Dim CListRange As Range
Dim ListRangeValue() As Variant
Dim CListRangeValue() As Variant

Set CListRange = _
ActiveSheet.Range("Q9:Q106,Q113:Q162,Q169:Q183")

Set ListRange = _
ActiveSheet.Range("C9:C106,C113:C162,C169:C183")
ListRange.Style.IncludeNumber = True


ReDim ListRangeValue(0 To ListRange.Cells.Count)

For Each Cell In ListRange.Cells


If Cell.Interior.ColorIndex <> 3 Then

Set Celv = Cells(Cell.Row, 16 + Weekscherm.ComboBox1.Value)

If Celv = "" Then

ListRangeValue(Counter) = Cell.Value
Counter = Counter + 1
Me.ComboBox2.AddItem (Cell.Row)
Me.ComboBox3.AddItem Cells(Cell.Row, 2)

End If
End If

Next Cell


Me.ComboBox1.List = ListRangeValue

Me.ComboBox1.ListIndex = 0 Me.ComboBox2.ListIndex = 0
Me.ComboBox3.ListIndex = 0

End Sub



Private Sub CommandButton1_Click()

X = Me.ComboBox1.ListIndex
If Me.TextBox1.Text = "" Or Me.TextBox1.Text = "" Then
X = X - 1
Else
X = Me.ComboBox1.ListIndex

Set Cel = Cells(ComboBox2.Value, 16 + Weekscherm.ComboBox1.Value)
Cel.Value = _
Me.TextBox1.Text

End If

Me.ComboBox1.ListIndex = IIf(X = 160, 0, X + 1)
Me.ComboBox2.ListIndex = Me.ComboBox1.ListIndex
Me.ComboBox3.ListIndex = Me.ComboBox1.ListIndex

Me.TextBox1.Text = ""
ComboBox1.AutoTab = True
End Sub

-------------------



I someone can help me fix this little bug I will be very please!!

Thanks in Advance,

Stif

 

[Stift]

Can noone help me ?????????? :

 

[Tom Ogilvy]

You can't set the listindex if there is nothing in the list

Private Sub UserForm_Initialize()

Dim Cell As Range
Dim Counter As Long

Dim ListRange As Range
Dim CListRange As Range
Dim ListRangeValue() As Variant
Dim CListRangeValue() As Variant

Set CListRange = _
ActiveSheet.Range("Q9:Q106,Q113:Q162,Q169:Q183")

Set ListRange = _
ActiveSheet.Range("C9:C106,C113:C162,C169:C183")
ListRange.Style.IncludeNumber = True


ReDim ListRangeValue(0 To ListRange.Cells.Count)

For Each Cell In ListRange.Cells


If Cell.Interior.ColorIndex <> 3 Then

Set Celv = Cells(Cell.Row, 16 + Weekscherm.ComboBox1.Value)

If Celv = "" Then

ListRangeValue(Counter) = Cell.Value
Counter = Counter + 1
Me.ComboBox2.AddItem (Cell.Row)
Me.ComboBox3.AddItem Cells(Cell.Row, 2)

End If
End If

Next Cell


Me.ComboBox1.List = ListRangeValue

If combobox1.Listcount > 0 then Me.ComboBox1.ListIndex = 0
If combobox2.Listcount > 0 then Me.ComboBox2.ListIndex = 0
If combobox3.Listcount > 0 then Me.ComboBox3.ListIndex = 0

End Sub

 

[Stift]

Sorry Tom but that's not exaclty what I mean.

I don't no if it's possible.but the check must be in d
Commandbutton.

Something like

Code
-------------------

If Counter=0 Then
MsgBox "All data has been filled"
Unload Me
menu.show
Else

//rest of code

End Sub

 

[Stift]

Anybody who can help me ,please do !!! I really must get this fixxed. :

 

[Stift]

Is there nobody who can help me ????????

 

[Stift]

:

 

[Stift]

I still didn't solve this problem.
The solution that was given works.But it must be fixed from th
commandbutton instead of the userform open