Excel VBA | How to use selected number of items in Listbox as Long

[Young Grasshopper]

Hi world,

I have this small userform with a multiselect listbox:

I'm trying to write a code which is run/looped for each selected item in the listbox, so im trying to set selection as Long.
So in the example in the picture

"Ma - pri 1.." would be 0
"Ma - Brand" would be 1
"Test 6 abc 7" would be 2

The values in the userform will be different each time it's used.
This is what I have now, but i get an error, and i really have no idea on how to do it, so the error don't surprise me..

Sub CustomerPreview2()
Dim i As Long
For i = 0 To 99
        For Each item In PreviewCampaignListbox.Selected
           If PreviewCampaignListbox.Selected(i) = True Then
            Worksheets("Sheet1").Range("G11:U11").Offset(i).SpecialCells(xlCellTypeConstants).Copy
            ThisWorkbook.Worksheets("Sheet2").Range("B34").Offset(, i * 10).PasteSpecial Transpose:=True

                
                Exit For
              End if
            Next
         Next

End Sub

Any suggestions?

 

[RoryA]

It would be something like:

For i = 1 to PreviewCampaignListbox.ListCount
           If PreviewCampaignListbox.Selected(i - 1) = True Then

 

[Young Grasshopper]

Hi Rory,

Close to what I wanted, but a couple "problems":
This seems to give the each listindex a number, no matter if its selected or not?
In your code, if I only select the 4th listindex f.ex, the "i" will be "4" where i would like it to be "1". Since it's the first of the selected.

I would need "i" to count only selected and not the full list.
And would it also be possible to start "i" from 0 instead of 1? A can work with both, but from 0 would be cleaner.

Thank you

 

[RoryA]

I assumed the value for the offsets was related to the position in the list. If not, you'll need a separate counter variable that you increment in the loop. For example:

Sub CustomerPreview2()
  Dim i As Long
  Dim counter as long
  For i = 1 to PreviewCampaignListbox.ListCount
    If PreviewCampaignListbox.Selected(i - 1) = True Then

        Worksheets("Sheet1").Range("G11:U11").Offset(counter).SpecialCells(xlCellTypeConstants).Copy
        ThisWorkbook.Worksheets("Sheet2").Range("B34").Offset(, counter * 10).PasteSpecial Transpose:=True
        counter = counter + 1

   End if
 Next i

End Sub

 

[Young Grasshopper]

Sorry Rory,

I think I explained myself badly here... This is exactly what i need it to do:
I have two diffrent worksheets; Sheet1 and CustomerPreview.

The listbox is populated from the rows of data from Sheet1 and is populated every time the listbox is initialized.
The code is going to copy data from Sheet1 to customer preview in a neat way, but only the selection/rows i select from the listbox.

The "Offset(, i * 10)" for pasting is because there is a new "section" for pasting on every 10th column in Worksheet.CustomerPreview. Let's just call them Section1, Section2 and so on.

So let's say i open the userform with PreviewCampaignListbox. The listbox get's populated by 10 rows f.ex and I select the first three. With something similar to the first code you posted row/selection1 would be pasted in Section1, row2 in Section2 and row3 in Section3.. and that would be fine.

But let's say I select the 2th, 5th, 6th and 9th lines in PreviewCampaignListbox. From your first code the 2th row would be pasted in Section1, 5th row in Section2 and so on, but i need the 2th row to be pasted in Section1, 5th in Section2, 6th in Section3 and 9th in Section4.

So when copying the long variable needs to be calculated from how many rows I need to go down in Sheet1 to find the matching text, and when pasting the long variable needs to be calculated from if it's the top selection in PreviewCampaignListbox, or the 2th, or 3th and so on..

I think the last code you sendt would do this, but i didn't get it to work.
My problem as a newbee is that i can't quite figure out how to use two long variables in one If statement, and also how you refer to listbox value, when more then one value is selected.

I tried something like this.. Didn't work, but may show a little clearer what I'm trying to do

Private Sub PreviewActive_Click()
Dim i As Long
Dim ir As Long

For i = 0 To Me.PreviewCampaignListbox.ListCount - 1
For ir = 0 To 99

            If Me.PreviewCampaignListbox.Selected(i - 1) = True Then
                If Me.PreviewCampaignListbox.Text = ThisWorkbook.Worksheets("Sheet1").Range("D1").Offset(ir).Text Then
  
            Worksheets("Sheet1").Range("G11:U11").Offset(ir).SpecialCells(xlCellTypeConstants).Copy
            ThisWorkbook.Worksheets("CustomerPreview").Range("counter").Offset(, i * 10).PasteSpecial Transpose:=True
            

            Exit For
            End If
            End If
            
        Next
        Next
            

End Sub

 

[RoryA]

From your first code the 2th row would be pasted in Section1, 5th row in Section2 and so on, but i need the 2th row to be pasted in Section1, 5th in Section2, 6th in Section3 and 9th in Section4.

Um, what's the difference here?

 

[Young Grasshopper]

Nothing This is what I wrote in my head;
From your first code the 2th row would be pasted in Section2, 5th row in Section5 and so on,
but i need the 2th row to be pasted in Section1, 5th in Section2, 6th in Section3 and 9th in Section4.

 

[RoryA]

OK, so I think what you want is halfway between the two versions I posted:

Sub CustomerPreview2()
  Dim i As Long
  Dim counter as long
  For i = 1 to PreviewCampaignListbox.ListCount
    If PreviewCampaignListbox.Selected(i - 1) = True Then

        Worksheets("Sheet1").Range("G11:U11").Offset(i - 1).SpecialCells(xlCellTypeConstants).Copy
        ThisWorkbook.Worksheets("Sheet2").Range("B34").Offset(, counter * 10).PasteSpecial Transpose:=True
        counter = counter + 1

   End if
 Next i

End Sub

 

[Young Grasshopper]

Sorry Rory, I forgot to update you on this one, but it worked brilliant! Just as i wanted
Thank you so much, and enjoy your evening!