0

I can't figure out why this line is throwing the "Error 1004: Application Defined or Object Defined error"? Can someone help?

The object "CombQTY" is a combo box in a userform "MASTER". I am trying to populate the drop down menu of this combo box with the range "QTY_range" (size is 31 rows of numbers in a column = 0,1,2,3,..30).

I have tried swapping the "Userform" with "MASTER" and vice versa.

Private Sub Userform_Initialize() MASTER.CombQTY.List = Worksheets("RANGES").Range("QTY_range").Value End Sub

I can't understand why its throwing that error because I don't see any problem with this code. Please help

Improve this question
4
  • Please don't SHOUT when asking your question. Text in ALL CAPS is difficult to read and understand, and it's somewhat rude to SHOUT for attention. Thanks. Commented Apr 24, 2019 at 18:28
  • 2
    Is the named range definitely on that sheet? Your code works for me. Commented Apr 24, 2019 at 18:35
  • 2
    What are the dimensions of QTY_Range ? Commented Apr 24, 2019 at 18:54
  • Never refer to a form's default instance in that form's code-behind. If CombQTY is a control on that userform, the only correct qualifier to use is Me. Otherwise you are assigning the List property of the CombQTY combobox on the default instance of the form, ....which may or may not be that instance. Unqualified Worksheets is implicitly referring to whatever the ActiveWorkbook is; a proper Workbook object qualifier would be needed here. Commented Apr 24, 2019 at 19:02

1 Answer 1

Reset to default
2

Not directly answering your question, but relative to what you're doing (we don't see what QTE_range address is).

I tend to loop and collect a list, so i don't have to worry about the dimensions of my named range (more columns than rows).. .example code (untested):

dim i as range, a as range, arr as variant set a = thisworkbook.names("QTE_range").referstorange for each i in a if arr(ubound(arr)) <> "" then redim preserve arr(ubound(arr)+1) arr(ubound(arr)) = i.value next Me.CombQTY.List = arr

Note the references... userform is Me and workbook is thisworkbook, which could be antoher ref, or to a sheet name, etc.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Postscript: i also changed how to reference your named range, whcih doesn't require the sheet name (in case you have it wrong)... Names().RefersToRange
Upvoted - only needs some error handling to avoid blowing up with a type mismatch when arr(ubound(arr)) contains a worksheet error and that error value is being compared to "", but that wasn't in scope.
@MathieuGuindon Very true. I think the only truly in scope thing I could add would be the single line code that reads: Me.CombQTY.List = ThisWorkbook.Names("QTE_range").RefersToRange.Value

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.