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

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



---
Message posted from http://www.ExcelForum.com/

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).Val ue = Me.Textbox1.Text
End If
'.....so a if for every week of the year'

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

--
Regards,
Tom Ogilvy


"Stift " wrote in message
...
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

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



---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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


--
Regards,
Tom Ogilvy



"Stift " wrote in message
...
No sorry but that is not what I mean.Weeknummers are asked in a other
form.

In language it has to be something like this:

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

Please I need this fixed to finish my trainee-project!


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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

--
Regards,
Tom Ogilvy

"Stift " wrote in message
...
Please somebody help me !!! I really must get this working!


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com