Selected Answer
Leopard
Your Worksheet_Change event macro worked to set data validation (to a range) when there was a change in stock data (copied to worksheet BRANDS).
My version uses the Worksheet_SelectionChange event instead- I figure you're only worried about validation when you're in a cell. It determines the data validation needed in a (single) clicked cell (from the STOCK sheet) and shows that list so the user can pick an option.
Here's the code with guiding comments- later you will probably need to change the bits in bold to extend the scope:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim dict As Object
Dim a As Variant, Cll As Range
Dim c As Long
' do nothing if multiple cells or not in range
If Intersect(Target, Range("B13:D27")) Is Nothing Or Target.CountLarge > 1 Then Exit Sub
Set dict = CreateObject("Scripting.Dictionary")
' loop through previous column in stock items, checking adjacent cells match
For Each Cll In Worksheets("STOCK").Range("A2:A7").Offset(0, Target.Column - 1)
If Len(Cll) > 0 And Not dict.exists(Cll.Value) Then
Select Case Target.Column
Case 2
dict.Add Key:=Cll.Value, Item:=1
Case 3
If Cll.Offset(0, -1).Value = Target.Offset(0, -1) _
And Target.Offset(0, -1) <> "" _
Then dict.Add Key:=Cll.Value, Item:=1
Case 4
If Cll.Offset(0, -2).Value = Target.Offset(0, -2) _
And Target.Offset(0, -2) <> "" And _
Cll.Offset(0, -1).Value = Target.Offset(0, -1) _
And Target.Offset(0, -1) <> "" Then
dict.Add Key:=Cll.Value, Item:=1
' B,C and D complete so set maximum for column E as existing stock
With Target.Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateWholeNumber, _
Operator:=xlBetween, Formula1:="0", _
Formula2:=CStr(Cll.Offset(0, 1).Value)
' tell user if QTY > stock
.ErrorMessage = "Current stock is only " & _
Cll.Offset(0, 1).Value
End With
End If
Case Else
MsgBox "Not yet designed for that!"
Exit Sub
End Select
End If
Next Cll
'write validation to target cell and display
With Target.Validation
.Delete
' leave sub if no matches
If dict.Count = 0 Then Exit Sub
.Add Type:=xlValidateList, Formula1:=Join(dict.Keys, ",")
'show the list
Application.SendKeys ("%{Down}")
End With
End Sub
As it leaves me, there is no validation in cells B13:B27 of BRANDS. Click in B13 say, and you get validation options of BS 1200R20, BS 1200R22 and BS 1200R24 (that middle one being made up by me- see green cells on sheet STOCK). Pick one and click in C13 and you'll be offered the associated PATTERNs. Click one and choose D13- that will offer the ORIGIN(s) and -if you pick one- it sets the maximum value allowed in E13 as the current stock level. Enter an excessive value like 800 in E13 and it should cause an error. If you don't set correct values in B, C and D, a level won't be set.
On reflection, it might have been better to write this using .Cells(x,y) but I don't have the time to rewrite it!
Hope this helps. If so, please mark this Answer as Selected.