I hope to get some help. I am trying to create a Listbox that takes the data from Sheet2 Column A.
and populate the ListBox on sheet1 Range("A:A") on selection change.
The purpose to creating the listbox is to enter the names on Sheet1 Column A cells through Listbox.
Your help will be appreciated.
- Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- If Not Intersect(Target, Range("A:A")) Is Nothing Then
- Dim ws As Worksheet
- Dim rng As Range
- Dim MyArray
- Set ws = Sheets("Sheet2")
- Set rng = ws.Range("A1:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
- With Me.ListBox1
- .ColumnHeads = False
- .ColumnCount = rng.Columns.Count
- MyArray = rng
- .List = MyArray
- End With
- End If
- End Sub