User Forms and Ranges in Excel

JezLisle · #1 (**permalink**) 05-11-07, 11:26

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

MikeTheBike · #2 (**permalink**) 05-14-07, 08:36

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

Internet Services

Web Development

Digital Marketing

Consumer Tech