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

B

Blanchetdb

Board Regular
Joined
Jul 31, 2018
Messages
153
Office Version
  1. 2016
Platform
  1. Windows
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:

VBA Code: 
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
 
Sort by date Sort by votes
Bosquedeguate said:
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?

VBA Code: 
'    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

VBA Code: 
'    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
Click to expand...
this is what I get

1690569139359.png
1690569139359.png
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Add Dim v
to the Subroutines.

Do you have an Option Explicit statement in your VBA code?
 
Upvote 0
yes, please upload it to Google, DropBox or equivalent and the link to download it.
 
Upvote 0
Bosquedeguate said:
yes, please upload it to Google, DropBox or equivalent and the link to download it.
Click to expand...

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
 
Upvote 0
Bosquedeguate said:
Do you have an Option Explicit statement in your VBA code?
Click to expand...
@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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
populating the next available row in a shared workbook
Replies
1
Views
263
rondeondo
rondeondo

Forum statistics

Threads
1,215,073
Messages
6,122,970
Members
449,095
Latest member
Mr Hughes

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