VBA Drop Down Lists

[Spellbound]

Hi all

I have another dilemma which I believe can only be solved using a VBA solution.

In the attached workbook, I need to create a drop down list of names in A2 and a drop down list of ID numbers in B2. The data for the lists is taken from Column A & B respectively and currently is about 3,000 rows.

I need the list to contain only one instance of the name or ID number and to ignore duplicated names/ID numbers, blank rows and rows containing the text and numeric values shown in brown.

As you will see in the example worksheet, a name and ID number may have more than one occurrence but these are not visible as the font colour has been matched to the background colour.

The idea is that when a name or ID number is selected from the drop down list, the user is then taken to the first instance of that name or ID number.

Not sure if I have explained this correctly but I have not been able to find an example of VBA code that will come anywhere near to getting me started.

TIA ...David (spellbound)

[rylo]

Hi

D2:

Please Login or Register  to view this content.

Copy down as required.
Insert, Names, Define, Names, RefersTo=

Please Login or Register  to view this content.

F1: NAMES
F2:

Please Login or Register  to view this content.

copy down as required

A2: Data, Validation, Allow: List, Source: =Names

Right click on the sheet tab, view code and enter

Please Login or Register  to view this content.

HTH

rylo

[MarvinP]

Hi spellbound,

You should read about http://spreadsheets.about.com/od/dat...reate_list.htm
Many of the problems we get could be easily solved if the data was set up correctly.

Excel is a great tool if you structure your data to allow it to work.

[Spellbound]

Hi rylo

Thanks for your efforts. I'm not quite sure if this will work within my worksheet due to the size and complexitiy of it.

However, I want to get it working, so that I can at least try to insert this method in the main workbook.

I have attached the sample workbook which now inludes your formulas and code.

To get it working, I had to put the first formula into D3 as it would not work properly in D2.

Despite copying the formula all the way down to the last entry; the last 'name' does not show up in the list in column F. I added a few more entries to test this out but it was the same result each time.

The other problem is that when a name is selected from the drop down list in A2; it does'nt revert back to the NAMES heading, after making your selection.

Hope you can help iron out these glitches ...David (spellbound)

[Spellbound]

Hi MarvinP

Thanks for your comments. I have read up on many articles relating to Excel, including speed and layout improvements.

However, in this instance, the order of the worksheet has to follow a particular layout; hence the reason for my seeking a VBA solution to the problem.

Ideally, I probably need a second pair of eyes to look at the structure of my workbook, to see if it can be improved but this is unlikely due to the size, complexitiy and confidentiality of the workbook.

Regards ...David (spellbound)

[rylo]

Hi

1) Change the formula in F2 to be

Please Login or Register  to view this content.

2) Macro code

Please Login or Register  to view this content.

rylo

[Spellbound]

Hi Rylo

That seems to work fine now.

I have also created the 2nd Drop Down list in B2 based upon your solution for column A and amended the VBA as best I could but I would be greatful if you would check out the VBA module to make sure that it is correct.

By the way I assume that you did not make use of columns C and E just to make it clearer and that it would not matter if there were no empty columns adjoining those needed to make this work.

Thanks ...David (spellbound)

[rylo]

David

Code look OK. Yep, C and E were just for clarity.

rylo

[Spellbound]

Hi Rylo

Been trying to implement your solutions into my workbook on different worksheets. Whilst doing this I realised that I already had a list if ID numbers stored as a named range - Pvt2_NUM. So far this has negated the need for the helper columns, since it is already sorted and contains no duplicates.

However, as this source may contain ID numbers which are not in use in every work sheet, I would like to add a Message Box to the code; which displays a message such as "ID Not Found" in the event of this happening ...is this possible.

On one of the other worksheets, I already had the following code:

Please Login or Register  to view this content.

and amalgamated it with your code as follows:

Please Login or Register  to view this content.

which appears to work correctly but as always with my limited knowledge of VBA, I would like to make sure that it is correct.

TIA ...David (spellbound)

[rylo]

David

Didn't test this, but I think it should be OK for the message addition.

While the calc part will work here, I'd move it so that it was part of the sub and an action in its own right. I'm not really sure if you want the calculate to occur only if the target cell is B3, or if it is any cell but B3. Why are you trying to force the calc anyway? Don't you have calculation to auto? If you do have it turned off and only want a calc to occur when you have cell B3 then that is the way it should now run.

Please Login or Register  to view this content.

rylo

[Spellbound]

Hi Rylo

I created a list that includes numbers that are not in the worksheet, sorry to say this but the 'MsgBox' does not work, everything else works fine.

I can't have auto calculation turned on because of the time it takes to re-calculate the workbook each time and that particular cell is a drop down list of dates that changes the column of figures below to match the selected date.

Hope you can resolve the 'MsgBox' problem.

Thanks ...David (spellbound)

[snb]

Or

Please Login or Register  to view this content.

cfr.the attachment

[rylo]

David

If the creation of the new validation list from snb doesn't solve things, then set up a new example file that has the named range scenario so we can have a look.

rylo

[Spellbound]

Hi snb

Thanks for your code. Although this did away with the use of helper columns, it did'nt return the column headers to their original state.

As you may have read, I am now using a an existing 'Name' range - Master_ID that negates the need for helper columns.

I am quite sure that it might be possible to combine your code with Rylos code to get to the desired result with a working Msg Box but not sure how to go about it due to my very limited knowledge of VBA.

Thanks again ...David (spellbound)

[Spellbound]

Hi Rylo

Just to clarify the situation, the selection from the drop down list is working fine with numbers that can be found.

However if you pick a number from the list that does not exist in the worksheet
e.g. 286701, then nothing happens (which is correct) but the Msg Box does not come up.

Not sure if this helps but I tried changing the line:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

which brought up the Msg Box for every number; including those numbers that are actually located on the worksheet.

I've been searching for some examples that might help to resolve this (hence the above) but I could'nt find anything that seemed to relate to my problem.

I have attached a revised workbook, hope this helps.

David (spellbound)

[rylo]

David

OK, silly me. Of course it is always going to find it in column E as E3 is in column E!

Please Login or Register  to view this content.

rylo

[Spellbound]

Hi Rylo

Have been incorporating your code into a few different worksheets without any problems; except for a worksheet where the ID number is derived using a formula; which results in the 'not found' msg box coming up for every number.

Any suggestions?

David

[rylo]

David

Use the

Please Login or Register  to view this content.

clause in the find formula structure. This will look at the results of formulas.

rylo