Populating a worksheet based on a Combo Box value within a User Form

[Blanchetdb]

Hello

I created a User Form that requires the client to complete. The client enters/selects information in various text boxes and combo boxes, selects SAVE at the end of the form and the info is copied in a specific Worksheet entitled Staffing-Processes. The Value in Combo Box entitled TestBranch is key for the next step. Based on the value they select; example: Science, the information is also copied into the Staffing-Science Worksheet. There are 11 different Branches, and they all have their own Worksheet.

this is what I have:

Private Sub CommandButton1_Click()
'when we click the continue button
Dim TargetRow As Integer
Dim TargetRow1 As Integer
Dim TargetRow2 As Integer
Dim TargetRow3 As Integer
Dim TargetRow4 As Integer
Dim TargetRow5 As Integer
Dim TargetRow6 As Integer
Dim TargetRow7 As Integer
Dim TargetRow8 As Integer
Dim TargetRow9 As Integer
Dim TargetRow10 As Integer
Dim TargetRow11 As Integer
Dim FullName As String 'full name
Dim Sel As Variant

Sel = Me.TestBranch.Value

      

If Sheets("Engine").Range("A3").Value = "NEW" Then
TargetRow = Sheets("Engine").Range("A2").Value + 1
Else
TargetRow = Sheets("Engine").Range("A4").Value
End If

''START INPUT IN DATABASE''

'INFORMATION (Core Information)
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 0).Value = TestGRLV
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 1).Value = TextStartDate
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 2).Value = TextBox1
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 3).Value = TestArea
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 4).Value = TestBranch
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 5).Value = TestNumber
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 7).Value = TextBox7
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 8).Value = TextBox8
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 9).Value = TextBox16
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 10).Value = ComboBox5
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 11).Value = ComboBox6
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 12).Value = ComboBox4
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 13).Value = ComboBox3
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 14).Value = TextBox15
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 15).Value = TextBox18
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 16).Value = TextBox17
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 17).Value = TextBox10
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 18).Value = TextBox19
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 19).Value = TestStatus
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 20).Value = TextBox5
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 22).Value = TextBox6
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 23).Value = ComboBox7
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 24).Value = TextBox11
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 25).Value = TextBox13
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 26).Value = TextBox12


Sel = Me.TestBranch.Value

'Copy to Staffing Science
If Sheets("Engine").Range("A3").Value = "NEW" And Sel = "Science/Sciences" Then
TargetRow1 = Sheets("Engine").Range("A10").Value + 1
Else
TargetRow1 = Sheets("Engine").Range("A12").Value
End If

Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 0).Value = TestGRLV
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 1).Value = TextStartDate
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 2).Value = TextBox1
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 3).Value = TestArea
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 4).Value = TestBranch
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 5).Value = TestNumber
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 7).Value = TextBox7
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 8).Value = TextBox8
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 9).Value = TextBox16
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 10).Value = ComboBox5
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 11).Value = ComboBox6
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 12).Value = ComboBox4
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 13).Value = ComboBox3
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 14).Value = TextBox15
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 15).Value = TextBox18
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 16).Value = TextBox17
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 17).Value = TextBox10
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 18).Value = TextBox19
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 19).Value = TestStatus
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 20).Value = TextBox5
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 22).Value = TextBox6
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 23).Value = ComboBox7
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 24).Value = TextBox11
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 25).Value = TextBox13
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 26).Value = TextBox12
 
'Copy to Staffing Legal
If Sheets("Engine").Range("A3").Value = "NEW" And Sel = "Agriculutre and Food Inspection Legal Services / Services juridiques - Agriculture et inspection des aliments" Then
TargetRow2 = Sheets("Engine").Range("A14").Value + 1
Else
TargetRow2 = Sheets("Engine").Range("A16").Value
End If

Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 0).Value = TestGRLV
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 1).Value = TextStartDate
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 2).Value = TextBox1
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 3).Value = TestArea
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 4).Value = TestBranch
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 5).Value = TestNumber
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 7).Value = TextBox7
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 8).Value = TextBox8
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 9).Value = TextBox16
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 10).Value = ComboBox5
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 11).Value = ComboBox6
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 12).Value = ComboBox4
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 13).Value = ComboBox3
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 14).Value = TextBox15
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 15).Value = TextBox18
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 16).Value = TextBox17
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 17).Value = TextBox10
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 18).Value = TextBox19
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 19).Value = TestStatus
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 20).Value = TextBox5
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 22).Value = TextBox6
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 23).Value = ComboBox7
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 24).Value = TextBox11
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 25).Value = TextBox13
Sheets("Legal-Staffing").Range("RefLegal").Offset(TargetRow2, 26).Value = TextBox12

