User Form Help Please

UserForm1.ListBox1.List = ssSQL

Option Explicit
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim rng As Range
Dim NoCols As Long
Dim NoRows As Long
    Set ws = Worksheets.Add
    
    Set rng = ws.Range("A1")
        
    ' let's create some random data to work with
    ' you probably want to replace this with real data
    
    NoCols = Int(Rnd * 5) + 1
    
    NoRows = Int(Rnd * 20) + 1
        
    With rng.Resize(NoRows, NoCols)
        .Formula = "=CHAR(CODE(""A"")+column()-1) & INT(RAND()*100)+1"
        .Value = .Value
    End With
    
    With Me.ListBox1
        .ColumnCount = NoCols
        .ColumnWidths = Join(Split(String(NoCols, ";"), ";"), "30;")
        .List = rng.CurrentRegion.Value
        .MultiSelect = fmMultiSelectMulti
    End With
        
End Sub
 
Private Sub CommandButton2_Click()
    Unload Me
End Sub

Public Sub Routeuserform()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim MyConn
    Dim i As Long, intLastRow As Long
    Dim ShDest As Worksheet
    Dim ssSQL As String
    Dim rng As Range
    Dim Cell As Variant
    Dim whereString
    Dim x As Integer
    Dim varrecords As Variant

    Set ShDest = Sheets("TEST")
            
    ssSQL = " SELECT [Sql Man Plan].[CW Drg] AS [Cw No], [FN Routes].description AS Details, [FN Routes].hours AS [EST Hours]" _
         & " FROM [FN Routes] INNER JOIN [Sql Man Plan] ON [FN Routes].[file no] = [Sql Man Plan].[File No]" _
         & " WHERE ((([Sql Man Plan].[CW Drg])='" & UserForm1.TextBox1.Value & "'))"
         

    Set cnn = New ADODB.Connection
    MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB
    
    With cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open MyConn
    End With

    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseServer
    rst.Open Source:=ssSQL, ActiveConnection:=cnn, _
             CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, _
             Options:=adCmdText
    'i want to populate userform listbox now
    rst.MoveLast

   'Count the number of records that are being returned and assign that
   'number to a variable.
      x = rst.RecordCount

      'Move the record pointer to the first record. This is required in
      'order to return the number of records specified by the RecordCount
      'Property. If this is not done, the record pointer will remain on
      'the last record and only the last record will be returned.
      rst.MoveFirst

      'Return the records to an array variable.
      varrecords = rst.GetRows(x)

      'Fill the list box.
      UserForm1.ListBox1.List = Array(varrecords)
        
      
    ' Close the connection
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing

End Sub

'Fill the list box.
      UserForm1.ListBox1.List = varrecords