ListBox "dynamic fill range"

[TK]

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

 

[sebastienm]

Hi TK,
Are you speaking of the listbox location? ie the listbox being on a Userform
vs the listbox being on a sheet?
The properties of the listbox are not the same whether the listbox is on a
UserForm or on a Sheet.
Userform Sheet
Source Data ListFillRange RowSource
Destination LinkedCell Control Source

Regards,
Sebastien

 

[TK]

Hi: Sebastien

Thanks for the reply. In the code I presented I'm attempting
to populate a listBox (the control being on the sheet(sheet4)).
I would like the ListFillrange to grow as users add items to the list.

Didn't you reverse the properties for the two controls?

Thanks
TK

 

[sebastienm]

TK,
You're right, i have switched the Userform and Sheet properties (i think.
Can't check right now as i don't have excel accessible.)

To link controls to ranges, i usually use Dynamic Named Ranges.
-menu Insert > Name > Define.
As a name, enter: Data
For RefersTo, enter: =OFFSET(Sheet4!$A$1,0,0,COUNTA(Sheet4!$A:$A),1)
which means the range starting in sheet4!A1 and having x rows, x being he
number of non-empty cells in column A. Also, it is dynamic, meaning that if
you add a value in columnm A, the named range is automatically updated to its
new reference.
-In the Vbe, set the RowSource property to Data (the named range)
Now when displaying the form, the listbox automatically displays the
content of the range. No more code to handle that part.
-One thing to take care of. If the user add an item, the listbox may not
show the update immediately. Assuming the user adds value through the
userform and click a button to Add the value to the range. Say this button is
Cmd, then at the end of the Cmd_Click sub, add the line:
Listbox1.RowSource=Listbox1.RowSource
This line forces the listbox to update to the new reference of Data.

I hope this helps,
Sebastien