Thread: Copy List of Userform Data to Worksheet

Yes, I would like to start in the first cell of column "B" each time the code runs.

Thank you for the effort, but unfortunately that is a bit too restrictive for me, as re-naming is too difficult this late in the process.


Is there no way to simply create a list of all the listbox names and export them together?

One can get a list of all textbox controls and then you can get the names or values as needed. For your example though, I don't know how that would help as you have a combobox in the list.

If you set the value to some specific cell, you could just set the ControlSource property.

I actually removed the combo from the list, so it is entirely made up of listboxes.

Can you please provide an example of what you are referring to?

I first posted how to do this method at: http://www.mrexcel.com/forum/showthread.php?p=1626849

In a Module:

Option Explicit

Public cCB() As Control, cTB() As Control

In the Userform:

'Public cCB() As Control, cTB() As Control 'In a Module
Private Sub UserForm_Initialize()
  FillControlArrays
End Sub

Private Sub FillControlArrays()
  Dim iCB As Integer, iTB As Integer, c As Control

  For Each c In Me.Controls
    Select Case TypeName(c)
      Case "TextBox"
        iTB = iTB + 1
        ReDim Preserve cTB(1 To iTB)
        Set cTB(iTB) = c
       Case "ComboBox"
        iCB = iCB + 1
        ReDim Preserve cCB(1 To iCB)
        Set cCB(iCB) = c
      End Select
    Next c
End Sub

You can then use standard methods to iterate the control arrays to get or put what you need. In your case, you would iterate cTB().

That is great. Thank you all for your input