Selected Answer
You are absolutely right! What you are asking is standard fare. But you should bear in mind that Microsoft will always teach the methods they perceive to be easier to learn rather than the correct way. Therefore they teach a lot of rubbish. One of those things is how to call a form.
The correct way is to first create the form as a variable, then show and hide it as many times as you like, and only unload it when you're done. This scenario gives you access to the form and all its controls both before showing it and after. This would (and does) enable you to load your ListBox before you show it and transfer values from it to a worksheet in the time after hiding and before unloading.
The code below demonstrates this process.
Private Sub Cmb_PriceLists_Click()
' TXL 054
Dim FrmService As Form_ListServices
Dim Ws As Worksheet
Dim R As Long ' row number
Dim x As Long
Dim i As Integer ' array index
' this command creates the form
' the Initialize event fires
Set FrmService = New Form_ListServices
' the form now exists with all its controls
' and you can access any one of them
With FrmService
With .ListBox1
.ColumnCount = 3
.List = Range("Rng_Services").Value
End With
.Show vbModal ' the Activate event occurs
' from this point forward the modal form takes control
' =====
' Code resumes HERE when a command in the form Hides it.
' Make sure not to Unload the form from within the form.
' The form is still fully loaded but again invisible
' you can access all controls and transfer data out
' a 'Sheet' isn't the same as a 'Worksheet'
Set Ws = Worksheets("Invoices")
With Ws
' this code defines the last cell in the sheet and looks
' for the first used cell going UP
R = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
' your code defines A as a start and looks for the first blank
' cell going dow, presuming the one above it to be the last used.
' It will find a blank cell between existing data.
End With
With .ListBox1
' not tested:-
For i = 0 To .ListCount - 1
If (.Selected(i) = True) And (.List(i, 1) <> "") Then
Ws.Cells(R, "A").Value = .List(i, 1)
For x = 0 To 2
Ws.Cells(R + x + 1, "A").Value = .List(i, x)
Next x
End If
Next i
End With
End With
Unload FrmService ' now unload the form
Set FrmService = Nothing ' and destroy it
End Sub
