Thread: Variable Drop Down Dependent List with Multiple Criteria

@kvsrinivasamurthy: I like it, very neat.

I would suggest that you make it a Change event rather than a Selection Change event so it only fires when you change a value. And you might want to limit it to cell B3.

Regards, TMS

Why not just use native formulae with dynamic named ranges?

See the attached

@TMS

I decided to carry on testing your code & spent some time today working on it, which essentially works fine. However, the updating of other cells and the rest of the worksheet did not function as I think you expected. I created a cutdown version of my workbook to test it fully and made the following amendment to the mdropdown module to trigger the changes in the remainder of the sheet with the first selection in E3.

' expose the first entry in the Dependent Drop Down List
With Sheets("Clients2")
    ' set the Dependent Drop Down List box
    Application.EnableEvents = False
    .Range(sDDDL).Value = Range("DV_NUM")(1, 1)
    Application.EnableEvents = True
    ' update Clients2 worksheet for 1st selection
    ActiveSheet.Calculate
End With

and likewise with the worksheet module:

' Sheet3 (Clients2) Sheet Class Module

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$1" _
Then
    ActiveSheet.Range("$E$1").Calculate
    CreateDropDown2
    Exit Sub
End If

If Target.Address = "$E$1" _
Or Target.Address = "$E$3" _
Then
    Debug.Print "Calculating"
    ActiveSheet.Calculate
End If

End Sub

Which appears to make everything update as it should but I am not sure if my changes are technically correct or in the correct place, as in the mDropDown module.

I wanted to make use of your 'debug' for testing, as I may need to adjust the ranges in the Issues sheet in the near future but I could not get it to work. I did notice that for some reason the DebugData macro does not appear in the macro list, even though the 3 'Test' macros are there.

There is one small problem, which I cannot resolve, in that although the number in E3 appears correctly ranged right; the numbers in the dropdown list itself are all ranged left for some reason. The source data for the Data Validation list in the Issues sheet is also correctly ranged right. Any ideas?

Thanks again ...David

@Shoieb.arshad: that particular solution didn't contain any code. It was demonstrating an approach that uses just standard formulae. If you want code examples, look further back in the thread.

However, note ...

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

And you have had an explanation ... but you *are* hijacking another user's thread.

If you want to explore this further, open a new thread and link to this one with reference to specific issues and clarification required.

Regards, TMS

