(Solved) – Defined names not recognized in VBA

(solved)-–-defined-names-not-recognized-in-vba

I have done a test run on using defined names from excel and then using them in VBA to fill ActiveX ComboBoxs as shown below:

Option Explicit

Private Sub Worksheet_Activate()
Dim dogs1 As Object

Dim Sh As Worksheet
'Me.all.Clear

If Choose = "dogs" Then
    With Sheets("dogs")
        all.ListFillRange = "dogs1"
        Debug.Print all
    End With
ElseIf Choose = "Cats" Then
    With Sheets("cats")
        all.ListFillRange = "Cats"
        Debug.Print all
    End With
End If



End Sub

Private Sub Choose_Click()

With Sheets("Index")
Choose.ListFillRange = "animalL"
End With

Worksheets("Print").Select
Worksheets("Interface").Select

End Sub

This one works like a dream, using my defined names from excel. however I am now trying to implement this in my actual program and the defined names are not working. I did have a similar problem when building the animal drop downs but that was only because I didn’t make them workbook (global) names, but I am doing this and now I am at a loss of what is wrong. Here is the code I am working on:

Option Explicit

Private Sub Worksheet_Activate()
Dim Sh As Worksheet

Me.MFG.Clear
For Each Sh In ThisWorkbook.Worksheets
    Me.MFG.AddItem Sh.Name
Next Sh

If MFG.Value = "Kawneer" Then
    With Sheets("Kawneer")
        Disc.ListFillRange = "Frames"
        Debug.Print Frame
    End With
ElseIf MFG.Value = "PRL" Then
    With Sheets("PRL")
        Disc.ListFillRange = "test"
    End With
End If

End Sub

my option explicit is saying my Frames defined name is not defined, but it should be which means test isn’t reading in either, so it’s not a specific problem, but an overall problem with my defined names. All are set to workbook(I tried doing them as separate worksheets too), and when I test it in excel they work just fine (example click on cell press “=” and then type in Frames, it gives me my range). I didn’t add my ComboBox code because it seems to be working fine, just can’t get the defined names to work. Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *