empty row with listbox values

[Doflamingo]

Hi all,

I have a code that allows me to put in the column B of an Excel sheet all the items displayed in a listbox

Here is the code

Sheet9.Activate
For b = 0 To ListBox1.ListCount - 1
With Cells(b + 3, 2)
        .ColumnWidth = 15
        .Value = ListBox1.List(b)
End With
Next

But If the items of the listbox change, I would like the new ones be written below the previous ones
I have a code for an empty row, but How to adapt it with the column B ?

Here is the code for empty row

Dim emptyRow As Long
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
Cells(emptyRow, 2).Value = ListBox1.Value

Any ideas ?

 

[Rick Rothstein]

I have a code that allows me to put in the column B of an Excel sheet all the items displayed in a listbox

Here is the code

For b = 0 To ListBox1.ListCount - 1
    With Cells(b + 3, 2)
        .ColumnWidth = 15
        .Value = ListBox1.List(b)
    End With
Next

This single line of code will do what the above looping code does...

Range("B3").Resize(ListBox1.ListCount) = ListBox1.List

But If the items of the listbox change, I would like the new ones be written below the previous ones
I have a code for an empty row, but How to adapt it with the column B ?

How is the ListBox populated with its values? Will the "new" items be written into the ListBox underneath any existing items that are already there? If not, does the above mean you want to have the code pick out any new items that were added in between older items and put them in Column B under the list of previously existing items?

 

[Doflamingo]

Hi @Rick Rothstein

The listbox is populated with the values of different optionbutton
No the ''new items'' are not written into the ListBox underneath any existing items that are already there, given that I have a commandbutton that clear the listbox. Here is the code

listbox1.clear

What I would like to see is the new items of the listbox be written in the excel sheet in the column B AND just the line below the previous ones.

The code you gave me does not put the new items of the listbox in an empty row for each of them, just below the last item of the listbox written previously
But it change the previous ones since the range ''B3''

Dim emptyRow As Long
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
For b = 0 To ListBox1.ListCount - 1
Cells(b + 3, emptyRow + 1).Value = ListBox1.List(b)
Next
Range("b3").Resize(ListBox1.ListCount) = ListBox1.List

 

[Rick Rothstein]

The code you gave me does not put the new items of the listbox in an empty row for each of them, just below the last item of the listbox written previously

I never said it would... what I said is it did exactly the same thing as the code you had posted. I could not answer your exact question without understanding how your data was set up which, I think, you have now answered. Let me think about how to implement that for you.

 

[Doflamingo]

Here is the answer

For b = 0 To ListBox1.ListCount - 1
With Sheets("Data")
 
     nextRow = .Range("B" & Rows.Count).End(xlUp).Row + 1
 
     .Range("B" & nextRow) = ListBox1.List(b)
End With


Next

 

[Rick Rothstein]

Here is the answer

For b = 0 To ListBox1.ListCount - 1
With Sheets("Data")
 
     nextRow = .Range("B" & Rows.Count).End(xlUp).Row + 1
 
     .Range("B" & nextRow) = ListBox1.List(b)
End With


Next

I think I may have misunderstood what you wanted. I thought you were going to add new items to an existing ListBox and that you wanted those new items pulled out from the existing list and placed on the worksheet, but the code you are showing says you will put whatever is in the Listbox when you run the code under the current list of items on the worksheet even if some of those items are duplicates. If that is what you want, here is my code modified to do that...

Cells(Rows.Count, "B").End(xlUp).Offset(1).Resize(ListBox1.ListCount) = ListBox1.List

Remember, this code is a replacement for the code you posted, not an addition to it.