i am using a excel form in my local machine and currently given the code like be

Question

i am using a excel form in my local machine and currently given the code like below for the add button:

Private Sub ADD_click()
Dim emptyRow As Long

'Make Sheet1 active
Sheet5.Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
Cells(emptyRow, 1).Value = ComboBox1.Value
Cells(emptyRow, 2).Value = ComboBox2.Value
Cells(emptyRow, 3).Value = TextBox6.Value
Cells(emptyRow, 4).Value = TextBox1.Value
Cells(emptyRow, 5).Value = TextBox2.Value
Cells(emptyRow, 6).Value = TextBox3.Value
Cells(emptyRow, 7).Value = TextBox4.Value
Cells(emptyRow, 8).Value = TextBox5.Value
End Sub

now i want to update those details to the excel sheet in my share location in the place of sheet5.

Explanation / Answer

Just make the below modifications highlighted in BOLD

Private Sub ADD_click()
Dim emptyRow As Long
Dim WB As Workbook
'Make Sheet1 active

'Replace the file path appropriately

Set WB = Workbooks.Open("C:UsersXXXXDesktopTEXT_FILE.xlsm")

'Sheet5.Activate

WB.Sheets("Sheet1").Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
Cells(emptyRow, 1).Value = ComboBox1.Value
Cells(emptyRow, 2).Value = ComboBox2.Value
Cells(emptyRow, 3).Value = TextBox6.Value
Cells(emptyRow, 4).Value = TextBox1.Value
Cells(emptyRow, 5).Value = TextBox2.Value
Cells(emptyRow, 6).Value = TextBox3.Value
Cells(emptyRow, 7).Value = TextBox4.Value
Cells(emptyRow, 8).Value = TextBox5.Value

'Closing the file on Directory

WB.Close SaveChanges:=True

End Sub

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.