'Copy to Staffing Audit
If Sheets("Engine").Range("A3").Value = "NEW" And Sel = "Audit and Evaluation/ Vérification et de l’évaluation" Then
TargetRow3 = Sheets("Engine").Range("A18").Value + 1
Else
TargetRow3 = Sheets("Engine").Range("A20").Value
End If

Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 0).Value = TestGRLV
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 1).Value = TextStartDate
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 2).Value = TextBox1
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 3).Value = TestArea
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 4).Value = TestBranch
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 5).Value = TestNumber
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 7).Value = TextBox7
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 8).Value = TextBox8
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 9).Value = TextBox16
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 10).Value = ComboBox5
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 11).Value = ComboBox6
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 12).Value = ComboBox4
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 13).Value = ComboBox3
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 14).Value = TextBox15
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 15).Value = TextBox18
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 16).Value = TextBox17
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 17).Value = TextBox10
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 18).Value = TextBox19
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 19).Value = TestStatus
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 20).Value = TextBox5
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 22).Value = TextBox6
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 23).Value = ComboBox7
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 24).Value = TextBox11
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 25).Value = TextBox13
Sheets("Audit-Staffing").Range("RefAudit").Offset(TargetRow3, 26).Value = TextBox12

this continues for the other Branches

the code presently copies the info on the User Form to ALL worksheets, not just the one that equals the Value in the ComboBox

can I get some assistance

thank you
Dan

 

[Blanchetdb]

temporarily make these changes subroutine
In SaveDataUsingSheetName

Run the code from the VBA screen. The values from the Debug.Print statements will display in the "Immediate" window.
Let me know what you see.

Do you have the capability to upload your Excel file to Google, or Drop Box?

'    Set rng = Range("BranchSheetNames") 'from Sheet7 - Staffing sheet names (Col B)
    Set rng = Range("Sheet1!$B$1:$B$13") 'from Sheet7 - Staffing sheet names (Col B)
    Debug.Print sheetname
    For Each v In rng
      Debug.Print v
    Next

In SaveDataUsingMenuTitle subroutine

'    Set rng = Range("BranchMenuList") 'TestBranch (ComboBox) selections (Col A)
    Set rng = Range("Sheet1!$A$1:$A$13") 'TestBranch (ComboBox) selections (Col A)
    Debug.Print TestBranch
    For Each v In rng
      Debug.Print v
    Next

If the above does not work

this is what I get

 

[Bosquedeguate]

Add Dim v
to the Subroutines.

Do you have an Option Explicit statement in your VBA code?

 

[Blanchetdb]

Add Dim v
to the Subroutines.

Do you have an Option Explicit statement in your VBA code?

Dim v ..... ? Range, Variant?

I have the Excel file saved on my desktop, I can send it to you.

 

[Bosquedeguate]

yes, please upload it to Google, DropBox or equivalent and the link to download it.

 

[Blanchetdb]

yes, please upload it to Google, DropBox or equivalent and the link to download it.

Google Sheets: Sign-in

Access Google Sheets with a personal Google account or Google Workspace account (for business use).

docs.google.com

1.select File
2.download
3.Microsoft Excel (XLSM)
4.select the download

and then you should have it

first time downloading to Google so not sure if I did it correctly

 

[igold]

Do you have an Option Explicit statement in your VBA code?

@Bosquedeguate, you apparently do not use Option Explicit. I have read some of your other posts and all of your suggested codes have undeclared variables. You may want to get in the habit of using Option Explicit and declaring all your variables. It would make it easier for the community which you are trying to help.

It might have helped in this thread!