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

K

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: 30

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
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
Saurabhj said:
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
Click to expand...
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
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
Saurabhj said:
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
Click to expand...
hello saurabhj, thank you for your help! The code works perfectly now.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Change code from inside sheet to userform directly
Replies
0
Views
145
KalilMe
K
S
  • Solved
Help Required With Barcode Being Read Into A Userform...
Replies
3
Views
793
NoSparks
N
S
  • Solved
Using User form to load workbook, extract and combine desired data into new workbook or worksheet
2
Replies
10
Views
346
mumps
mumps
M
  • Question
Multiple users using same user form having errors and slow at times
Replies
2
Views
206
Joe4
Joe4
P
  • Solved
VBA to stop writing to specific sheet based on user selection
Replies
1
Views
190
Paul15
P

Forum statistics

Threads
1,214,387
Messages
6,119,208
Members
448,874
Latest member
Lancelots

Share this page

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
Back
Top