@TMS: Any thoughts on tidying up those bits & pieces in my last post (28/01/12 #37)?

@kvsrinivasamurthy: I have been testing out your code, which essentially works but I have encountered similar problems to the ones that I had in the early stages of this thread, which were mostly solved by TMS in his code.

I have amended the code as per the suggestion by TMS to a Worksheet Change event, which helps with the re-calculation plus I added "ActiveSheet.Calculate" at the end of the code.

The main problem is that when C2 changes, it does not clear E4 which is left over with the number from the previously selected dropdown list. This in turn results in a #N/A error, which is to be expected; however this could be disastrous if more than one customer has the same NUM number in column D (Issues), which could happen.

I tried to experiment by using some of the code from TMS solutions but with my very limited knowledge of VBA, I could not get it to work in any way.

The other cosmetic issues are that the numbers in the dropdown list should be formatted as 000 and if possible ranged right. When the appropriate number is selected from the list, it appears correctly aligned and ranged right in E4; it is just how they appear in the dropdown list. I tried looking up the properties of the VBA validation code "xlValidateList" but nothing seemed to cover these aspects.

I have attached a very cut down version of my project incorporating your code, which may make things clearer. Cell positions and references have been adjusted to suit where applicable.

I know this has turned into quite a long thread and I do appreciate everyones efforts.

David

Pl see attached file.I have changed code and formula in E8,E9 cell.Your views are welcome
with regards
kvsrinivasamurthy

@spellbound: this seems to be dragging on now and suffering from what we used to call "scope creep".

However, the updating of other cells and the rest of the worksheet did not function as I think you expected.

As far as I can see, it works exactly as I expected it to based on what was asked for. You change B1, it creates a drop down list and populates cell E3. You change cell E1 or E3, the worksheet re-calculates. I've put a test formula in place which is simply =E3*2. When I change B1, it creates the drop down list and populates E3 with the first entry and my test formula calculates. I don't need to calculate the sheet so I can only assume that Calculation is set to Manual in your workbook.

I created a cutdown version of my workbook to test it fully and made the following amendment to the mdropdown module to trigger the changes in the remainder of the sheet with the first selection in E3.

It would be helpful if you shared the cut down workbook so that we're all on the same page. You've had several versions of the workbook I have updated for you, plus those from at least two other contributors. The code may work differently depending on the structure and content of the workbook/worksheet(s) ... especially if *your* code switches off calculation, or you have it switched off to improve processing speed.

Which appears to make everything update as it should but I am not sure if my changes are technically correct or in the correct place, as in the mDropDown module.

Difficult to comment without seeing the full picture, workbook, content and code.

I wanted to make use of your 'debug' for testing, as I may need to adjust the ranges in the Issues sheet in the near future but I could not get it to work.

For the debug code to work, it needs the word "Debug" in cell E1 on the Issues sheet. I use this as an external "switch" mechanism.

If Sheets("Issues").Range("E1") <> "Debug" Then Exit Sub

I did notice that for some reason the DebugData macro does not appear in the macro list, even though the 3 'Test' macros are there.

It doesn't appear in the list of macros because it takes parameters.

Sub DebugData(lLR, sDDDL, sWIP, sDVStart, sDVCol, sDVRange, sDVString, sDVSortStart, sDVSortRange)

There is one small problem, which I cannot resolve, in that although the number in E3 appears correctly ranged right; the numbers in the dropdown list itself are all ranged left for some reason. The source data for the Data Validation list in the Issues sheet is also correctly ranged right. Any ideas?

The numbers are Text in order to retain the leading zeroes. By default, text values are aligned left. I don't know of any way to cause the drop down box to right align text values ... though that's not to say it can't be done. The cell, E3, itself is formatted as right aligned, as are the cells in the DV list. So, as it stands, you have a choice, left aligned text values in the drop down box or numeric, right aligned values without leading zeroes in the drop down box.

The main problem is that when C2 changes, it does not clear E4 which is left over with the number from the previously selected dropdown list. This in turn results in a #N/A error, which is to be expected; however this could be disastrous if more than one customer has the same NUM number in column D (Issues), which could happen.

There was nothing in cell C2 in the examples I have been working with. Not sure how or why we would be expected to clear cell E4, which also had nothing in it. If you want to monitor cell C2, you need to adapt the Worksheet Change event to do that. It would only require a block of code to be copied and modified.

I tried to experiment by using some of the code from TMS solutions but with my very limited knowledge of VBA, I could not get it to work in any way.

You see, now I don't know what you are working with ...

Having looked at the workbook you have uploaded, the reason that cell E4 is not changing is because you are using code from kvsrinivasamurthy. That doesn't set the cell to the first value in the DV List. The Drop Down list is extracted in the loop but you don't update the value in the cell. Under the circumstances, I don't really know how appropriate that is ... it would mean that you get an appropriate value in the cell, but not necessarily the one required. Anyway, my code did that because that's what you asked for; kvsrinivasamurthy's doesn't. kvsrinivasamurthy's code also uses SelectionChange rather than Change.

The other cosmetic issues are that the numbers in the dropdown list should be formatted as 000 and if possible ranged right. When the appropriate number is selected from the list, it appears correctly aligned and ranged right in E4; it is just how they appear in the dropdown list. I tried looking up the properties of the VBA validation code "xlValidateList" but nothing seemed to cover these aspects.

Discussed above. But, as you say, it's cosmetic ... it looks fine to me. I think you're going to have to live with it.

I have attached a very cut down version of my project incorporating your code, which may make things clearer. Cell positions and references have been adjusted to suit where applicable.

Why would you "adjust cell positions and references to suit where applicable"? That just means that, when we give you code, you have to work out how to change it to work with the live workbook. That's not good use of anyone's time.

I know this has turned into quite a long thread and I do appreciate everyones efforts.

The original question was answered and resolved long ago.

If you want to test out different approaches, I think you would be better keeping them in isolation. You now have a sample workbook with all my code in it which is, essentially, redundant. kvsrinivasamurthy's code is being used to create the DV list.

My code was designed to be flexible and easy to adapt. It was/is fully commented so that you know what each part of the code does, even if you don't understand how it does it. Where there are alternative approaches, I have made that clear in the comments.

It seems that you have taken two approaches which both appear to do what you wanted, mixed them, mauled them and broken them.


When you ask for help of this nature, you need to:
.. ensure that the sample accurately reflects the live scenario
.. identify all your requirements from the outset
.. fix the requirement(s) at the outset so that you know what success looks like.
.. when you have a solution that works, run with it. You can always enhance and improve later.
.. if it's important to you, respond in a timely fashion
.. study and understand the code and what it does before you try to mix and match.


I enjoyed developing the approach to meet your needs but, as you may have gathered, I am a little frustrated right now. I suggest you take a couple of steps back to where you had a working solution and build on that.


I have provided an updated sample workbook that reflects the fact that the cells have moved down a row. This has required an adjustment to the cell references in the Worksheet Change event and also a slight modification to the code to cope with the input cell being B2 rather than B1.


Regards, TMS