Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: User Forms and Ranges in Excel

    I have a user form which want to have show when i click on a command button, this works. I then want a list box to populate with a range of data which could grow in size from one of the worksheets (AllActions) in this workbook. This code below is what I am trying to use but not having much joy. Can anyone see why its not working?

    Jez

    Dim adoRS As New ADODB.Recordset
    Sub addToList(rs As ADODB.Recordset, strShtName As String)
    Dim lngJ As Long
    For lngJ = 0 To rs.Fields.Count - 1
    Sheets("AllActions").Cells(1, lngJ + 1) = rs.Fields(lngJ).Name
    Next
    Sheets("AllActions").Range("A2").CopyFromRecordset rs
    If rs.RecordCount >= 1 Then
    If rs.RecordCount = 1 Then
    Me.lstActions.RowSource = "AllActions!A2:J" + CStr(findLastRow(Sheets("AllActions").Range("A2"), "") + 1)
    Me.lstActions.ColumnCount = rs.Fields.Count
    Me.lstActions.ColumnHeads = True
    Else
    Me.lstActions.RowSource = "AllActions!A2:J" + CStr(findLastRow(Sheets("AllActions").Range("A2"), ""))
    Me.lstActions.ColumnCount = rs.Fields.Count
    Me.lstActions.ColumnHeads = True
    End If
    Else
    Me.lstActions.RowSource = "AllActions!A2:J2"
    Me.lstActions.ColumnCount = rs.Fields.Count
    Me.lstActions.ColumnHeads = True
    End If
    Set adoRS = rs
    End Sub

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I find what you are trying to do a little confusing, but I have put your code in the forn Initialise (sorry Initiliaze) event which seems to work.

    Code:
    Private Sub UserForm_Initialize()
    Dim lngJ As Long
    Dim rs As New ADODB.Recordset
    Dim cn As New ADODB.Connection
    
        cn.Open "TEST"
        
        rs.Open "SELECT * FROM tblJobs", cn, adOpenStatic, adLockReadOnly
        
        For lngJ = 0 To rs.Fields.Count - 1
        Sheets("AllActions").Cells(1, lngJ + 1) = rs.Fields(lngJ).Name
        Next
        Sheets("AllActions").Range("A2").CopyFromRecordset rs
        If rs.RecordCount >= 1 Then
            If rs.RecordCount = 1 Then
                    Me.lstActions.RowSource = "AllActions!A2:J" & rs.RecordCount + 1
                    Me.lstActions.ColumnCount = rs.Fields.Count
                    Me.lstActions.ColumnHeads = True
                Else
                    Me.lstActions.RowSource = "AllActions!A2:J" & rs.RecordCount + 1
                    Me.lstActions.ColumnCount = rs.Fields.Count
                    Me.lstActions.ColumnHeads = True
            End If
        Else
            Me.lstActions.RowSource = "AllActions!A2:J2"
            Me.lstActions.ColumnCount = rs.Fields.Count
            Me.lstActions.ColumnHeads = True
        End If
        
        rs.Close
        Set rs = Nothing
        cn.Close
        Set cn = Nothing
    End Sub
    basically I only change the is

    + CStr(findLastRow(Sheets("AllActions").Range("A2"), ""))


    To this

    & rs.RecordCount + 1

    EDIT: which make the if statment "If rs.RecordCount = 1 Then" redundant ?

    The only other thing(s) is I am not sure what you do with "strShtName"
    or why this "Set adoRS = rs"

    Where to you declare/dimension/open the "rs" recordset ??

    Also if you do not need the information in the sheet you could populate the list box directly from the record set.


    HTH


    MTB
    Last edited by MikeTheBike; 05-14-07 at 09:39.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •