Listbox Column Widths and Lengths -Autofit?

Roy,

I tried your solution, and got a Run-Time Error 9: Subscript out of range.

One possible error I see is putting this routine in the Initialize step. Once the user selects the region, the data is copied from from the SourceData/Sheet1 workbook/sheet to the Final/Data workbook/sheet, so if this runs in the Initialize subform routine that's prior to populating the data.

However, I tried it with data present, and that's when I got the Subscript out of range.

Here's the code as I tried to implement it. I'd appreciate your eyeballing it for errors:

Dim rSource As Range
    Dim ColCnt As Long
    Dim i      As Integer
    Dim CW     As String
'get the data source
    Set rSource = Workbooks("Final").Worksheets("Data").Cells(1, 1).CurrentRegion
'number of columns of data
    ColCnt = rSource.Columns.Count
    CW = ""
'build the widths based on columns' widths on sheet
    With Me.Data
'populate Listbox
        .List = rSource.Value
        For i = 1 To ColCnt
            CW = CW & rSource.Columns(ColCnt).Width & ";"
        Next i
'number of columns in listbox
        .ColumnCount = ColCnt
'set column widths
        .ColumnWidths = CW
'nothing selected
        .ListIndex = -1
    End With

I haven't tried moving to activate yet, just commented it out for now so I could get you a working copy of what I've got so far (see the attachments).

Roy,

I've tidied up what I could. I don't know what variables haven't been declared. I also don't know how to use "Option Explicit"; I've seen it in a few routines, but don't know what it does, where it goes, how to use, or why to use it. If I try to put it at the top of a routine it jumps up to the bottom of the previous one.

I've attached updated spreadsheets for your review.

Going to the "Activate Event of the Form", the listbox form gets populated when the data is copied from SourceData to Final. No click happens, on the form, so the code won't be applied. How do we get it to apply automatically, or should I be using something different than a listbox to show the data?

Marking as solved, as I'm redoing the userform and may not even use the field. I appreciate Roy's help. Thanks, and Merry Christmas