VBA Code runs on one Computer but not on another-CodePudding

I have written some code to run when the Workbook is opened and it works flawlessly on my personal computer. However after sending the file to my laptop I get the "application-defined or object-defined error" message. I genuinely do not understand why this has happened or how I can solve this.

Private Sub Workbook_Open()

'loads the combobox when book opened

    MenuGenerator.miscComboBox.List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("MenuMiscellaneous").RefersToRange)
    MenuGenerator.soupCombobox.List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("MenuSoups").RefersToRange)
    MenuGenerator.saladComboBox.List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("MenuSalads").RefersToRange)
    MenuGenerator.meatComboBox.List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("MenuMeat").RefersToRange)
    MenuGenerator.fishComboBox.List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("MenuFish").RefersToRange)
    MenuGenerator.starchComboBox.List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("MenuStarch").RefersToRange)
    MenuGenerator.veggieComboBox.List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("MenuVegetable").RefersToRange)
    MenuGenerator.dessertComboBox.List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("MenuDessert").RefersToRange)

End Sub

The code is set to load named ranges I created into the comboboxes.

CodePudding user response：

Add a diagnostic message box to the code.

Option Explicit

Sub Workbook_Open()

    Dim wb As Workbook, ws As Worksheet, i As Integer
    Dim fn, arObj, arMenu, rng As Range, obj
    Dim msg As String
    
    Set fn = Application.WorksheetFunction
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("MenuGenerator")
    
    arObj = Array("misc", "soup", "salad", "meat", "fish", "starch", "veggie", "dessert")
    arMenu = Array("Miscellaneous", "Soups", "Salads", "Meat", _
                   "Fish", "Starch", "Vegetable", "Dessert")
    
    On Error Resume Next
    For i = 0 To UBound(arObj)
        Set rng = Nothing
        Set obj = Nothing
        msg = ""

        Set rng = wb.Names("Menu" & arMenu(i)).RefersToRange
        If rng Is Nothing Then
            msg = msg & vbLf & "Error with name range 'Menu" & arMenu(i) & "'"
        End If
            
        Set obj = ws.OLEObjects(arObj(i) & "ComboBox")
        If obj Is Nothing Then
            msg = msg & vbLf & "Error with '" & arObj(i) & "ComboBox" & "'"
        End If
        
        If msg = "" Then
            obj.Object.List = fn.Transpose(rng)
        Else
            MsgBox msg, vbExclamation
        End If
       
    Next
    On Error GoTo 0
    
End Sub