combo box

[David]

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

 

[steve smallman]

David,

It seems to me as if your combo box is full.

What is the value of i when it crashes. I suspect it may
be around 255? I tried a quick trawl through help to see
the max number of entries in a combobox, but couldn't
find it.

may not be the issue, but its what it sounds like to me.

I would put the population code into a sub, and run it
using the selection change event for the combo. I would
use the selection in combo box 1 to determine which range
to populate the cbo2 with, assuming cbo1 contains the
exact sheet names. If not declare a varaiable and use
select to get the correct sheet name, if so modify your
code to read something like:

For Each c In Worksheets(combobox1.value).Range("A2:A500")

assuming the ranges are consistent.

Steve