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
Blanchetdb said:
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

represent?
and what does

mean?

I really appreciate your help with this
Click to expand...
Yes - you still need a Sheet7 (or put it on some other sheet) with this data. I would create a name range for it.

The rows in should match up with your ComboBox list
Cols A - D are
A - What is displayed in the ComboBox
B - Corresponding Sheet name
C - Name References (I do not have these so I will need your help)
D - Range Address in the Engines sheet for Row, New and Row (3 values for each menu selection as defined in Engines)


wks.Range("$B$1:$B$14") - this is the hardcoded Range for the info on Sheet7 (including the header row). It should be a named range eventually.

values = wks.Range("A" & r & ":D" & r).Value - this is a row of data from Sheet7 (corresponding to ComboBox selection.) By using the .Value method a 2 dimensional array is created with the values from the row. Sine the range is only for 1 row the values we want are in (1,1), (1,2), (1,3), and (1,4) where:
(1,1) - is the menu (ComboBox string),
(1,2) - corresponding sheet name,
(1,3) - Refences (I assumed these are name ranges (which I do not have or know))
(1,4) - Address range from the Engines sheet

These are columns A-D described above.
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Bosquedeguate said:
Yes - you still need a Sheet7 (or put it on some other sheet) with this data. I would create a name range for it.

The rows in should match up with your ComboBox list
Cols A - D are
A - What is displayed in the ComboBox
B - Corresponding Sheet name
C - Name References (I do not have these so I will need your help)
D - Range Address in the Engines sheet for Row, New and Row (3 values for each menu selection as defined in Engines)


wks.Range("$B$1:$B$14") - this is the hardcoded Range for the info on Sheet7 (including the header row). It should be a named range eventually.

values = wks.Range("A" & r & ":D" & r).Value - this is a row of data from Sheet7 (corresponding to ComboBox selection.) By using the .Value method a 2 dimensional array is created with the values from the row. Sine the range is only for 1 row the values we want are in (1,1), (1,2), (1,3), and (1,4) where:
(1,1) - is the menu (ComboBox string),
(1,2) - corresponding sheet name,
(1,3) - Refences (I assumed these are name ranges (which I do not have or know))
(1,4) - Address range from the Engines sheet

These are columns A-D described above.
Click to expand...
these are the references:

1690503503532.png
 
Upvote 0
Code should
Update the Staffing-Processes (always), then
Update the Sheet corresponding to the ComboBox selection.
(Always 2 interations in the code)


Revised Sheet7

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-StaffingRefCPAA22:24
7Corporate Management / Gestion intégréeCMB-StaffingRefCMBA26:A28
8Policy and Programs/ Politique et ProgrammesPPB-StaffingRefPPBA50:A52
9Human Resources / Ressources HumainesHR-StaffingRefHRA30:A32
10Innovation, Business and Service Development Branch/ Direction générale du développement des affaires, des services et de l’innovationIBSDB-StaffingRefIBSDBA34:A36
11Integrity and Redress Secretariat/ Direction générale de l’intégrité et des recoursIRS-StaffingRefIRSA38:A40
12International Affairs/ Affaires internationalesIA-StaffingRefIAA42:A44
13Office of the President/Bureau de la présidenteRefOPA46-A48
14DADRefDADA6:A8
15
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
Blanchetdb said:
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
Click to expand...
so I created the table in Sheet 1, the different sheets are in column B from 1 to 13, the ComboBox name is TestBranch

and when I try to run it, I get this message

1690504225065.png
 
Upvote 0
Change the code slightly.

We are now looking for the long string from the ComboBox in Column A (not B).

tmpWksName = ComboBox1.Value
Set wks = Worksheets("Sheet7")
Set rng = wks.Range("$A$1:$A$14")
r = WorksheetFunction.Match(tmpWksName, rng, 0)
values = wks.Range("A" & r & ":D" & r).Value
 
Upvote 0
here is how I am populating the ComboBox in the form initialization code:
I pulls the entries from my Sheet7 (Column A) - I assume the DAD and Operations entries should not be in that Sheet? Correct?
Should "Staffing-Processes" be on that Sheet (since we automatically update that sheet I assume it does not need to be a menu selection.)

VBA Code: 
Private Sub UserForm_Initialize()
  Dim wks As Worksheet
  Set wks = Worksheets("Sheet7")
  For Each v In wks.Range("A2:A13").Value
    If Left(v, 3) <> "xxx" Then
    ComboBox1.AddItem v
  Next
End Su

