Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating listbox
I have a 4 column listbox on a userform. I'm scanning the 2nd column of a
27-column table, something like : for each cell in 2nd column, if condition is met then Cell.Offset(0, -1).Copy Range("AC65536").End(xlUp).Offset(1, 0) Cell.Copy Range("AD65536").End(xlUp).Offset(1, 0) Cell.Offset(0, 8).Copy Range("AE65536").End(xlUp).Offset(1, 0) Cell.Offset(0, 24).Copy Range("AF65536").End(xlUp).Offset(1, 0) i.e copy the column to the left to first free cell in AC, the actual cell to first free cell in AD, etc. (AC1:AF1 are labels). Then I assign AC2:AF(whatever) to the listbox. The obvious point is that the cells I'm mapping to the listbox columns aren't adjacent. I'd prefer to do this without actually writing to the sheet, but any syntax I've tried won't wash, e.g. UserForm1.ListBox1.AddItem ActiveCell.Offset(0,-1) & ActiveCell & ... just concatenates in the listbox first column. All I could find on Google (OzGrid) was "do it the way you're doing it". Any suggestions would be appreciated. TIA, Andy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating listbox
outside loop
UserForm1.ListBox1.Columncount = 4 - start of loop With UserForm1.ListBox1 .AddItem cell.offset(0,-1).Value .List(.listcount-1,1) = cell.Value .List(.listcount-1,2) = cell.Offset(0,8).Value .List(.listcount-1,3) = cell.offset(0,24).Value End With - end of loop -- Regards, Tom Ogilvy "Andy Brown" wrote in message ... I have a 4 column listbox on a userform. I'm scanning the 2nd column of a 27-column table, something like : for each cell in 2nd column, if condition is met then Cell.Offset(0, -1).Copy Range("AC65536").End(xlUp).Offset(1, 0) Cell.Copy Range("AD65536").End(xlUp).Offset(1, 0) Cell.Offset(0, 8).Copy Range("AE65536").End(xlUp).Offset(1, 0) Cell.Offset(0, 24).Copy Range("AF65536").End(xlUp).Offset(1, 0) i.e copy the column to the left to first free cell in AC, the actual cell to first free cell in AD, etc. (AC1:AF1 are labels). Then I assign AC2:AF(whatever) to the listbox. The obvious point is that the cells I'm mapping to the listbox columns aren't adjacent. I'd prefer to do this without actually writing to the sheet, but any syntax I've tried won't wash, e.g. UserForm1.ListBox1.AddItem ActiveCell.Offset(0,-1) & ActiveCell & ... just concatenates in the listbox first column. All I could find on Google (OzGrid) was "do it the way you're doing it". Any suggestions would be appreciated. TIA, Andy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating listbox
Andy
Try the technique discussed here http://www.dicks-blog.com/excel/2004...ting_mult.html -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com Andy Brown wrote: I have a 4 column listbox on a userform. I'm scanning the 2nd column of a 27-column table, something like : for each cell in 2nd column, if condition is met then Cell.Offset(0, -1).Copy Range("AC65536").End(xlUp).Offset(1, 0) Cell.Copy Range("AD65536").End(xlUp).Offset(1, 0) Cell.Offset(0, 8).Copy Range("AE65536").End(xlUp).Offset(1, 0) Cell.Offset(0, 24).Copy Range("AF65536").End(xlUp).Offset(1, 0) i.e copy the column to the left to first free cell in AC, the actual cell to first free cell in AD, etc. (AC1:AF1 are labels). Then I assign AC2:AF(whatever) to the listbox. The obvious point is that the cells I'm mapping to the listbox columns aren't adjacent. I'd prefer to do this without actually writing to the sheet, but any syntax I've tried won't wash, e.g. UserForm1.ListBox1.AddItem ActiveCell.Offset(0,-1) & ActiveCell & ... just concatenates in the listbox first column. All I could find on Google (OzGrid) was "do it the way you're doing it". Any suggestions would be appreciated. TIA, Andy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating listbox
Worked perfectly. TVM, both gents.
Best rgds, Andy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populating listbox | Excel Discussion (Misc queries) | |||
Populating a ListBox | Excel Programming | |||
Populating combobox/listbox | Excel Programming | |||
Populating TextBox Value--using ListBox | Excel Programming | |||
Populating a userform 3 col listbox | Excel Programming |