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

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