Excel VBA: Looping through Userform selected Listbox Values to Worksheet Cell

[ajay_gajree]

Private Sub cmdPrint_Click()
''

 

[ajay_gajree]

Private Sub cmdPrint_Click()
'' --------------------------------------------------------------------------------
'' Printing
'' --------------------------------------------------------------------------------
Dim i As Integer
Dim lItem As Long
Dim wsh As Worksheet
Dim ans As Boolean
    
    ans = Application.Dialogs(xlDialogPrinterSetup).Show
    
    If ans Then
      
        i = tbxCopies.Value
        
        Call Shell("rundll32 printui.dll,PrintUIEntry /e /n" & Application.ActivePrinter & ")")
        
        MsgBox i & " copies will be printed.", vbInformation, "Info"
        
        For lItem = 0 To lboRisks.ListCount - 1
            If lboRisks.Selected(lItem) = True Then
                Set wsh = ThisWorkbook.Worksheets("BRP Dashboard")
                wsh.Range("R4").Value = lboRisks.Value
                wsh.PrintOut Copies:=i
                Set wsh = Nothing
            End If
        Next
    
    Cells(5, 11).Activate
    Unload Me
     
End Sub

Lets try that again, my post seem to have not posted correctly.

I have the above code where I am trying to loop through my selected listbox values and send in turn to cell R2 on my dashboard, instead I seem to be sending "True" to cell R2 instead

Can anyone help identify my error?

Thx in advance

 

[ajay_gajree]

With lboRisks
            For lItem = 0 To .ListCount - 1
                If .Selected(lItem) Then
                    Set wsh = ThisWorkbook.Worksheets("BRP Dashboard")
                    wsh.Range("R4").Value = .List(lItem)
                    wsh.PrintOut Copies:=i
                    Set wsh = Nothing
                End If
            Next lItem
End With

Solution found!

 

[Domenic]

Since the sheet being referenced doesn't change, you can assign it to your variable prior to your With/End With statement...

Set wsh = ThisWorkbook.Worksheets("BRP Dashboard")

With lboRisks
    For lItem = 0 To .ListCount - 1
        If .Selected(lItem) Then
            wsh.Range("R4").Value = .List(lItem)
            wsh.PrintOut Copies:=i
        End If
    Next lItem
End With


Set wsh = Nothing