Data Validation (Dynamic) Drop Down WITH COMBO BOX, INDIRECT not working

Hi,
I have been working on a spreadsheet that essentially allows a user to create a quote for clients, it takes data from a database and allows the user to sort it via a 3 tier drop down system. I have decided to overlay a combo box, when double clicked, to allow for auto completion and to stop any visual errors (when zoomed out). To do this I have found some code online (Written Below), I am average at programming but have little experience with VB, from what i can see, when double clicked the ComboBox will assign itself to the clicked cell and set "str = Target.Validation.Formula1", i.e. will set the data validation source formula as the variable str, and then sets the listfillrange as str (i.e. fills the combo box with the correct data). This would work absolutely fine had I only had one dropdown box, but It seems, that because the source formula for the data validation of the second and third tier of the drop down menu uses INDIRECT and is hence dependent on the first, that it doesn't work. I may be completely barking up the wrong tree with everything I've said here, but I'd appreciate if anyone could offer any advice.

I cannot upload my actual document, but have been messing around with a test document that is of similar format, of which I have uploaded, the only difference being that it uses a two tier drop down, rather than three.

Like I said, any advice is appreciated, I'd really like to know how it works and the solution, Thanks

Please Login or Register  to view this content.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Hi,

Change the listfillrange line thus

Please Login or Register  to view this content.

It works in the sample workbook you posted.

Actually, I stand corrected- it does not work. It was actually the data validation dropdown showing through the activex control that I was clicking. I will check further. Ah, of course- it should be

Please Login or Register  to view this content.

Use the SelectionChange event rather than the doubleclick event.

The declaration should look like this

Please Login or Register  to view this content.

Originally Posted by georgedixon

This is the code I have, i need the cancel to remain there though. From what i can work out, defining this variable ensures that the procedure only runs on click of a cell that contains a data validation list

No, the Cancel is there to stop the cell going into edit mode if you double-click it. It should not be necessary if you are not using a doubleclick event and cannot be included in the declaration for a SelectionChange event.

I see what you mean- it appears to only work every second time. Perhaps that is why the original used a double-click. I will see if I can come up with a solution.

It appears to work more reliably to delete the object each time and recreate it

Please Login or Register  to view this content.

You're welcome.

It's because the first one uses a row for the data, not a column. That won't work with a ListFillRange.

Try

Please Login or Register  to view this content.

Try a small change to what I posted previously

Please Login or Register  to view this content.

Great- don't forget to mark the thread Solved please.