How to link user form text boxes to multiple worksheets in the same workbook?

karmaLee

New Member
Joined
Nov 23, 2021
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
Hello, I am still very new to VBA and am working on a project where I have created a user form to send data input by user to a “Sheet3”. Now I want the same user form to send the data input to BOTH “Sheet3” and “Sheet7”. Currently, my user form can send data to “Sheet3” just fine.

In “Sheet7”, I have several columns: “Date”, “Code”, “Serial No.”, “Repair Date”, and “Status”

I want the user form’s Date, Code, Serial No. textboxes and OptionButton8 to also send the data to Sheet7 based on the columns respective name.

Here is my code(from userform):

Private Sub CommandButton1_Click()
'code to send data from service records userform to service record excel sheet

Dim newRow As Long
'activate service records excel sheet
Sheet3.Activate
'determine newRow
newRow = WorksheetFunction.CountA(Range("A:A")) + 1
'code to transfer data from userform listboxes, textboxes etc to excel sheet
'code to transfer data from textbox1 to "DATE" column
Cells(newRow, 1).Value = TextBox1.Value
'code to transfer data from textbox2 to "CODE" column
Cells(newRow, 3).Value = TextBox2.Value
'code to transfer data from textbox3 to "SERIAL NO" column
Cells(newRow, 4).Value = TextBox3.Value
If OptionButton8.Value = True Then
Cells(newRow, 10).Value = "Pending"
Else
End If

End sub




Then, I would like to program using VBA, the “Repair Date” column in such that when user keys in the date, the “Status” column will change from “Pending” to “Repaired” (assuming the “Status” column is already in “Pending”)

Any help would be greatly appreciated. Thank you.
 

Attachments

  • testt.png
    testt.png
    50.6 KB · Views: 22

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Saurabhj

Well-known Member
Joined
Jun 6, 2020
Messages
662
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi,

The new code would be :
VBA Code:
Private Sub CommandButton1_Click()
'code to send data from service records userform to service record excel sheet

Dim newRow As Long, newRowSh7 As Long

'activate service records excel sheet
Sheet3.Activate
'determine newRow
newRow = WorksheetFunction.CountA(Range("A:A")) + 1
newRowSh7 = Sheets("Sheet7").Cells(Rows.Count, 1).End(xlUp).Row + 1

'code to transfer data from userform listboxes, textboxes etc to excel sheet
'code to transfer data from textbox1 to "DATE" column
Cells(newRow, 1).Value = TextBox1.Value
'code to transfer data from textbox2 to "CODE" column
Cells(newRow, 3).Value = TextBox2.Value
'code to transfer data from textbox3 to "SERIAL NO" column
Cells(newRow, 4).Value = TextBox3.Value

If OptionButton8.Value = True Then
    Cells(newRow, 10).Value = "Pending"
Else
End If

With Sheets("Sheet7")
    'code to transfer data from textbox1 to "DATE" column
    Cells(newRowSh7, 1).Value = TextBox1.Value
    'code to transfer data from textbox2 to "CODE" column
    Cells(newRowSh7, 2).Value = TextBox2.Value
    'code to transfer data from textbox3 to "SERIAL NO" column
    Cells(newRowSh7, 3).Value = TextBox3.Value
    'code to transfer data from textbox2 to "Repair Date" column
    Cells(newRowSh7, 4).Value = TextBox4.Value
    'code to transfer data from textbox3 to "Status" column
    Cells(newRowSh7, 5).Value = TextBox5.Value
End With

End Sub

To change status of column when date is entered by user, we need to add event handler. In Sheet7, use below code.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   Sheets("Sheet7").Cells(Target.Row, Target.Column + 1) = "Repaired"
     
End Sub
 
Upvote 0

karmaLee

New Member
Joined
Nov 23, 2021
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
Hi,

The new code would be :
VBA Code:
Private Sub CommandButton1_Click()
'code to send data from service records userform to service record excel sheet

Dim newRow As Long, newRowSh7 As Long

'activate service records excel sheet
Sheet3.Activate
'determine newRow
newRow = WorksheetFunction.CountA(Range("A:A")) + 1
newRowSh7 = Sheets("Sheet7").Cells(Rows.Count, 1).End(xlUp).Row + 1

'code to transfer data from userform listboxes, textboxes etc to excel sheet
'code to transfer data from textbox1 to "DATE" column
Cells(newRow, 1).Value = TextBox1.Value
'code to transfer data from textbox2 to "CODE" column
Cells(newRow, 3).Value = TextBox2.Value
'code to transfer data from textbox3 to "SERIAL NO" column
Cells(newRow, 4).Value = TextBox3.Value

If OptionButton8.Value = True Then
    Cells(newRow, 10).Value = "Pending"
Else
End If

With Sheets("Sheet7")
    'code to transfer data from textbox1 to "DATE" column
    Cells(newRowSh7, 1).Value = TextBox1.Value
    'code to transfer data from textbox2 to "CODE" column
    Cells(newRowSh7, 2).Value = TextBox2.Value
    'code to transfer data from textbox3 to "SERIAL NO" column
    Cells(newRowSh7, 3).Value = TextBox3.Value
    'code to transfer data from textbox2 to "Repair Date" column
    Cells(newRowSh7, 4).Value = TextBox4.Value
    'code to transfer data from textbox3 to "Status" column
    Cells(newRowSh7, 5).Value = TextBox5.Value
End With

End Sub

To change status of column when date is entered by user, we need to add event handler. In Sheet7, use below code.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   Sheets("Sheet7").Cells(Target.Row, Target.Column + 1) = "Repaired"
    
End Sub
For the code that is supposed to send the user form data to both sheets, the data is now only sent to Sheet 7 and Sheet3 now does not receive the data.

For this code, is there any way to make it such that only 1 column is changed? When I tested the code, the columns after the "Status" columns all have the "Repaired" in them. I only want 1 column to have this feature.
To change status of column when date is entered by user, we need to add event handler. In Sheet7, use below code.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   Sheets("Sheet7").Cells(Target.Row, Target.Column + 1) = "Repaired"
    
End Sub
 
Upvote 0

Saurabhj

Well-known Member
Joined
Jun 6, 2020
Messages
662
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi,

Please make below change:

The code will update Column E if user make any change in Column D.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Assuming Repair Date is in 4th Column (D) then to update
'Column E use below
    If Target.Column = 4 Then
        Sheets("Sheet7").Cells(Target.Row, Target.Column + 1) = "Repaired"
    End If
End Sub
 
Upvote 0

karmaLee

New Member
Joined
Nov 23, 2021
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Please make below change:

The code will update Column E if user make any change in Column D.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Assuming Repair Date is in 4th Column (D) then to update
'Column E use below
    If Target.Column = 4 Then
        Sheets("Sheet7").Cells(Target.Row, Target.Column + 1) = "Repaired"
    End If
End Sub
hello saurabhj, thank you for your help! The code works perfectly now.
 
Upvote 0

Forum statistics

Threads
1,195,902
Messages
6,012,202
Members
441,679
Latest member
GDOG_27

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top