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.