Cascading drop down lists getting hung up..

[RS.Static]

I've basically been cleaning up a system that someone else built this past week (on top of 4-5 months of missing information, clerical errors, typos, etc). I can not find any answers for this issue (maybe I'm just asking the wrong questions..)
So there are 3 cascading drop downs from a separate worksheet database just split into 3 columns with a main category, sub, then the last option (not sure how many ways there are to build the cascading lists).
What is happening is the lists seem to be getting hung up. (so no matter what I choose on any row/column that have the lists) The last 2 lists stay the same.I've tried deleting all info in the whole row, clearing the form (the macro clears it) and the only way I can figure to solve the problem is clear the form, exit excel and restart excel (saving even after it gets stuck is no issue).
It seems to be something to do with picking the first 2 categories and not choosing the 3rd option, but every time I think I have a specific way of making it happen repeatedly, there are situations where it works.
Also there are 3 different forms that use the same lists, and it will get stuck on all 3 worksheets.

Any ideas, thoughts, help or even just pointing me in the right direction to search for a solution would be greatly appreciated.

[skywriter]

It's really hard to say without seeing the workbook, data, and the code.

ViewPic

[RS.Static]

As far as I can tell is he used a macro for the lists. I think this is the code. (edited to add a bit more. there's definitely a bit more to it than I had). feels like something is missing


Set grouprange = ThisWorkbook.Sheets("PO").Range("A21:C39")
Set typerange = ThisWorkbook.Sheets("PO").Range("D21:F39")
Set descrange = ThisWorkbook.Sheets("PO").Range("G21:L39")
Set dropdownrng = ThisWorkbook.Sheets("PO").Range("D10:H10")


If Not Application.Intersect(ActiveCell, dropdownrng.Cells) Is Nothing Then
Dim a, b, c, d As String
crow = ActiveCell.Row
groupval = ActiveCell.Value
If groupval = "" Then Exit Sub
Application.EnableEvents = False
ThisWorkbook.Sheets("PO").Range("D11:E11").FormulaR1C1 = "=IFERROR(VLOOKUP(R[-1]C,Supplier!C[-2]:C[2],2,0),"""")"
ThisWorkbook.Sheets("PO").Range("G11:H11").FormulaR1C1 = "=IFERROR(VLOOKUP(R[-1]C[-3],Supplier!C[-5]:C[-1],3,0),"""")"
ThisWorkbook.Sheets("PO").Range("D12:H12").FormulaR1C1 = "=IFERROR(VLOOKUP(R[-2]C,Supplier!C[-2]:C[2],4,0),"""")"
ThisWorkbook.Sheets("PO").Range("D13:H13").FormulaR1C1 = "=IFERROR(VLOOKUP(R[-3]C,Supplier!C[-2]:C[2],5,0),"""")"

ThisWorkbook.Sheets("PO").Range("D11:E11").Value = ThisWorkbook.Sheets("PO").Range("D11:E11").Value
ThisWorkbook.Sheets("PO").Range("G11:H11").Value = ThisWorkbook.Sheets("PO").Range("G11:H11").Value
ThisWorkbook.Sheets("PO").Range("D12:H12").Value = ThisWorkbook.Sheets("PO").Range("D12:H12").Value
ThisWorkbook.Sheets("PO").Range("D13:H13").Value = ThisWorkbook.Sheets("PO").Range("D13:H13").Value
Application.EnableEvents = True
Exit Sub
End If


If Not Application.Intersect(ActiveCell, grouprange.Cells) Is Nothing Then
crow = ActiveCell.Row
groupval = ActiveCell.Value
If groupval = "" Then Exit Sub
Call ChangeType(groupval)
End If

If Not Application.Intersect(ActiveCell, typerange.Cells) Is Nothing Then
crow = ActiveCell.Row
groupval = Cells(ActiveCell.Row, 1).Value
typeval = ActiveCell.Value

If typeval = "" Then Exit Sub
Call ChangeDescGroup(groupval)
Call ChangeDescType(typeval)
End If

If Not Application.Intersect(ActiveCell, descrange.Cells) Is Nothing Then
Application.EnableEvents = False
crow = ActiveCell.Row
groupval = ActiveCell.Value
If groupval = "" Then Exit Sub
Range("O" & crow).Value = Range("S" & crow).Value
Application.EnableEvents = True
Exit Sub
End If


Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub



I had to delete a bunch of info to make the file fit. Hopefully the issue is still there12222015 Inventory Control Management V12-up.xlsm

Any other info needed to help find the solution let me know.

[skywriter]

You have a lot of sheets here.

Can you point me to where you are having a problem and give me a detailed explanation of what I need to do to recreate the problem.

Tell me what specifically I should see that's the problem.

[RS.Static]

Basically in the PO, Receiving, or pick_ticket forms. under group/type/item description. the drop downs get stuck. like after picking from 2-3 of them & backtracking or pick the first 2 then misclick on a drop down lower in the list.
Then no matter what I pick in group, type or item description do not change. I
I'm not 100% on how to make it happen every time & appreciate your patience.. its really easy for me to see when it messes up because I know all the materials and such pretty well.

sorry having trouble with the pics. hope this works

this one is right
[ATTACH=CONFIG]http://s28.postimg.org/ooea82tkt/Right.png[/ATTACH]

this one is wrong
[ATTACH=CONFIG]http://s24.postimg.org/o7uhv7hn9/Wrong.png[/ATTACH]

[skywriter]

Okay maybe if I explain what you have here you can work with it.

On your sheet "Report_Backup" you have 3 pivot tables. These pivot tables are the source for your drop down lists, all of them at the same time. Whatever you see in the second and third pivot table is what you will see in your type name and item description columns for any drop down you select.

What happens is if you change one of the group names the worksheet change event is triggered and the pivot table is filtered according to the group name you changed. The type name is filtered according to the group name you just changed, then if you change the type name the description name is filtered based on the type name you just changed.

Each time you change any group name cell, let's say A23 then all the type name choices for all the other cells are now based upon the type name you just changed. If you change one of the type names then all the item descriptions are based on the type name you changed.

The issue here is you have a lot of drop downs and they all have the same data source.

So if you change any group name and check the type name for all the other cells you will see the type name is based on the group name you just changed.

The bottom line is if you want to see different items in a particular row, change the group name for that row and the type name will be based on the group name, then change the type name and the description will be based on the group name and type name.

[RS.Static]

Just got back from Christmas break. I appreciate the help so far, but am still pretty uncertain of how to fix my issue.
Basically what happens is the sub groups get stuck - so I'll have all options under group, but no matter what I choose under the first list, the second "type" will not give me another list, as well the third group "description" will not change from the type listing it gets stuck on.
The most consistent way I've been able to make it happen (definitely not the only way - believe me if it was I wouldn't be very worried about it) is by choosing a group/type/description in row 21, nothing else, then hitting clear form. It gets stuck in every row, on whatever lists were chosen before I cleared. It seems to be fine if I choose an option or fill something out in the 4th column (titled unit) before hitting clear form.
Hopefully that's enough information to give you a better idea of my issue.