Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox "dynamic fill range"
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox "dynamic fill range"
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox "dynamic fill range"
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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox "dynamic fill range"
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 "TK" wrote: 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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shortcut to switch from "fill down" to "copy" with mouse drag | Excel Discussion (Misc queries) | |||
More on "Data Range" - Dynamic Range Names | Charts and Charting in Excel | |||
Dynamic Range with Function "AND" | Excel Discussion (Misc queries) | |||
How can I make a range "dynamic"? | Excel Worksheet Functions | |||
Listbox "expand range as list grows" | Excel Programming |