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

B

Blanchetdb

Board Regular
Joined
Jul 31, 2018
Messages
161
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
If the value in your ComboBox contains the name of the worksheet that is to receive your data you can use that value and eliminate a lot of redundant code.
Also, your code appears to be writing to all your worksheets.

Consider adding a sheet with the following information. Use the ComboBox value to lookup the values for the code below.

A copy of your user form would be helpful -

Add Row button.xlsm
ABCD
1Sheet NameReferenceTargetRowRef1TargetRowRef2
2Staffing-ProcessesRefA2A4
3Science-StaffingRefScienceA10A12
4Legal-StaffingRefLegalA14A16
5Audit-StaffingRefAuditA18A20
Sheet7


Here is a sample consolidated version of your code (again this is only a quick sample of how you could implement your code and was not tested due to lack of data.)

Note: Comments are added to the code to, hopefully, help you understand how to make your code more effficient.

VBA Code: 
Private Sub CommandButton1_Click()
  Dim wksName As String
  Dim wks As Worksheet
  Dim rng As Range
  
  Dim refRngName As String
  Dim tmp
  
  'Get value(s) from command button
    wksName = ComboBox1.Value
    Set wks = Worksheets("Sheet7")
    Set rng = wks.Range("$A$2:$D$5")
    strRef = WorksheetFunction.VLookup(wksName, rng, 2, False)
    strTgt1 = WorksheetFunction.VLookup(wksName, rng, 3, False)
    strTgt2 = WorksheetFunction.VLookup(wksName, rng, 4, False)
    refRngName = strRef
    
'-----------------------------------------------------------
'the code here is also dependent on the ComboBox Selection
' the values could all be driven from another table or from arrays within your code
' Range ( reference)
' SheetNames
' TargetRow(s) from ranges -
' etc
' ....
'-----------------------------------------------------------

  
If Sheets("Engine").Range("A3").Value = "NEW" Then
  TargetRow = Sheets("Engine").Range(strTgt1).Value + 1 ' the value "A2" and "A4" should be driven from a lookup table.
Else
  TargetRow = Sheets("Engine").Range(strTgt2).Value
End If

''START INPUT IN DATABASE''

'The following code should only appear once in your event code

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

'....

End Sub
[CODE=vba]
[/CODE]

I'd be happy to help more if needed. Please provide a complete version of your form event code.

I hope this helps!!
 
Upvote 0
Bosquedeguate said:
If the value in your ComboBox contains the name of the worksheet that is to receive your data you can use that value and eliminate a lot of redundant code.
Also, your code appears to be writing to all your worksheets.

Consider adding a sheet with the following information. Use the ComboBox value to lookup the values for the code below.

A copy of your user form would be helpful -

Add Row button.xlsm
ABCD
1Sheet NameReferenceTargetRowRef1TargetRowRef2
2Staffing-ProcessesRefA2A4
3Science-StaffingRefScienceA10A12
4Legal-StaffingRefLegalA14A16
5Audit-StaffingRefAuditA18A20
Sheet7


Here is a sample consolidated version of your code (again this is only a quick sample of how you could implement your code and was not tested due to lack of data.)

Note: Comments are added to the code to, hopefully, help you understand how to make your code more effficient.

VBA Code: 
Private Sub CommandButton1_Click()
  Dim wksName As String
  Dim wks As Worksheet
  Dim rng As Range
 
  Dim refRngName As String
  Dim tmp
 
  'Get value(s) from command button
    wksName = ComboBox1.Value
    Set wks = Worksheets("Sheet7")
    Set rng = wks.Range("$A$2:$D$5")
    strRef = WorksheetFunction.VLookup(wksName, rng, 2, False)
    strTgt1 = WorksheetFunction.VLookup(wksName, rng, 3, False)
    strTgt2 = WorksheetFunction.VLookup(wksName, rng, 4, False)
    refRngName = strRef
   
'-----------------------------------------------------------
'the code here is also dependent on the ComboBox Selection
' the values could all be driven from another table or from arrays within your code
' Range ( reference)
' SheetNames
' TargetRow(s) from ranges -
' etc
' ....
'-----------------------------------------------------------

 
If Sheets("Engine").Range("A3").Value = "NEW" Then
  TargetRow = Sheets("Engine").Range(strTgt1).Value + 1 ' the value "A2" and "A4" should be driven from a lookup table.
