Hi maryam,
I made up some data. "Sheet2" was the active sheet. On my form I have listbox1, listbox2, datagrid1, and cmdAdd as a command button. I populated listbox1 from a range (as you can see). Then populated listbox2 with the selected items from listbox1 on the listbox2 click and the datagrid1. then the user enters the values for the items selected. once finshed the user selects the command button to send the values to "Sheet3" this was where I put the data.
[VBA]
Option Explicit
Private r As ADOR.Recordset
Private InPutRange As ADOR.Recordset
Private iCols As Long, iRows As Long
Private Sub cmdAddNew_Click()
Dim mI As Long, k As Long
r.UpdateBatch
r.MoveFirst
For mI = 0 To r.RecordCount - 1
If mI = 0 Then
Worksheets("Sheet3").Cells(mI + 1, 1).Value = r.Fields(mI).Name
Worksheets("Sheet3").Cells(mI + 1, 2).Value = r.Fields(mI + 1).Name
Else
Worksheets("Sheet3").Cells(mI + 1, 1).Value = r.Fields(0).Value
Worksheets("Sheet3").Cells(mI + 1, 2).Value = r.Fields(1).Value
End If
If Not r.BOF Or Not r.EOF Then r.MoveNext
Next
Set r = Nothing
End Sub
Private Sub ListBox2_Click()
Dim mI As Long
For mI = 0 To ListBox1.ListCount
If ListBox1(mI).Selected Then
ListBox2.AddItem ListBox1(mI)
End If
Next
End Sub
Private Sub ListBox2_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim mI As Long
For mI = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(mI) Then
ListBox2.AddItem ListBox1.List(mI)
r.AddNew
r.Fields(0).Value = ListBox1.List(mI)
End If
Next
Set DataGrid1.DataSource = r
End Sub
Private Sub UserForm_Initialize()
Dim i As Long, j As Long, k As Long
Dim Rng As Range
iCols = 1
iRows = 95
'
' Create a new disconnected recordset object
'
Set r = New ADOR.Recordset
Set InPutRange = New ADOR.Recordset
Set OutPutRange = New ADOR.Recordset
' Add the column
InPutRange.Fields.Append "DataFromExcelSheet", adVarChar, 50
r.Fields.Append "Selected", adVarChar, 50
r.Fields.Append "Values", adVarChar, 50
Set Rng = ActiveSheet.Range("H6:H100")
InPutRange.CursorType = adOpenDynamic
InPutRange.Open
For j = 1 To iRows
InPutRange.AddNew
InPutRange.Fields(0).Value = Rng(j).Text
ListBox1.AddItem Rng(j).Text
Next
r.CursorType = adOpenDynamic
r.Open
End Sub
[/VBA]
Enjoy!
Tommy