Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

populate adjacent item based on selected from datavalidation

0

Hi  experts 

I have  three datavalidation from row13 in BRANDS sheet  in columns B,C,D when filled items  in J: L then will populate in data validation B:D , but waht I  look for  if  I  select  the  item  from column B  based on column J then  should populate  next adjacent cell in column C based on column K   and when select  item from  column C  then populate item in column D based on column L .

example : if  I select  1200R24 from column B   should just populate  G580  in column C based on  column K  ,but  what  show  me G580, R187 !

and  if I select G580 from column C then  will populate JAP,INDO in column D based on column L but it  shows  JAP,INDO,THI .

I hope  finding  help  for  this  problem .

thanks  in advance 

Answer
Discuss

Discussion

Leopard

Quite annoyed that I just spent about an hour trying to set up (non-macro) dependent data validation lists to the right of your BRANDS sheet then saw my validation formulae were being overwritten. I then realised that you had a Worksheet_Change event macro working silently (and undoing my work!). Next time PLEASE state that in your question and include the code in your question.

I'll see if I have any time tomorrow to look at your problem.
John_Ru (rep: 6102) May 30, '23 at 6:02 pm
Add to Discussion

Answers

0
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.

Discuss

Discussion

Hi John,
sorry for I don't  post  the  main code I  work it  and  need modifying , also  delaying  for  answer you .
honestly  I  haven't  seen before  using  data validation  for this  way  . most  of programmer  use  combobox, but  your   way  will much useful  not  to  me  also  the  others  readers in this  forum especially  it's  rare to  depend on data validation .
the attached  file  works  as  what  I want  it .
thanks for  your  help  .
leopard (rep: 88) Jun 2, '23 at 12:40 pm
Glad that's was what you wanted. I made up that method for your question, using ideas from your code. Thanks for selecting my Answer, Leopard. 
John_Ru (rep: 6102) Jun 2, '23 at 12:52 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login