Chris
You're likely getting that error because you have something in the
ListFillRange property of the combobox. You can populate a combobox with
ListFillRange or AddItem, but not both. If you use ListFillRange, AddItem,
RemoveItem, and Clear will give you that error (or similar). If the
combobox is on a Userform (as opposed to a Worksheet), the property is
called RowSource.
--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com
"Tom Ogilvy" wrote in message
...
I ran your Combobox3_change
event in a userform and it didn't have a problem. I suspect it is a
context
problem - the situation when the code is run causes the problem. Based a
"light" review of all the code you provided, I really can't diagnose where
the problem might be.
--
Regards,
Tom Ogilvy
"Chris Houchin" wrote in message
...
Thanks for the reply - however, I'm still getting the same "Unspecified
Error." It always occurs on the .RemoveItem line. The weird part is that
I
have two identical sets of comboboxes and the first set works as desired.
I
included my code below - combobox1 changes combobox2 and box3 changes
box4.
The code appears identical to me (I included my previous attempt,
commented
out), but an error occurs at the ".RemoveItem" line still. I would
appreciate any other ideas! I am pretty new to this stuff and can't get
this attempt off the ground!!
Thanks - chris
Private Sub ComboBox1_change()
Dim rng As Range
Dim acell As Range
Me.ComboBox2 = Null
Me.ComboBox2.Clear
Set rng = Sheets("WorkPackage").Range("B20:B29")
For Each acell In rng
If acell.Value < "" Then
Sheets("Timesheet").ComboBox2.AddItem acell.Value
Else
Exit For
End If
Next
ComboBox2.ListIndex = 0
End Sub
Private Sub ComboBox2_click()
Dim HourRange2 As Range
Set HourRange2 = Range("F10:U10")
If ComboBox2.ListIndex < 0 Then _
Call Allowhours(HourRange2, ComboBox2.ListIndex, "combo2")
End Sub
Private Sub ComboBox3_Change()
Dim rng3 As Range
Dim acell3 As Range
' Me.ComboBox4 = Null
' Me.ComboBox4.Clear
With ComboBox4
X = .ListCount
For Y = 0 To X - 1
.RemoveItem 0
Next
End With
Set rng3 = Sheets("WorkPackage").Range("C20:C29")
For Each acell3 In rng
If acell3.Value < 0 Then
Sheets("Timesheet").ComboBox2.AddItem acell3.Value
Else
Exit For
End If
Next
ComboBox4.ListIndex = 0
End Sub
Private Sub ComboBox4_click()
Dim HourRange4 As Range
Set HourRange4 = Range("F11:U11")
If ComboBox4.ListIndex < 0 Then _
Call Allowhours(HourRange4, ComboBox4.ListIndex, "combo4")
End Sub
----- Tom Ogilvy wrote: -----
going forward won't work because you pass the listcount on the way
up
as it
comes down.
With ComboBox1
x = .ListCount
For y = x-1 To 0 Step - 1
.RemoveItem y
Next
End With
or if you want to go forward, always remove the first item (item 0)
With ComboBox1
x = .ListCount
For y = 0 To x - 1
.RemoveItem 0
Next
End With
--
Regards,
Tom Ogilvy
"Chris Houchin" wrote in
message
...
Thanks for the tip, Chris; however, the function is returning
another
error. The box says
"Run-time error '-2147467259 (80004005)':
Automation Error
Unspecified Error"
The error occurs on the " .RemoveItem (Y)" line, as if the
combobox
is
locked from editing (although the worksheet is unprotected). Any
other
suggestions?
----- chris wrote: -----
try a combination of:
ListCount and a RemoveItem Loop
With ComboBox1
x = .ListCount
For y = 0 To x - 1
.RemoveItem (y)
Next
End With
----- Chris Houchin wrote: -----
I have several comboboxes on a worksheet, in pairs.
When Box1
is changed, Box2 is populated with the Additem property, depending
on
box1's
selection. When Box1 is altered from the original selection, the
old
values
for Box2 need to be cleared but I cannot use Clear method, it's
giving an
"Unspecified error." My comboboxes are on a worksheet. Can
someone
help me
clear the previous options of dropdownlist associated with the
combobox? I
really appreciate your help!!
Chris
(This message is closely related to one posted by
Shilps on
4/19 - subject: How to clear the contents of COmboBox?, but he
never
really
followed up to help resolve)