VBA Userform List Box to Populate upon entering a specific text in Column B, copy data upon selection and paste data in the same Row in another Column

[arijitirf]

Hi!
I want to populate a User Form having a listbox upon entering "Board Room" in Column B:B in Sheet 1 (List Box data will be sourced from Sheet 2 A1:A10)

Based on any selection in User Form 1 another User Form List Box will populate (List Box date will be sourced from Sheet 2 B1:B2)

Both the selected result will be copied in the same row in Column G & Column H in Sheet 1 where the specific text is entered in Column B.

Instance:
If I enter Board Room in Row B12 then a User Form 1 having List Box will be populate and based on selection another User Form 2 will populate. Hit the OK button to paste the selected Text from List Box 1 and List Box 2 in Row G12 & H12 respectively.

Help required.

 

[arijitirf]

Hi!
I want to populate a User Form having a listbox upon entering "Board Room" in Column B:B in Sheet 1 (List Box data will be sourced from Sheet 2 A1:A10)

Based on any selection in User Form 1 another User Form List Box will populate (List Box date will be sourced from Sheet 2 B1:B2)

Both the selected result will be copied in the same row in Column G & Column H in Sheet 1 where the specific text is entered in Column B.

Instance:
If I enter Board Room in Row B12 then a User Form 1 having List Box will be populate and based on selection another User Form 2 will populate. Hit the OK button to paste the selected Text from List Box 1 and List Box 2 in Row G12 & H12 respectively.

Help required.

@DanteAmor
@Fluff

Requesting your help Sir.

 

[Edgar_]

You need to specify a lot of behaviors there.

Assuming you have a Sheet1 and a Sheet2 with data in columns A and B.

Create 2 user forms, UserForm1 and UserForm2. Add a ListBox to each and add a button to UserForm2. Make sure they're called Listbox1 in each form and CommandButton1 for the button in UserForm2. Then, in your Sheet1 code, add this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Columns("B")) Is Nothing Then
        If Target.Count = 1 Then
            If Target.Value = "Board Room" Then
                UserForm1.Show
            End If
        End If
    End If
End Sub

For UserForm1, add this code:

Option Explicit

Private Sub ListBox1_Click()
    UserForm2.Show
End Sub

Private Sub UserForm_Activate()
    Dim cl As Range
    For Each cl In Sheet2.Range("A1:A10")
        Me.ListBox1.AddItem cl.Value
    Next cl
End Sub

For UserForm2, add this code:

Option Explicit

Private Sub CommandButton1_Click()
    Sheet1.Range("G" & ActiveCell.Row) = UserForm1.ListBox1.Value
    Sheet1.Range("H" & ActiveCell.Row) = UserForm2.ListBox1.Value
End Sub

Private Sub UserForm_Activate()
    Dim cl As Range
    For Each cl In Sheet2.Range("B1:B2")
        Me.ListBox1.AddItem cl.Value
    Next cl
End Sub

This assumes that Shet1 has a column B where you can enter values, such as "Board Room". You did not specify any condition for the selection in UserForm2, so, after selecting an item in UserForm1, UserForm2 will show up. Don't see any way to attach the file, so you'll have to build it yourself.

 

[arijitirf]

You need to specify a lot of behaviors there.

Assuming you have a Sheet1 and a Sheet2 with data in columns A and B.

Create 2 user forms, UserForm1 and UserForm2. Add a ListBox to each and add a button to UserForm2. Make sure they're called Listbox1 in each form and CommandButton1 for the button in UserForm2. Then, in your Sheet1 code, add this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Columns("B")) Is Nothing Then
        If Target.Count = 1 Then
            If Target.Value = "Board Room" Then
                UserForm1.Show
            End If
        End If
    End If
End Sub

For UserForm1, add this code:

Option Explicit

Private Sub ListBox1_Click()
    UserForm2.Show
End Sub

Private Sub UserForm_Activate()
    Dim cl As Range
    For Each cl In Sheet2.Range("A1:A10")
        Me.ListBox1.AddItem cl.Value
    Next cl
End Sub

For UserForm2, add this code:

Option Explicit

Private Sub CommandButton1_Click()
    Sheet1.Range("G" & ActiveCell.Row) = UserForm1.ListBox1.Value
    Sheet1.Range("H" & ActiveCell.Row) = UserForm2.ListBox1.Value
End Sub

Private Sub UserForm_Activate()
    Dim cl As Range
    For Each cl In Sheet2.Range("B1:B2")
        Me.ListBox1.AddItem cl.Value
    Next cl
End Sub

This assumes that Shet1 has a column B where you can enter values, such as "Board Room". You did not specify any condition for the selection in UserForm2, so, after selecting an item in UserForm1, UserForm2 will show up. Don't see any way to attach the file, so you'll have to build it yourself.

Excellent!

Exactly what I want. Thank you so much @Edgar_