Hi:

The following code works as expected, but I'm trying to make the
listbox fill range dynamic with the code between
'///////////
...........
...........
'//////////

It works on form's listbox but not a sheet's listbox.


Private Sub ListBox2_Click()

Dim SourceData As Range
Dim Val1 As String
Dim Val2 As String
'////////////////////////////////////
' Dim myRng As Range

' With Worksheets("sheet4")
' Set myRng = .Range("a1:b" & .Cells(.Rows.Count, "A").End(xlUp).Row)
' End With
' ListBox2.ListFillRange = myRng.Address(external:=True)
' Set SourceRange = Range(ListBox2.ListFillRange)

'/////////////////////////////////////

'replace the following line with above
Set SourceRange = Range(ListBox2.ListFillRange)

Val1 = ListBox2.Value
Val2 = SourceRange.Offset(ListBox2.ListIndex, 1).Resize(1, 1).Value

Label1.Caption = Val1 & " " & Val2

If ActiveCell.Column = 1 Then
ActiveCell.Value = Val1
ActiveCell.Offset(0, 3) = Val2
ActiveCell.Offset(1, 0).Activate

Else
MsgBox "Put the CellPointer in the right column"
End If
End Sub

I would appreciate any help or example.


Thanks
TK