List of items in a userform listbox, object required error in code

[jayherring86]

Hi,

I'm attempting modify some code from one userform to a new one, using different cell references from the original macro. I'm working at a level where I am beginning to understand VBA functions but I admit that this code is just a little bit beyond my understanding (it was previously given to me in this forum to help with the first listbox I put together).

I've changed the code as best I can and am getting a "424 Object Required" error, where the ## is in the code below.

I'm happy to give more detail as to the thing i'm trying to do but briefly, the user will change a combobox called CB_Name, then the code runs which will look for all the times that name is mentioned in COL P (of another sheet, not the activesheet), then list all of the Items in col B rows that contain that name. The current code below does this by using the "find" feature that excel has and using an offset of -14 columns after the initial "find all of that name" part - I think i've changed the cell references correctly but i can't think of why a previously working code has this error now.

Please Login or Register  to view this content.

[jayherring86]

I solved this problem myself, but don't know how to delete the thread.

As it turns out, the solution was simple. I reference a "CB_Name" in the code, which is a ComboBox that is to contain the name I want to search for. When the userform initialises, the CB_Name box is loaded with the word "name" which is taken from cell P2. the above code starts looking from cell P3 so it can't highlight any cells - causing the object error.

To fix the code, all i had to do was change the "Range("p3:p"... " to " Range("p2:p" ... " and the code runs fine.

If an admin can delete this thread then that would be fine.

[LJMetzger]

Hi Jay,

VBA thinks your variable is type 'Variant' which can masquerade as any type, but apparently not as an uninitialized range as you have discovered. You need to let VBA know the TYPE for each variable you are using. You accomplish this in two ways:
a. Use 'Option Explicit' at the top of each module to FORCE variable type declation.
b. Use a dim statement (or equivalent) to let Excel know the type of each variable

Try running the following three procedures in it's own code module. 'Option Explicit' must be at the top of the module. Run it as is with 'Option Explicit' commented out. Then run the procedures with 'Option Explicit' activated.

Please Login or Register  to view this content.

To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. dim i as Integer). http://www.cpearson.com/excel/DeclaringVariables.aspx

The following is a pretty good tutorial on Data Types:
http://www.functionx.com/vbaexcel/Lesson03.htm

The following is the best tutorial I've seen on ranges:
http://www.globaliconnect.com/excel/...=79&Itemid=475

--------------------

Please feel free to continue this thread if my response does not answer your question. If your question is answered, please mark the thread as solved. If you have additional questions about the same code, don't use this thread, but start a new one.

I hope I've helped.

Lewis

[jayherring86]

Thank you so much for showing me this, I feel like it's put me on to something i can really learn from.