b
 
Upvote 0
Bosquedeguate said:
Change the code slightly.

We are now looking for the long string from the ComboBox in Column A (not B).

tmpWksName = ComboBox1.Value
Set wks = Worksheets("Sheet7")
Set rng = wks.Range("$A$1:$A$14")
r = WorksheetFunction.Match(tmpWksName, rng, 0)
values = wks.Range("A" & r & ":D" & r).Value
Click to expand...
so the table is in Sheet 1
Bosquedeguate said:
here is how I am populating the ComboBox in the form initialization code:
I pulls the entries from my Sheet7 (Column A) - I assume the DAD and Operations entries should not be in that Sheet? Correct?
Should "Staffing-Processes" be on that Sheet (since we automatically update that sheet I assume it does not need to be a menu selection.)

VBA Code: 
Private Sub UserForm_Initialize()
  Dim wks As Worksheet
  Set wks = Worksheets("Sheet7")
  For Each v In wks.Range("A2:A13").Value
    If Left(v, 3) <> "xxx" Then
    ComboBox1.AddItem v
  Next
End Su

b
Click to expand...
The DAD, Operations and Staffing-Processes does not need to be on the sheet

right now when I select: NEW Staffing .....
1690505713212.png

the UserForm opens with this code

Sub ShowTestUserForm()

Sheets("Engine").Range("A3").Value = "NEW"
Sheets("Engine").Range("A11").Value = "NEW"
Sheets("Engine").Range("A15").Value = "NEW"
Sheets("Engine").Range("A19").Value = "NEW"
Sheets("Engine").Range("A23").Value = "NEW"
Sheets("Engine").Range("A27").Value = "NEW"
Sheets("Engine").Range("A31").Value = "NEW"
Sheets("Engine").Range("A35").Value = "NEW"
Sheets("Engine").Range("A39").Value = "NEW"
Sheets("Engine").Range("A43").Value = "NEW"
Sheets("Engine").Range("A47").Value = "NEW"
Sheets("Engine").Range("A51").Value = "NEW"
TextAdvior.Show

End Sub

the UserForm is called TextAdvior
it also makes sure that on the Engine sheet all the respective cells show NEW because there is also an EDIT option that is yet to be finalized....NEW triggers a new row to be created where the EDIT mode will allow me to EDIT an already existing entry.
 

Attachments

  • 1690505484264.png
    1690505484264.png
    39.6 KB · Views: 3
Upvote 0
I suggest the following code to initialize your Engine sheet values to "New". It is cleaner and eliminated redundant coding.

VBA Code: 
  Dim wks As Worksheet
  Dim addr
  rngaddrs = Array("A3", "A11", "A15", "A19", "A23", "A27", "A31", "A35", "A39", "A43", "A47", "A51")
  
  Set wks = Worksheets("Engine")
  For Each addr In rngaddrs
    wks.Range(addr) = "NEW"
  End
 
Upvote 0
Here is a way to initialize Engine sheet values to "NEW" using the information from Sheet7, Column D
VBA Code: 
  Dim wks As Worksheet
  Dim wksEngine As Worksheet
  Dim addr
  
  Set wksEngine = Worksheets("Engine")
  Set wks = Worksheets("Sheet7")
  
  For Each v In wks.Range("D2:D13").Value
    tmp = Split(v, ":")
    addr = Left(tmp(0), 1) & (Mid(tmp(0), 2) + 1) 'get first value in the range and increment it by one (1)  "A10" becomes "A11'
    wksEngine.Range(addr) = "NEW"
  Next
 
Upvote 0
Bosquedeguate said:
Here is a way to initialize Engine sheet values to "NEW" using the information from Sheet7, Column D
VBA Code: 
  Dim wks As Worksheet
  Dim wksEngine As Worksheet
  Dim addr
 
  Set wksEngine = Worksheets("Engine")
  Set wks = Worksheets("Sheet7")
 
  For Each v In wks.Range("D2:D13").Value
    tmp = Split(v, ":")
    addr = Left(tmp(0), 1) & (Mid(tmp(0), 2) + 1) 'get first value in the range and increment it by one (1)  "A10" becomes "A11'
    wksEngine.Range(addr) = "NEW"
  Next
Click to expand...
so....

are suggesting that I change the present code I use when I initialize the UserForm to the code above OR insert this code to the coding that fills the worksheets?

also, I still get the error message

1690510073589.png

can help on how to fix that issue?

thank you for all your help .... didn't realize it was going to be that much
 
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,975
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