I have a userform created using Excel VBA that is used to register information before saving it to a worksheet. All the information the user enters is stored in a listbox and then copied to a worksheet. However, I need to copy each column of information to different ranges in the worksheet, they are not all together to copy whole listbox with the simple .List or .Value properties.
The listbox has four columns. Right now I am trying to get all the values stored on the second column to copy them to the worksheet. The number of rows in the listbox constantly changes depending on how many entries the user registers. I tried the following piece of code:
Range(FirstColumnRange).Resize(lstAllProducts.ListCount, 1).Value = lstAllProducts.List
This works perfectly for getting all the information on the first column of the listbox. Although the .List property gets all the values on the listbox, using the .Resize property with a fixed column value of 1 just saves the first column to the worksheet ignoring the others. The issue is that I can't seem to do the same for the other columns.
I tried the following code for getting the values of the second column:
Range(SecondColumnRange).Resize(lstAllProducts.ListCount, 1).Value = lstAllProducts.List(lstAllProducts.ListIndex, 1)
However, this only works if a row of the listbox is selected, and only saves that value. Is there a way to modify that last part of that code (lstAllProducts.List(lstAllProducts.ListIndex, 1)) so that I can get all the values on the relevant column (second column in this case) without selecting anything in the listbox? I would really appreciate any suggestion. Also, I would prefer a solution that does not require looping like the one I used for the first column.