Private Sub cboLs_DropButtonClick() Dim wb As Workbook 'workbook 2 for combobox list Dim i As Long Dim ssheet As Worksheet Set wb = Workbooks.Open("C:\Users\Desktop\Book1.xlsx") Set ssheet = wb.Worksheets("LS") If Me.cboLs.ListCount = 0 Then For i = 2 To ssheet.Range("A" & ssheet.Rows.Count).End(xlUp).Row Me.cboLs.AddItem Sheets("LS").Cells(i, "A").Value Next i End If End Sub Private Sub cboLs_Change() Dim wb As Workbook Dim ssheet As Worksheet Dim i As Long Set wb = Workbooks.Open("C:\Users\Desktop\Book1.xlsx") Set ssheet = wb.Worksheets("LS") For i = 2 To ssheet.Range("A" & ssheet.Rows.Count).End(xlUp).Row If ssheet.Cells(i, "A").Value = (Me.cboLs) Or ssheet.Cells(i, "A").Value = Val(Me.cboLs) Then Me.txtProject = ssheet.Cells(i, "B").Value End If Next i End Sub Private Sub cmdadd_Click() Dim e As Long Dim Sheet1 As String Worksheets(Sheet1).Activate 'Workbook-1 here i need to capture my userform data but it is going to workbook-2 on sheetname LS 'position cursor in the correct cell A6. ActiveSheet.Range("A6").Select e = 1 'set as the first ID 'if all the above are false (OK) then carry on. 'check to see the next available blank row start at cell A6... Do Until ActiveCell.Value = Empty ActiveCell.Offset(1, 0).Select 'move down 1 row e = e + 1 'keep a count of the ID for later use Loop 'Populate the new data values into the 'Data' worksheet. ActiveCell.Value = e 'Next ID number ActiveCell.Offset(0, 2).Value = Me.txtname.Text 'set col B ActiveCell.Offset(0, 3).Value = Me.txtbook.Text 'set col C ActiveCell.Offset(0, 1).Value = Me.cboLs.Text 'set col D Me.txtname.Text = Empty Me.txtbook.Text = Empty Me.cboLs.Text = Empty End Sub
I have workbook-1 where the data should be actually captured from the userform when add button is clicked.
In workbook-2 I just have my combobox list inorder to display the excel data when selected from the comobox and textbox automatically in the userform.
But now I am facing a problem, when I fill the userform by selecting all combobox list and filling other data manually then clicked on add button the data is trasferring to my Workbook-2 (below my combobox list).
How to capture the userform data to my workbook-1 on Sheetname "Sheet1".
My Workbook-2 path is "C:\Users\Desktop\Work.xlmx", will I need to include this path also for the commandbutton?