Textbox focus lost

[Stift]

Hi everyone, I've got a exceldatabase where a user can fill in some data
in with a userform containing a combobox,textbox and command button,
When Backgroundcolor of what the content is of the combobox it will not
be displayed in the combobox, but the focus of the textbox won't work
properly then.
See code below.

Thanks for the help!


Code:
--------------------

Private Sub UserForm_Initialize()

Dim Cell As Range
Dim Counter As Long
Dim ListRange As Range
Dim ListRangeValue() As Variant

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

ReDim ListRangeValue(0 To ListRange.Cells.Count - 1)

For Each Cell In ListRange.Cells

If Cell.Interior.ColorIndex <> 3 Then

ListRangeValue(Counter) = Cell.Value
Counter = Counter + 1
End If

Next Cell

Me.ComboBox1.List = ListRangeValue

Me.ComboBox1.ListIndex = 0
End Sub

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



Code:
--------------------

Private Sub CommandButton1_Click()
If Me.TextBox1.Text = "" Then
MsgBox "Vul getal in"
Else
X = Me.ComboBox1.ListIndex
Me.ComboBox1.ListIndex = IIf(X = 160, 0, X + 1)

If Weekscherm.ComboBox1.Value = "Week 1" Then
Set Cel = Cells(9 + ComboBox1.ListIndex - 1, 17)
Cel.Value = _Me.TextBox1.Text
End If
'.....so a if for every week of the year'

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

End Sub

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

 

[Tom Ogilvy]

Assuming Week 1 would be unique in the range C9:C183



Code:
--------------------

Private Sub CommandButton1_Click()
If Me.TextBox1.Text = "" Then
MsgBox "Vul getal in"
Else
X = Me.ComboBox1.ListIndex
Me.ComboBox1.ListIndex = IIf(X = 160, 0, X + 1)

If Weekscherm.ComboBox1.Value = "Week 1" Then
res = Application.Match("Week 1",ActiveSheet.Range("C9:C183",0)
Activesheet.Range("C9:C183")(res).Offset(0,14).Value = Me.Textbox1.Text
End If
'.....so a if for every week of the year'

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

 

[Stift]

No sorry but that is not what I mean.Weeknummers are asked in a othe
form.

In language it has to be something like this:

If the cell where the cell-values are stored in combobox1 has a re
background then
remove the cell from the list (that works now) and say to textbox
that he musn't refer to the next cell but one further.

Please I need this fixed to finish my trainee-project

 

[Tom Ogilvy]

Private Sub CommandButton1_Click()
If Me.TextBox1.Text = "" Then
MsgBox "Vul getal in"
Else
X = Me.ComboBox1.ListIndex
Me.ComboBox1.ListIndex = IIf(X = 160, 0, X + 1)

If Weekscherm.ComboBox1.Value = "Week 1" Then
Set ListRange = _
ActiveSheet.Range("C9:C106,C113:C162,C169:C183")
kk = 0
For Each Cell In ListRange.Cells
If Cell.Interior.ColorIndex <> 3 Then
kk = kk + 1
if kk = Combobox1.ListIndex + 1 then
idex = cell.Row
Exit For
End If
Next Cell
Set Cel = Cells(idex, 17)
Cel.Value = Me.TextBox1.Text
End If
'.....so a if for every week of the year'

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

 

[Stift]

Tom ,thanks for your help so far, It runs but when I fill in text an
press commandbutton error at this line :Next Cell

Next without fo

 

[Stift]

Please somebody help me !!! I really must get this working! :confused

 

[Tom Ogilvy]

There was a missing End if


this compiles OK

Private Sub CommandButton1_Click()
If Me.TextBox1.Text = "" Then
MsgBox "Vul getal in"
Else
X = Me.ComboBox1.ListIndex
Me.ComboBox1.ListIndex = IIf(X = 160, 0, X + 1)

If Weekscherm.ComboBox1.Value = "Week 1" Then
Set ListRange = _
ActiveSheet.Range("C9:C106,C113:C162,C169:C183")
kk = 0
For Each Cell In ListRange.Cells
If Cell.Interior.ColorIndex <> 3 Then
kk = kk + 1
If kk = ComboBox1.ListIndex + 1 Then
idex = Cell.Row
Exit For
End If
End If
Next Cell
Set Cel = Cells(idex, 17)
Cel.Value = Me.TextBox1.Text
End If
'.....so a if for every week of the year'

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

End Sub

 

[Stift]

I've got it fixxed myself. All who replied and try to help me.
Thanks a lot !!

Almost finished my project.

Now I only have to make some code, that only ask for the cells(wher
the text of textbox is stored in) who are empty