combo box...

[David]

Excuse me, I belive I posted this in the wrong
forum(excel.worksheet.functions) - I am not trying to post same q in
multiple places. thanks group.


Excel 2003
sheet 1 is a "form", sheets 2 - 5 column A1 - A? contain a list of names

I have 2 combo boxes on sheet 1, combobox1 lists all sheets except sheet 1 -
(working)

combobox2 needs to load with a list of names from sheet 2 when I launch the
workbook - (not working)

CODE in "thisworkbook"

**********
Dim i
Dim c As Range

i = 0

For Each c In Worksheets("Sheet2").Range("A2:A500")
If c.Value <> "" Then
i = i + 1 ' add item to combo box here
Else
Exit For
End If
Next

MsgBox "I counted " & i & " names", vbOKOnly, "Count" ' for testing only

**********

I am getting subscript out of range error 9 - what am i not seeing ?

the second part of this is, I need to repopulate combobox 2 when a new sheet
is selected from combobox 1, I know code needs to be in the change event of
combobox 1 to clear combobox 2 and reload it, after that I need help.

Thanks... David

 

[Tom Ogilvy]

Dim c As Range

For Each c In Worksheets("Sheet2").Range("A2:A500")
If c.Value <> "" Then
Worksheets("Sheet1").combobox1.Additem c.Value
else
exit for
End if
Next

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

Private Sub Combobox1_Change()
Dim cell as Range
if me.combobox1.Listindex = -1 then exit sub
with me
Combobox2.clear
for each cell in worksheets( .combobox1.Value).range("A1:A500")
if cell.Value <> "" then
combobox2.AddItem cell.Value
else
exit for
End if
Next
End With
End Sub

Untested

--
Regards,
Tom Ogilvy


"David"

 

[David]

Thanks Tom, the combo boxes work as needed.

Dim c As Range

For Each c In Worksheets("Sheet2").Range("A2:A500")
If c.Value <> "" Then
Worksheets("Sheet1").combobox1.Additem c.Value
else
exit for
End if
Next

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

Private Sub Combobox1_Change()
Dim cell as Range
if me.combobox1.Listindex = -1 then exit sub
with me
Combobox2.clear
for each cell in worksheets( .combobox1.Value).range("A1:A500")
if cell.Value <> "" then
combobox2.AddItem cell.Value
else
exit for
End if
Next
End With
End Sub

Untested

--
Regards,
Tom Ogilvy


"David"