Combobox.clear
 

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!

Chri

(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)

Combobox.clear
 

try a combination of
ListCount and a RemoveItem Loo

With ComboBox
x = .ListCoun
For y = 0 To x -
.RemoveItem (y
Nex
End Wit

----- 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!

Chri


(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)

Combobox.clear
 

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)

Combobox.clear
 

thx for correction Tom

----- Tom Ogilvy wrote: ----

going forward won't work because you pass the listcount on the way up as i
comes down

With ComboBox
x = .ListCoun
For y = x-1 To 0 Step -
.RemoveItem
Nex
End Wit

or if you want to go forward, always remove the first item (item 0

With ComboBox
x = .ListCoun
For y = 0 To x -
.RemoveItem
Nex
End Wit


--
Regards
Tom Ogilv



"Chris Houchin" wrote in messag
..
Thanks for the tip, Chris; however, the function is returning anothe
error. The box say
"Run-time error '-2147467259 (80004005)'
Automation Erro
Unspecified Error
The error occurs on the " .RemoveItem (Y)" line, as if the combobox i
locked from editing (although the worksheet is unprotected). Any othe
suggestions
----- chris wrote: ----
try a combination of
ListCount and a RemoveItem Loo
With ComboBox
x = .ListCoun
For y = 0 To x -
.RemoveItem (y
Nex
End Wit
----- Chris Houchin wrote: ----
I have several comboboxes on a worksheet, in pairs. When Box
is changed, Box2 is populated with the Additem property, depending on box1'
selection. When Box1 is altered from the original selection, the old value
for Box2 need to be cleared but I cannot use Clear method, it's giving a
"Unspecified error." My comboboxes are on a worksheet. Can someone help m
clear the previous options of dropdownlist associated with the combobox?
really appreciate your help!
Chri
(This message is closely related to one posted by Shilps o
4/19 - subject: How to clear the contents of COmboBox?, but he never reall
followed up to help resolve

Combobox.clear
 

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)

Combobox.clear
 

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)