combo box error.

[golyg]

Using Excel XP.
I receive the following error when I open my workbook that already contains values in comboboxes.
A little background...
When the value in the first combobox changes, the list in the second dropdown changes.

error message:

run-time error '380':
Could not set the List property. Invalid propery value.

The first combobox event:

Public Sub cboProdFam1_Change()
        
            selectProdFam = cboProdFam1.Value
            Call determinePFSelection(selectProdFam, Me.cboComp1)

End Sub

Public Sub determinePFSelection(x, ByVal y As ComboBox)

    Select Case x
                Case "AI20"
                    y.List = AI20
                Case "AI-28"
                    y.List = AI28
                Case "EZ-28"
                    y.List = EZ28
                Case "Crystalsert CI - 28"
                    y.List = Crystalsert
                Case "Medicel"
                    y.List = Medicel
                Case "Other"
                    y.List = Blank
                Case Else
                    y.List = Blank
            End Select
End Sub

My variants are defined on the activate method of this worksheet.

Private Sub Worksheet_Activate()
 AI20 = Array("Shuttle holder - 21912", _
                    "Shuttle base - 21900", _
                    "Shuttle lid - 21901", _
                    "Transition cell holder - 21891", _
                    "Transition cell - 21892", _
                    "Transition cell(beveled) - 21892", _
                    "Body - 21893", _
                    "Plunger -21894", _
                    "Spring - 21915", _
                    "Inserter Assembly - 15385")
                    
        AI28 = Array("", "Body (tube) - 21870", _
                "Plunger - 21872", "Transition cell - 21871", _
                "Transition cell(beveled) - 21890", _
                "Insert -21869", "Spring -21915", _
                "O-Ring - 21469", _
                "Inserter Assembly - 15353")
                    
        EZ28 = Array("", "Body - 21855", _
                    "Plunger - 21856", _
                    "Drawer - 21855", _
                    "Spring - 21721", _
                    "Haptic Puller - 21859", _
                    "Inserter Assembly - 15342")
                    
         Crystalsert = Array("", "Body - 21926", _
                            "Plunger - 21856", _
                            "Drawer - 21925", _
                            "Spring - 21721", _
                            "Plunger bearing - 21930", _
                            "Inserter Assembly - 15389")

        Medicel = Array("")
        
        Blank = Array("")
 
        'cboComp1.List = Crystalsert
End Sub

Here is the sequence of events:
user 1 opens sheet, enters data, saves and closes workbook.
User 2 opens sheet, and receives error messsage.

Any ideas?

 

[combo]

Guess that you need to pass combobox as object to cboProdFam1, so either declare it ByRef or just ommit (Public Sub determinePFSelection(x, y As ComboBox)).

combo

 

[golyg]

After reading my previous post, I should have included more information.

When user #2 opens the sheet, receives error, the Arrays (AI20, AI28, etc) are not initalized. So that is what is throwing the error.

My question is why are they not initialized when the sheet is opened and that worksheet is activated as done in workbook_open()

Private Sub Workbook_Open()
Sheet4.Activate
    Sheet4.Visible = xlSheetVeryHidden
    Sheet1.Activate
end sub

 

[combo]

Declare arrays in standard module (as Variant). Initialise them in Workbook_Open event procedure.

combo