listbox works in form; not imbedded???

[jasonsweeney]

I have a set set of four list boxs that needs to be populated with
list from one of 36 columns....9 main areas, each with fou
sub-columns...User selects 1-9, and then four list boxes are populate
from the four sub-columns that belongs to that main area....e.g.
select 2, then Excel gets the four sub-columns that belong to item
and drops them into four listboxes.

The user selects 1-9 from a combobox.

I use a series of offsets to tell Excel the location of the column t
populate the listboxes.

The following code works fine when I use a list box in a form.
However, I need the code to work when the list box is embedded i
Sheet1. I get a "1004" error everytime I run the macro, and it say
"method 'Range' of Object_worksheet failed.

What am I doing wrong?

Here is the code:
______________________

Sub worksheet_activate()
mainlist = Sheet4.Range("threads") 'the names of the 9 columns
Sheet1.ComboBox1.List = mainlist
End Sub

Private Sub ComboBox1_Change()
'
Dim clericallist As Variant
Dim mechlist As Variant
Dim proactivelist As Variant
Dim worldclasslist As Variant
'
' Following Section uses the term "base" to refer to the
' Column number of the main area
' Add 1 to the base and you get the items for sub-column 1
' Add 2 to the base and you get the items for sub-column 2, etc.
If Sheet1.ComboBox1.ListIndex = 0 Then base = 1
If Sheet1.ComboBox1.ListIndex = 1 Then base = 6
If Sheet1.ComboBox1.ListIndex = 2 Then base = 11
If Sheet1.ComboBox1.ListIndex = 3 Then base = 16
If Sheet1.ComboBox1.ListIndex = 4 Then base = 21
If Sheet1.ComboBox1.ListIndex = 5 Then base = 26
If Sheet1.ComboBox1.ListIndex = 6 Then base = 31
If Sheet1.ComboBox1.ListIndex = 7 Then base = 36
If Sheet1.ComboBox1.ListIndex = 8 Then base = 41
If Sheet1.ComboBox1.ListIndex = 9 Then base = 46
'
' At the top of each column is a count of the numner of items
' in the list. The next section grabs that count number and
' adds three (because the lists start in row 3) to determine the
' location of the bottom cell in the list....
clericallength = Sheet3.Range("A1").Offset(0, base + 1).Value + 3
'mechanicallength = Sheet3.Range("A1").Offset(0, base + 2).Value + 3
'proactivelength = Sheet3.Range("A1").Offset(0, base + 3).Value + 3
'worldclasslength = Sheet3.Range("A1").Offset(0, base + 4).Value + 3
'
'
' >>>>>>>>>> ERROR EXISTS IN THIS SECTION <<<<<<<<<
' This section defines the range of the list....
' The first "Cells(3,Base+2)" tells Excel the top cell of the list
' the second "Cells (clericallength, base+2)" tells Excel the bottom
' cell in the list
clericallist = Sheet3.Range(Cells(3, base + 2), Cells(clericallength
base + 2))
'mechlist = Sheet3.Range(Cells(3, base + 3), Cells(mechanicallength
base + 3))
'proactivelist = Sheet3.Range(Cells(3, base + 4)
Cells(proactivelength, base + 4))
'worldclasslist = Sheet3.Range(Cells(3, base + 5)
Cells(worldclasslength, base + 5))
'
'
' This section populates the listboxes with the appropriate lists
Sheet1.mainclericalbox.List = clericallist
'Sheet1.mainmechanicalbox.List = mechlist
'Sheet1.mainproactivebox.List = proactivelist
'Sheet1.mainworldclassbox.List = worldclasslist


End Su

 

[Guest]

look in help for: Shapes.ControlFormat Property on how to fill a list box on a worksheet.

 

[jasonsweeney]

Thnaks for the post. Thew problem is not knowing how to fill th
listboxes generally.

The problem is the specific use of "(Cells(x,y), Cells(x,y)" to specif
the range. Above, if I change the offending section to:

clericalist = Sheet1.range("A1:A20")

it fills the embedded list boxes just fine. But I need a dynami
definition for the range based on my above post......

Anybody know a workaround for the use of "Cells(x,y)"??

 

[jasonsweeney]

Its very strange. The problem is definately associated with definin
the range selection by use of "Cell(x,y)". This definately work
inside forms. Yet the same code definately does not work when
listbox is embedded on a worksheet.

Is there a way to use R1:C1 with range selection