Else
  TargetRow = Sheets("Engine").Range(strTgt2).Value
End If

''START INPUT IN DATABASE''

'The following code should only appear once in your event code

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

'....

End Sub
[CODE=vba]
[/CODE]

I'd be happy to help more if needed. Please provide a complete version of your form event code.

I hope this helps!!
Click to expand...
Unfortunately the value of the Combo Box is not the name of the sheet...

for example:
Sheet named Science-Staffing needs to interact with a Combo Box value = "Science/Sciences"
Sheet Named Audit-Staffing needs to interact with a Combo Box value = "Audit and Evaluation/ Vérification et de l’évaluation"
etc....

Sheet called Engine looks like this:
A B
406Total entries in database - Staffing-Processes
NEWMode
408Row store
1230Total entries in database - Log DAD
NEWMode
1231Row store
228Total entries in database - Staffing/Science
NEWMode
229Row store
1Total entries in database - Staffing/Legal
NEWMode
2Row store
7Total entries in database - Staffing/Audit
NEWMode
8Row store
15Total entries in database - Staffing/CPA
NEWMode
16Row store
55Total entries in database - Staffing/CMB
NEWMode
57Row store
83Total entries in database - Staffing/HR
NEWMode
83Row store
60Total entries in database - Staffing/IBSDB
NEWMode
61Row store
1Total entries in database - Staffing/IRS
NEWMode
2Row store
15Total entries in database - Staffing/IA
NEWMode
16Row store
5Total entries in database - Staffing/OP
NEWMode
6Row store
26Total entries in database - Staffing/PPB
NEWMode
27Row store

A2, A6, A10, A14, A18, etc... up to A50 has the following formula: =COUNTA('Staffing-Processes'!A2:A1099)

each time the User Form is completed, the information MUST be added to the Staffing-Processes sheet. The data is then added to another sheet based on the ComboBox. So if the ComboBox equals "Science/Sciences" then the information is also copied to the sheet entitled Science-Staffing

this is the user form:

1690486452797.png
 
Upvote 0
Ok
I think the code sample I sent you will still work with a minor changes.

Can you provide the text for the ComboBox selections?
 
Upvote 0
H
Bosquedeguate said:
Ok
I think the code sample I sent you will still work with a minor changes.

Can you provide the text for the ComboBox selections?
Click to expand...
Presently on the road but there are 11 different options within the combo box so if you insert “A, B, C, …. “

I can replace with the correct names later

Thanks
 
Upvote 0
Blanchetdb said:
H

Presently on the road but there are 11 different options within the combo box so if you insert “A, B, C, …. “

I can replace with the correct names later

Thanks
Click to expand...
these are the selections that can be chosen within the Comb Box

Agriculutre and Food Inspection Legal Services / Services juridiques - Agriculture et inspection des aliments
Audit and Evaluation/ Vérification et de l’évaluation
Communications and Public Affairs/ Communications et affaires publiques
Corporate Management / Gestion intégrée
Human Resources / Ressources Humaines
Innovation, Business and Service Development Branch/ Direction générale du développement des affaires, des services et de l’innovation
Integrity and Redress Secretariat/ Direction générale de l’intégrité et des recours
International Affairs/ Affaires internationales
Office of the President/Bureau de la présidente
Operations (Atlantic)/Opérations (Atlantique)
Operations (NHQ)/Opérations (AC)
Operations (Ontario)/Opérations (Ontario)
Operations (Quebec)/Opérations (Québec)
Operations (West)/Opérations (Ouest)
Policy and Programs/ Politique et Programmes
Science/Sciences
 
Upvote 0
Here is revised ComboBox event code:
It will update the Staffing-Process sheet first,
then the sheet selected by the ComboBox. Needs an additional as previously shown.

