Update Linked ComboBox List Based On Change In Another ComboBox Selection

[paul_pearson]

The code is for a userform.I need some help with adding code.In the userform if Internal (combobox3) is selected then I want Combobox`s 5 & 4 & 6 to have a certain list to choose from.The lists would be for Part,Part No. and Fault for Internal.If In the userform External (combobox3) is selected then Combobox`s 5 & 4 & 6 will have a different list to choose from.The lists would be for Part,Part No. and Fault for External..I would prefer this to be hard coded but if it needs to be called from a sheet then that will also do

The lists are a example in sheet called Sample...

At present the combobox 5 & 4 & 6 just have zero`s as entries for now until I work out how to code

Thanks

[Excel Fox]

You've mentioned the same part, part.no and fault. But I am assuming those would be unique parts, part.nos and faults.

So the code can be


Private Sub ComboBox3_Change()

Dim lngCol As Long
With Worksheets("sample")
lngCol = .Rows(1).Find(What:=Me.ComboBox3.Text, lookAt:=xlWhole).Column
Me.ComboBox4.List = .Range("A3:A" & .Cells(.Rows.Count, lngCol).End(xlUp).Row).Offset(, lngCol - 1).Value2
Me.ComboBox5.List = .Range("A3:A" & .Cells(.Rows.Count, lngCol).End(xlUp).Row).Offset(, lngCol).Value2
Me.ComboBox6.List = .Range("A3:A" & .Cells(.Rows.Count, lngCol).End(xlUp).Row).Offset(, lngCol + 1).Value2
End With

End Sub

[paul_pearson]

Thanks Excel Fox
Works very good.
Would you see any issues with the way the 2 codes are placed in the userform?
Some code is hard coded and some code is referring to a sheet.
Thanks
Paul

[Excel Fox]

Issues? Can't say until you specify any reason why you think the hard coding might be an issue. Are you going to frequently change the sheet names? Are you going to move the header rows up or down to another row? If yes, then yes the hard coding might be an issue.

[paul_pearson]

The only change I made was to add an extra row to the top of the sample sheet and alter the code slightly...it works and I think everything else will remain the same
Thanks
Paul

[Excel Fox]

So why are you attaching the worksheet again? If you've got it working, do you have any further questions?

[paul_pearson]

Just making sure I have done the changes correctly...otherwise no more questions

[Excel Fox]

OK, I'm sure you have replicated the solution provided, and I assume you don't have more questions.

[paul_pearson]

I actually do have a question please
Can the userform only show what is in all the combobox`s lists once entries have been entered in order.So for any lists to be visible the date must be entered first followed by Machine followed by Category etc..etc..so if someone tries to enter in the Category before entering a date first a message pops up saying enter a date ...lists are only visible if entries are made in order from the top to the bottom.When a date is entered first then the Machine list becomes visible etc;;etc;; and so on down the userform
Thanks
Paul