Userforms / Combobox

Ok, I'm building my first userform and have thrown in a combo box. I want to tie the options in the box to a list I have in my Excel sheet. Normally, if I wanted a drop down list I'd do the whole Data/Validation/List thing, but I'm not finding how to tie the two together in the VBA editor. I did a search and dug around in the results, but didn't find how to tie them together. Any helpful pointers in the right direction would be much appreciated!
Thanks,
Chad

Excelent! Thank you Andy!

C

Ok, so I put the code in (obviously using my sheet and cell refs) and when I run the form all I get is a blank box... it'll drop down but nothing is in it.

...and I also tried the other route and put "Sheet08!A198:213" in the comboboxes Row Source slot and got "Invalid Property Value".

Is there an add in or something that I need to have turned on to get this to work or am I missing something?
Much thanks!
C

everytime i have used RowSource for a combobox I have used the following format and it has worked everytime so far.

Sheet08!A198:A213

Sorry, I misstyped... I did have the extra "a" in there! We figured out that my problem is my computer. I had a coworker try on thier computer and the code worked fine and then I tried it on my laptop and it works fine there. Something is amiss on my work desktop!
Thanks!
Chad

Ok, I think I figured out what's going on.. apparently, you can not put the sheet reference in and have it work! I found that I had to make the worksheet that has my list active! This is the same if you're hard coding it in the combobox properties under "RowSource" So I ended up with the following code...

Please Login or Register  to view this content.

...or if you're hard coding it into the combobox properties only put in the cell refs (ie - "A198:A213" without the quotes)

Originally Posted by Andy Pope

That's not the case for me.

Userform uses contents of Sheet08. Works whether sheet is activesheet or not.

Andy,
Which version of Excel are you using? I'm using 2003 SP3.
Chad

Interesting! It will not let me put the sheet name in there no matter what. When I try it gives me "Invalid Property Value" ...and this is whether I'm trying to use the sheet name proper or what I've renamed the tab. Odd.
C

Yes, I've checked and double checked all of that... Ok, I've attached a copy of the sheet where I've removed all of our proprietary math and it's still doing it.

If I use..

Please Login or Register  to view this content.

...I get a message box with, "Run time error '380': Could not set the RowSource property. Invalid property value." I can hit "OK" or "Help". OK ends the run and "Help" gives me a Excel help box with nothing in it.

Now if I run...

Please Login or Register  to view this content.

...it'll run fine, but it's pulling the list from Sheet1 instead of Sheet08.

Maybe it's the zero I have in front of the 8 that's throwing something off?

I opened a new excel sheet and copied your code in to the code editor once I'd made a fresh userform with a new combobox and it worked fine with the first code set.

If you would, take a look when you have a chance and see if I'm doing anything blatantly wrong.
Very much appreciated!
Chad

BINGO! I think I just figured it out!

The Tab name and the Sheet name have to be the same! If they are not, it doesn't work! ...and, it doesn't matter what the name is! Check me out on this.
THX
C

Originally Posted by Andy Pope

You are using the Codename of the sheet rather than the Tab name

Please Login or Register  to view this content.

You are correct! Scratch that last post by me!

..and I wasn't using the single quotes! Ok, I learned something new today. Thank you very much!
Chad

OK, this is a very similar problem to I have and I have tried copying your solutions into my project with no success at all.

What am I doing wrong?

I am using Excel 2007.

I can't help you much since I'm stuck with 2003... sorry! Maybe someone with some 2007 experience can throw down.
C