Validation List - VB Reference

[MAdcock]

Hi,

I am struggling with most probably a straight forward problem, but here goes. I want to write some VB coding to match cell values to a list I have already made "Stock" so that only rows with the values as defined in my list remain. My problem is being able to reference the list "Stock" in VB - is it possible to do so rather than retyping the list seperated by commas?

Please could someone help me! Many thanks

Mark

[Killer42]

I am struggling with most probably a straight forward problem, but here goes. I want to write some VB coding to match cell values to a list I have already made "Stock" so that only rows with the values as defined in my list remain. My problem is being able to reference the list "Stock" in VB - is it possible to do so rather than retyping the list seperated by commas?

Is this in Excel?

[MAdcock]

Yes, sorry I forgot to mention it is for an excel spreadsheet.

Help is much appreciated!

[MAdcock]

Excel list defined via:

Insert - Name - Define

Just to make sure :)

[Killer42]

Excel list defined via:

Insert - Name - Define

Just to make sure :)

Sorry, I haven't found time to look into this one (and nobody else seems to be hopping onboard, which is a little surprising. However, one tip I often give people trying to write macro code in Excel is this. Start recording a new macro, do the function yourself (if possible), then stop recording. You can then edit the macro, and the code has been written for you.

This will not necessarily produce exactly what you need, because for one thing, it's often specific to the particular location at which (or the data on which) you ran it. But it is a great way to get started and learn a lot about how to code things.

[MAdcock]

Thanks for that, I do that already and yes the problem is that the macro then references the cells rather than the "LIST" I am after.

If anyone knows the answer - PLEASE PLEASE HELP

[Killer42]

Thanks for that, I do that already and yes the problem is that the macro then references the cells rather than the "LIST" I am after.

If anyone knows the answer - PLEASE PLEASE HELP

Hm... I'm not that familiar with lists in Excel. I played around and can get my code to refer to a range of cells by the name assigned to it, if that's any help.

Range("AABBCC").BorderAround xlContinuous, xlMedium

The online help mentions that the .Names property can be used to find the names available, too.

[Killer42]

Here are a couple of links I found which may provide some sort of info. It's hard to tell whether they will help or not, because a lot of the stuff I searched refers to "lists" in the general sense, basically meaning any bunch of cells, rtaher than what Excel specifically refers to as a list. Anyway, here they are...

• Adding new data records to an Excel list
• Working With Lists (This one doesn't relate to VBA, but may help generate some ideas. Any of the functions which reference a List, you should also be able to invoke from VBA.)