one userform working for two sheets with different command.

[yoon]

hello good morning and happy Monday everyone, I want to ask it is possible to get a data from one userform to multiple sheets which for the first sheet, I want to sum the total of hours and total quantity based on the same product name and process number and the rest sheets are the daily report of the worker based on id workers? help you all understand my question and I also attached images to make it more understanding. I hope you all can help with my problem. As you can see for the first image is I want the data of the total hours and total quantity based on product name and process number and the second image is one of the id worker daily report sheet

 

[Bosquedeguate]

Yes, your code for the Submit button on your form can write to more than one sheet. Can you provide the current code for the Submit button and the Sheet names where you want to store data?

 

[yoon]

Private Sub CommandButton1_Click()
targetsheet = ComboBox2.Value
If targetsheet = "" Then
Exit Sub
End If
Worksheets(targetsheet).Activate
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Cells(lastrow + 1, 1).Value = ComboBox1.Value
ActiveSheet.Cells(lastrow + 1, 2).Value = TextBox1.Value
ActiveSheet.Cells(lastrow + 1, 3).Value = TextBox2.Value
ActiveSheet.Cells(lastrow + 1, 4).Value = TextBox3.Value
ActiveSheet.Cells(lastrow + 1, 5).Value = TextBox4.Value
ActiveSheet.Cells(lastrow + 1, 6).Value = TextBox5.Value
ActiveSheet.Cells(lastrow + 1, 7).Value = TextBox6.Value
ActiveSheet.Cells(lastrow + 1, 8).Value = TextBox7.Value
ActiveSheet.Cells(lastrow + 1, 9).Value = TextBox8.Value
ActiveSheet.Cells(lastrow + 1, 10).Value = TextBox9.Value
MsgBox ("Data is added successfully")
End Sub

the sheet name that I want to store the total hours and total quantity based on product name and process number: "FORM AFTER COMPLETED WORK"

 

[Bosquedeguate]

Dim wksCompleted
Set wksCompleted = Worksheets(“FORM AFTER COMPLETED WORK")

Just like your ActiveSheet code

wksCompleted.Cell(r,c).Value = form control value (e.g TextBox1) …

 

[yoon]

Dim wksCompleted
Set wksCompleted = Worksheets(“FORM AFTER COMPLETED WORK")

Just like your ActiveSheet code

wksCompleted.Cell(r,c).Value = form control value (e.g TextBox1) …

can you give the full code as I not understand the last sentence?

 

[yoon]

Dim wksCompleted
Set wksCompleted = Worksheets(“FORM AFTER COMPLETED WORK")

Just like your ActiveSheet code

wksCompleted.Cell(r,c).Value = form control value (e.g TextBox1) …

I think it will have problem to put the data as the userform has combobox to choose sheet where I want to put the data but I want it will automatic also put the data in the "form after completed work" even I choose other sheet.

 

[Bosquedeguate]

Your modified click event for your "SUBMIT" button should look something like the following.
I assume I got the TextBox(number) values correct for output to the COMPLETED work sheet.

Private Sub CommandButton1_Click()
  Dim wksCompleted As Worksheet
  
  targetsheet = ComboBox2.Value
  If targetsheet = "" Then
    Exit Sub
  End If
  Worksheets(targetsheet).Activate
  lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
  lastrow = lastrow + 1
  With ActiveSheet
    .Cells(lastrow, 1).Value = ComboBox1.Value
    .Cells(lastrow, 2).Value = TextBox1.Value
    .Cells(lastrow, 3).Value = TextBox2.Value
    .Cells(lastrow, 4).Value = TextBox3.Value
    .Cells(lastrow, 5).Value = TextBox4.Value
    .Cells(lastrow, 6).Value = TextBox5.Value
    .Cells(lastrow, 7).Value = TextBox6.Value
    .Cells(lastrow, 8).Value = TextBox7.Value
    .Cells(lastrow, 9).Value = TextBox8.Value
    .Cells(lastrow, 10).Value = TextBox9.Value
  End With
  MsgBox ("Record added to sheet " & targetsheet & " successfully.")
  
  Set wksCompleted = Worksheets("FORM AFTER COMPLETED WORK")
  
  lastrow = wksCompleted.Cells(Rows.Count, 1).End(xlUp).Row
  lastrow = lastrow + 1
  With wksCompleted
    .Cells(lastrow, 2).Value = TextBox1.Value 'Product Name
    .Cells(lastrow, 3).Value = TextBox3.Value 'Process No
    .Cells(lastrow, 4).Value = TextBox6.Value 'Total Hours
    .Cells(lastrow, 5).Value = TextBox7.Value 'Total Qty
  End With
  MsgBox ("Record added to sheet " & wksCompleted.Name & "successfully.")
End Sub

 

[yoon]

Your modified click event for your "SUBMIT" button should look something like the following.
I assume I got the TextBox(number) values correct for output to the COMPLETED work sheet.

Private Sub CommandButton1_Click()
  Dim wksCompleted As Worksheet
 
  targetsheet = ComboBox2.Value
  If targetsheet = "" Then
    Exit Sub
  End If
  Worksheets(targetsheet).Activate
  lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
  lastrow = lastrow + 1
  With ActiveSheet
    .Cells(lastrow, 1).Value = ComboBox1.Value
    .Cells(lastrow, 2).Value = TextBox1.Value
    .Cells(lastrow, 3).Value = TextBox2.Value
    .Cells(lastrow, 4).Value = TextBox3.Value
    .Cells(lastrow, 5).Value = TextBox4.Value
    .Cells(lastrow, 6).Value = TextBox5.Value
    .Cells(lastrow, 7).Value = TextBox6.Value
    .Cells(lastrow, 8).Value = TextBox7.Value
    .Cells(lastrow, 9).Value = TextBox8.Value
    .Cells(lastrow, 10).Value = TextBox9.Value
  End With
  MsgBox ("Record added to sheet " & targetsheet & " successfully.")
 
  Set wksCompleted = Worksheets("FORM AFTER COMPLETED WORK")
 
  lastrow = wksCompleted.Cells(Rows.Count, 1).End(xlUp).Row
  lastrow = lastrow + 1
  With wksCompleted
    .Cells(lastrow, 2).Value = TextBox1.Value 'Product Name
    .Cells(lastrow, 3).Value = TextBox3.Value 'Process No
    .Cells(lastrow, 4).Value = TextBox6.Value 'Total Hours
    .Cells(lastrow, 5).Value = TextBox7.Value 'Total Qty
  End With
  MsgBox ("Record added to sheet " & wksCompleted.Name & "successfully.")
End Sub

thank you for your answer I am very appreciate it But I want to ask how can I make the userform is still can be used for sheet for other workbooks? As supposedly the "form after completed work" sheet is at another workbook

 

[Bosquedeguate]

Assuming you have common fields in your other worksheets you will add similar code to write your TextBox values to other sheets in your Active workbook or sheets in other Workbooks.

If other Workbooks they should be opened to prevent errors.

The ComboBox that you have on your form to select the Target worksheet or Workbook.Worksheet should work.

I would probably use a VBA
Select Case ComboBox2
Case sheetname1
…
Case sheetname2
…
End Select
for writing your code to save the form data

Does this answer your question?