listbox??

[miiia83]

hi!

I was wondering if anyone could show me how the code to make a listbox.. The code I have in Excel VB is as shows... I want the function_code to be shown as a listbox!

Sub Get_Category()

    resrow = 3     'börjar vid cell 3,2
    rescol = 2
    Sheets("sheet1").Range("b2:j1000").ClearContents
    

    OpenConnection ("RFC")
   
    strQuery = "SELECT * FROM DESCRIPTIONS"   'DESCRIPTIONS = tabellnamn
    Set rs = ConnRFC.Execute(strQuery)
    Do Until rs.EOF
        Sheets("sheet1").Cells(resrow, rescol).Value = rs("FUNCTION_CODE")
        Sheets("sheet1").Cells(resrow, rescol + 1).Value = rs("DESCRIPTION")
        
        resrow = resrow + 1
        rs.MoveNext
    Loop
    
    KillConnection ("RFC")


End Sub

 

[eblake]

I might be way off base, but you can create a validation box from an access database using the following.. You can select a range to populate with the validation list before you run the sub, or it will use whatever is currently selected.

Public cn As New ADODB.Connection
Sub add_validation()
Dim rs As New Recordset
Dim strSQL As String

dbLoc = "c:\documents and settings\accountname\dbname" 'Set location of database


    If cn.State = 1 Then cn.Close 'If connection to db is open close it
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbLoc & ".MDB;Persist Security Info=False"
    strSQL = "SELECT OM.`OM Last Name`, OM.`OM First Name`, OM.`Aspect Team` FROM `" & dbLoc & "`.OM OM WHERE (OM.`OM Last Name` Not Like 'Agent Left Department-%') AND (OM.Gone=0) AND (OM.Center='Dallas') ORDER BY OM.`OM Last Name`"
    rs.Open strSQL, cn, adOpenForwardOnly, adLockPessimistic, adCmdText

myList = "" 'Blank out list
    Do Until rs.EOF
       myList = myList & rs.Fields("OM FIRST NAME") & "," 'Loop through and build list for validation
        rs.MoveNext
    Loop

    myList = Left(myList, Len(myList) - 1) 'Stip the trailing , off
    
    If cn.State = 1 Then cn.Close 'If connection to db is open close it
    
'Build Validation
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=myList
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

 

[miiia83]

strSQL = "SELECT OM.`OM Last Name`, OM.`OM First Name`, OM.`Aspect Team` FROM `" & dbLoc & "`.OM OM WHERE (OM.`OM Last Name` Not Like 'Agent Left Department-%') AND (OM.Gone=0) AND (OM.Center='Dallas') ORDER BY OM.`OM Last Name`"

I didn't quite understand that line..??