Autopopulate ComboBox Value based on the value of TextBox

[deba2020]

I have a macro enabled excel workbook named Consolidation.xlsm.
1st sheet contains details of Material Received, Storage Location, GRN and Quantity, Name of the sheet : Sheet1
Data in the sheet1 looks like this

In sheet 2 data appears like this

Requirement:
Upon clicking on corresponding cell (Storage Location) a userform will pop up like this. which will automatically grab the RM Code in Textbox

Now in Combobox ("Location") we need list of those locations where D170 were stored as per the details available in Sheet1
and in GRN field we need list of those GRN of D170 based on locations selected in Combobox.

Please help.

 

[Fluff]

What code do you currently have for the userform?

 

[deba2020]

What code do you currently have for the userform?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Row < 5 Then Exit Sub
If Not Intersect(Range("d:d"), Target) Is Nothing And Target.Count = 1 Then
UserForm2.Left = Target.Left + 25
UserForm2.Top = Target.Top + 30 - Cells(ActiveWindow.ScrollRow, 1).Top
UserForm2.TextBox1.value = Target.Offset(0, -2)
UserForm2.Show
End If
End Sub

 

[Fluff]

Do you currently have any code in the userform itself?

 

[JEC]

I prefer a ComboBox over a Textbox in your case, but you could study this.

Private Sub ComboBox1_DropButtonClick()
  ar = Sheets(1).Cells(1, 1).CurrentRegion
  If IsNumeric(Application.Match(TextBox1.Value, Application.Index(ar, 0, 1), 0)) Then
      For j = 1 To UBound(ar)
          If UCase(ar(j, 1)) = UCase(TextBox1.Value) Then c00 = c00 & "|" & ar(j, 2)
      Next
      ComboBox1.List = Application.Transpose(Split(Mid(c00, 2), "|"))
  End If
End Sub

 

[JEC]

Or without loop

Private Sub ComboBox1_DropButtonClick()
  Sheets(1).Range("A2", Sheets(1).Cells(Rows.Count, 1).End(xlUp)).Name = "a"
  ar = Sheets(1).Cells(1, 1).CurrentRegion
  ComboBox1.List = Application.Index(ar, Filter(Evaluate("transpose(Iferror(Find(""" & UCase(TextBox1.Text) & """, a) * Row(a), ""~""))"), "~", False), 2)
End Sub

 

[deba2020]

There is no code in the userform, is there anyway to upload the file?

Do you currently have any code in the userform itself?

 

[deba2020]

I prefer a ComboBox over a Textbox in your case, but you could study this.

Private Sub ComboBox1_DropButtonClick()
  ar = Sheets(1).Cells(1, 1).CurrentRegion
  If IsNumeric(Application.Match(TextBox1.Value, Application.Index(ar, 0, 1), 0)) Then
      For j = 1 To UBound(ar)
          If UCase(ar(j, 1)) = UCase(TextBox1.Value) Then c00 = c00 & "|" & ar(j, 2)
      Next
      ComboBox1.List = Application.Transpose(Split(Mid(c00, 2), "|"))
  End If
End Sub

Thanks JEC, your code worked but is there anyway to get unique list of locations in combobox also request you to help me with the combobox for GRN.

 

[JEC]

For unique (For GRN you can use the same method):

Private Sub ComboBox1_DropButtonClick()
    ar = Sheets(1).Cells(1, 1).CurrentRegion
     If IsNumeric(Application.Match(TextBox1.Value, Application.Index(ar, 0, 1), 0)) Then
        For j = 1 To UBound(ar)
            If UCase(ar(j, 1)) = UCase(TextBox1.Value) And InStr(c00, ar(j, 2)) = 0 Then c00 = c00 & "|" & ar(j, 2)
        Next
        ComboBox1.List = Application.Transpose(Split(Mid(c00, 2), "|"))
     End If
End Sub

or

Private Sub ComboBox1_DropButtonClick()
  ar = Sheets(1).Cells(1, 1).CurrentRegion
  With CreateObject("scripting.dictionary")
     If IsNumeric(Application.Match(TextBox1.Value, Application.Index(ar, 0, 1), 0)) Then
        For j = 1 To UBound(ar)
            If UCase(ar(j, 1)) = UCase(TextBox1.Value) Then c00 = .Item(ar(j, 2))
        Next
        ComboBox1.List = Application.Transpose(.keys)
     End If
  End With
End Sub

 

[deba2020]

For unique (For GRN you can use the same method):

Private Sub ComboBox1_DropButtonClick()
    ar = Sheets(1).Cells(1, 1).CurrentRegion
     If IsNumeric(Application.Match(TextBox1.Value, Application.Index(ar, 0, 1), 0)) Then
        For j = 1 To UBound(ar)
            If UCase(ar(j, 1)) = UCase(TextBox1.Value) And InStr(c00, ar(j, 2)) = 0 Then c00 = c00 & "|" & ar(j, 2)
        Next
        ComboBox1.List = Application.Transpose(Split(Mid(c00, 2), "|"))
     End If
End Sub

or

Private Sub ComboBox1_DropButtonClick()
  ar = Sheets(1).Cells(1, 1).CurrentRegion
  With CreateObject("scripting.dictionary")
     If IsNumeric(Application.Match(TextBox1.Value, Application.Index(ar, 0, 1), 0)) Then
        For j = 1 To UBound(ar)
            If UCase(ar(j, 1)) = UCase(TextBox1.Value) Then c00 = .Item(ar(j, 2))
        Next
        ComboBox1.List = Application.Transpose(.keys)
     End If
  End With
End Sub

Thanks Friend, it was indeed a great help. It worked like wonder.