VBA Code: 
Private Sub CommandButton1_Click()
  Dim wksName As String
  Dim wksEngine As Worksheet
  Dim wks As Worksheet
  Dim wksOutput As Worksheet
  Dim tmpWksName As String
  Dim rng As Range, rngtmp As Range, rngOutput As Range
  Dim values
  
  Dim refRngName As String
  Dim tmp
  
  'Get value(s) from command button
    tmpWksName = ComboBox1.Value
    Set wks = Worksheets("Sheet7")
    Set rng = wks.Range("$B$1:$B$14")
    r = WorksheetFunction.Match(wksName, rng, 0)
    values = wks.Range("A" & r & ":D" & r).Value
    
    
'-----------------------------------------------------------
'the code here is also dependent on the ComboBox Selection
' the values could all be driven from another table or from arrays within your code
' Range ( reference)
' SheetNames
' TargetRow(s) from ranges -
' etc
' ....
'-----------------------------------------------------------

  Set wks = Worksheets("Sheet7")
  Set rng = wks.Range("$B$1:$B$14") 'this should be a named range
  For i = 1 To 2
    Select Case i:
      Case 1 'Always Update Staffing Processes
        'Get value(s) from command button
        wksName = "Staffing-Processes"
        r = WorksheetFunction.Match(wksName, rng, 0)
      Case 2 'Target Sheet
        wksName = tmpWksName
        r = WorksheetFunction.Match(wksName, rng, 3)
    End Select
    values = wks.Range("A" & r & ":D" & r).Value
    'based on Values(1,4) - addresses of values in "Engine" sheet
    'get their values
    engAddr = values(1, 4)
    Set wksEngine = Worksheets("Engine")
    engVals = wksEngine.Range(engAddr).Value
    
    If engVals(2, 1) = "NEW" Then
      TargetRow = engVals(1, 1) + 1 ' the value "A2" and "A4" should be driven from Engine sheet for Staffing-Processes.
    Else
      TargetRow = engVals(3, 1)
    End If
      


''START INPUT IN DATABASE''

'The following code should only appear once in your event code

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

End Sub
 
Upvote 0
Bosquedeguate said:
Here is revised ComboBox event code:
It will update the Staffing-Process sheet first,
then the sheet selected by the ComboBox. Needs an additional as previously shown.

VBA Code: 
Private Sub CommandButton1_Click()
  Dim wksName As String
  Dim wksEngine As Worksheet
  Dim wks As Worksheet
  Dim wksOutput As Worksheet
  Dim tmpWksName As String
  Dim rng As Range, rngtmp As Range, rngOutput As Range
  Dim values
 
  Dim refRngName As String
  Dim tmp
 
  'Get value(s) from command button
    tmpWksName = ComboBox1.Value
    Set wks = Worksheets("Sheet7")
    Set rng = wks.Range("$B$1:$B$14")
    r = WorksheetFunction.Match(wksName, rng, 0)
    values = wks.Range("A" & r & ":D" & r).Value
   
   
'-----------------------------------------------------------
'the code here is also dependent on the ComboBox Selection
' the values could all be driven from another table or from arrays within your code
' Range ( reference)
' SheetNames
' TargetRow(s) from ranges -
' etc
' ....
'-----------------------------------------------------------

  Set wks = Worksheets("Sheet7")
  Set rng = wks.Range("$B$1:$B$14") 'this should be a named range
  For i = 1 To 2
    Select Case i:
      Case 1 'Always Update Staffing Processes
        'Get value(s) from command button
        wksName = "Staffing-Processes"
        r = WorksheetFunction.Match(wksName, rng, 0)
      Case 2 'Target Sheet
        wksName = tmpWksName
        r = WorksheetFunction.Match(wksName, rng, 3)
    End Select
    values = wks.Range("A" & r & ":D" & r).Value
    'based on Values(1,4) - addresses of values in "Engine" sheet
    'get their values
    engAddr = values(1, 4)
    Set wksEngine = Worksheets("Engine")
    engVals = wksEngine.Range(engAddr).Value
   
    If engVals(2, 1) = "NEW" Then
      TargetRow = engVals(1, 1) + 1 ' the value "A2" and "A4" should be driven from Engine sheet for Staffing-Processes.
    Else
      TargetRow = engVals(3, 1)
    End If
     


''START INPUT IN DATABASE''

'The following code should only appear once in your event code

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

End Sub
Click to expand...
thank you

