CODEWITHSUNDEEP
vbaexcel
Santo
Santo

Reputation: 41

How to capture data to a specified excel sheet(first workbook) from two different workbooks from the Userform?

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?

Below is my code of combox and add commandbutton:

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

Upvotes: 0

Views: 102

Answers (2)

C. van Dorsten
C. van Dorsten

Reputation: 152

In the sub cmdadd_Click, the second workbook is still active. Therefore before Worksheets(‘Sheet1’).Activate add:

Dim wb As Workbook
Dim ssheet As Worksheet

Set wb = Workbooks.Open("C:\Users\Desktop\Work.xlmx")
Set ssheet = wb.Worksheets("Sheet1")

Like you did in the other subs. Next add the following before worksheets(‘sheet1”):

wb.activate
ssheet.activate

Delete these lines from your sub:

Dim Sheet1 As String
Worksheets(Sheet1).Activate

This should do the trick.

Upvotes: 0

John Steed
John Steed

Reputation: 629

In your code, I never see you setting the value of the Sheet1 string variable.

Please note it is not required to activate a worksheet in order to work with it. Likewise, it is not required a select a cell. Try something like this...

Private Sub cmdadd_Click()
Dim e As Long   
Dim destSheet As Worksheet
Set destSheet = Worksheets("Sheet1")

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.
    destSheet.Range("A6").Value = e 'Next ID number
    destSheet.Range("B6").Value = Me.txtname.Text 'set col B
    destSheet.Range("C6").Value = Me.txtbook.Text 'set col C
    destSheet.Range("D6").Value = Me.cboLs.Text 'set col D

    Me.txtname.Text = Empty
    Me.txtbook.Text = Empty
    Me.cboLs.Text = Empty

End Sub

Likewise, use the same approach for your loop to get your desired value of e. By the way, if you are just looking for the value of the last populated row in column A, instead of looping (which is inefficient for this purpose), you can do use 

destSheet.Cells(destSheet.Rows.Count, "A").End(xlUp).Value

This is the same as going to the last cell at the bottom of column A and pressing CTRL+Up to go to the last populated cell. Then you can just add 1 to that value.

Upvotes: 0

Related Questions