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.
Code
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
.Clear
.ColumnHeads = False
.ColumnCount = rng.Columns.Count
MyArray = rng
.List = MyArray
End With
End If
End Sub
Display More