so I still need to create a table with all the information shown previously and have it reside in "Sheet 7" or whatever other sheet I choose ... right?

what does
wks.Range("$B$1:$B$14")
Click to expand...
represent?
and what does
values = wks.Range("A" & r & ":D" & r).Value
Click to expand...
mean?

I really appreciate your help with this
 
Upvote 0
Based on your ComboBox list, did I get the descriptions/division titles properly matched with a Named worksheet.

There are several ComboBox list entries (mostly Operations offices) for which I do not know their respective Sheets (if there is one), also for the Office of the President.
Is the a ComboBox entry for the DAD sheet?

Add Row button.xlsm
ABCD
1CBXTextSheet NameReferenceTarget Range
2Science/SciencesScience-StaffingRefScienceA10:A12
3Agriculutre and Food Inspection Legal Services / Services juridiques - Agriculture et inspection des alimentsLegal-StaffingRefLegalA14:A16
4Audit and Evaluation/ Vérification et de l’évaluationAudit-StaffingRefAuditA18:A20
5xxxStaffing-ProcessesRefA2:A4
6Communications and Public Affairs/ Communications et affaires publiquesCPA-StaffingA22:24
7Corporate Management / Gestion intégréeCMB-StaffingA26:A28
8Policy and Programs/ Politique et ProgrammesPPB-StaffingA50:A52
9Human Resources / Ressources HumainesHR-StaffingA30:A32
10Innovation, Business and Service Development Branch/ Direction générale du développement des affaires, des services et de l’innovationIBSDB-StaffingA34:A36
11Integrity and Redress Secretariat/ Direction générale de l’intégrité et des recoursIRS-StaffingA38:A40
12International Affairs/ Affaires internationalesIA-StaffingA42:A44
13DADA6:A8
14
15Office of the President/Bureau de la présidente
16Operations (Atlantic)/Opérations (Atlantique)
17Operations (NHQ)/Opérations (AC)
18Operations (Ontario)/Opérations (Ontario)
19Operations (Quebec)/Opérations (Québec)
20Operations (West)/Opérations (Ouest)
Sheet7
 
Upvote 0
Bosquedeguate said:
Based on your ComboBox list, did I get the descriptions/division titles properly matched with a Named worksheet.

There are several ComboBox list entries (mostly Operations offices) for which I do not know their respective Sheets (if there is one), also for the Office of the President.
Is the a ComboBox entry for the DAD sheet?

Add Row button.xlsm
ABCD
1CBXTextSheet NameReferenceTarget Range
2Science/SciencesScience-StaffingRefScienceA10:A12
3Agriculutre and Food Inspection Legal Services / Services juridiques - Agriculture et inspection des alimentsLegal-StaffingRefLegalA14:A16
4Audit and Evaluation/ Vérification et de l’évaluationAudit-StaffingRefAuditA18:A20
5xxxStaffing-ProcessesRefA2:A4
6Communications and Public Affairs/ Communications et affaires publiquesCPA-StaffingA22:24
7Corporate Management / Gestion intégréeCMB-StaffingA26:A28
8Policy and Programs/ Politique et ProgrammesPPB-StaffingA50:A52
9Human Resources / Ressources HumainesHR-StaffingA30:A32
10Innovation, Business and Service Development Branch/ Direction générale du développement des affaires, des services et de l’innovationIBSDB-StaffingA34:A36
11Integrity and Redress Secretariat/ Direction générale de l’intégrité et des recoursIRS-StaffingA38:A40
12International Affairs/ Affaires internationalesIA-StaffingA42:A44
13DADA6:A8
14
15Office of the President/Bureau de la présidente
16Operations (Atlantic)/Opérations (Atlantique)
17Operations (NHQ)/Opérations (AC)
18Operations (Ontario)/Opérations (Ontario)
19Operations (Quebec)/Opérations (Québec)
20Operations (West)/Opérations (Ouest)
Sheet7
Click to expand...
you did get the correct

Office of the President is A46:48

operations will be created during a different process independent from this one but using the similar coding...none for the DAD sheet
 
Upvote 0
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,223,888
Messages
6,175,206
Members
452,618
Latest member
Tam84

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