NEED HELP on EXCELVB LISTBOX DATA TRANSFER TO EXCEL RANGES

[claw72]

Hello everyone.

i really need help on this one.. sorry for bad english!

I have made 2 listbox.. listbox1 and listbox2. data on listbox 2 comes every after i double click listbox1 data. my codes for that works properly.

i am working in a excel worksheet. i have these columns A(date), B(group),C(accomplishment),D(daily income) and E(group details).

now i want that :
1. when i select any cells on column E(group details) the userform containing those list boxes as earlier mentioned will appear.

2. data on listbox2 will be posted/transfered to excel ranges(separate sheet) after a button click.

Can anyone help me how to do sample codes for these?

i will greatly appreciate any help on this.

[Ancient Dragon]

>>Can anyone help me how to do sample codes for these?

What language?

[claw72]

hi ancient,

english please.. excelvba codes in english..

[Ancient Dragon]

I meant what programming language (c, c++, java, vb, shell, etc)

[claw72]

vb.. am using visual basic in excel.

[AndreRet]

Try the following -

Private Sub UserForm_Initialize()
Dim ListItems As Variant, i As Integer
Dim SourceWB As Workbook
    With Me.ListBox1
        .Clear ' remove existing entries from the listbox
        ' turn screen updating off, 
        ' prevent the user from seeing the source workbook being opened
        Application.ScreenUpdating = False 
        ' open the source workbook as ReadOnly
        Set SourceWB = Workbooks.Open("C:\FolderName\SourceWorkbook.xls", _
            False, True)
        ListItems = SourceWB.Worksheets(1).Range("B2:B21").Value 
        ' get the values you want
        SourceWB.Close False ' close the source workbook without saving changes
        Set SourceWB = Nothing
        Application.ScreenUpdating = True
        ListItems = Application.WorksheetFunction.Transpose(ListItems) 
        ' convert values to a vertical array
        For i = 1 To UBound(ListItems)
            .AddItem ListItems(i) ' populate the listbox
        Next i
        .ListIndex = -1 ' no items selected, set to 0 to select the first item
    End With
End Sub

From a closed workbook that is opened and closed again without the user noticing it
With the macro below you can fill a ListBox control with input from another closed workbook.
The macro opens the closed workbook without the user noticing it, gets the values for the ListBox and closes
the source workbook without saving any changes. Copy the macro into the UserForm's module sheet.

Private Sub UserForm_Initialize()
Dim ListItems As Variant, i As Integer
Dim SourceWB As Workbook
    With Me.ListBox1
        .Clear ' remove existing entries from the listbox
        ' turn screen updating off, 
        ' prevent the user from seeing the source workbook being opened
        Application.ScreenUpdating = False 
        ' open the source workbook as ReadOnly
        Set SourceWB = Workbooks.Open("C:\FolderName\SourceWorkbook.xls", _
            False, True)
        ListItems = SourceWB.Worksheets(1).Range("B2:B21").Value
        ' get the values you want
        SourceWB.Close False ' close the source workbook without saving changes
        Set SourceWB = Nothing
        ListItems = Application.WorksheetFunction.Transpose(ListItems) 
        ' convert values to a vertical array
        For i = 1 To UBound(ListItems)
            .AddItem ListItems(i) ' populate the listbox
        Next i
        .ListIndex = -1 ' no items selected, set to 0 to select the first item
        Application.ScreenUpdating = True
    End With
End Sub

From a closed workbook by using ADO
With the macros below you can fill in an ListBox/ComboBox with input from another workbook without opening it.
Copy the macros into the UserForm's module sheet.

Private Sub UserForm_Initialize()
' fill ListBox1 with data from a closed workbook
' can also be used from other applications to read data from an open workbook
Dim tArray As Variant
    tArray = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:B21")
    FillListBox Me.ListBox1, tArray
    Erase tArray
End Sub

Private Sub FillListBox(lb As MSForms.ListBox, RecordSetArray As Variant)
' fills lb with data from RecordSetArray
Dim r As Long, c As Long
    With lb
        .Clear
        For r = LBound(RecordSetArray, 2) To UBound(RecordSetArray, 2)
            .AddItem
            For c = LBound(RecordSetArray, 1) To UBound(RecordSetArray, 1)
                .List(r, c) = RecordSetArray(c, r)
            Next c
        Next r
        .ListIndex = -1 ' no item selected
    End With
End Sub

Private Function ReadDataFromWorkbook(SourceFile As String, _
    SourceRange As String) As Variant
' requires a reference to the Microsoft ActiveX Data Objects library 
' (menu Tools, References in the VBE)
' if SourceRange is a range reference:
'   this function can only return data from the first worksheet in SourceFile
' if SourceRange is a defined name reference:
'   this function can return data from any worksheet in SourceFile
' SourceRange must include the range headers
' examples:
' varRecordSetData = _
    ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:A21")
' varRecordSetData = _
    ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:B21")
' varRecordSetData = _
    ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "DefinedRangeName")
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
    dbConnectionString = _
        "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & SourceFile
    Set dbConnection = New ADODB.Connection
    On Error GoTo InvalidInput
    dbConnection.Open dbConnectionString ' open the database connection
    Set rs = dbConnection.Execute("[" & SourceRange & "]")
    On Error GoTo 0
    ReadDataFromWorkbook = rs.GetRows 
    ' returns a two dim array with all records in rs
    dbConnection.Close ' close the database connection
    rs.Close
    Set rs = Nothing
    Set dbConnection = Nothing
    On Error GoTo 0
    Exit Function
InvalidInput:
    MsgBox "The source file or source range is invalid!", _
        vbExclamation, "Get data from closed workbook"
    Set rs = Nothing
    Set dbConnection = Nothing
End Function

Hope this helps.

[claw72]

sir andre,

thank you very much for the code. however the reverse procedure is what i want. i already have a list on my listbox. what i want is to have this list be posted in excel ranges after a button click.

but then again, thank you very much. i'll study it thoroughly. i know it will help a lot.

[AndreRet]

It's a pleasure.

The code you need is in there. Change it to suit your needs.