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
 
Sort by date Sort by votes
Regarding changes to TestBranch combo box initialization, and
setting Engine cell values to "NEW" ...
When I am doing software engineering I try to eliminate hardcoding values and use configuration data (like we have in Sheet7) to set values.
This way if/when there are changes you do not have to dig through your code looking for hardcoded values that might have to change.
This is why I have, also, suggested creating named ranges in Sheety for Column A, ColumnB, and all of the data A1:D13 - and using these named ranges
in the new code that has been added. This way if the lists change (increase or decrease in size) the only change that would need to be made is the
range(s) in the Name Manager.

So, to answer your question ... No, you do have to make the changes that I have suggested, but for your own sanity you should seriously consider them.




"Compile error: Can't find project or library"
Error was caused by wksName not being defined in the line
r = WorksheetFunction.Match(wksName, rng, 0)

Delete the following lines of code:
VBA Code: 
tmpWksName = TestBranch.Value
Set wks = Worksheets("Sheet1")
Set rng = wks.Range("$B$2:$B$13")
r = WorksheetFunction.Match(wksName, rng, 0)
values = wks.Range("A" & r & ":D" & r).Value


Cut and Paste the code below
Change "Sheet7" below to the sheet where you have the new data (Menu (combo box) text, Staffing sheet names, Refences, Engine sheet address range (An:An+2)

VBA Code: 
'-----------------------------------------------------------
' the code here is also dependent on the TestBranch (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")  
  For i = 1 To 2
    Select Case i:
      Case 1 'Always Update Staffing Processes
        Set rng = wks.Range("$B$1:$B$14") 'Sheet7 - Staffing sheet names (Col B)
        'Get value(s) from command button
        wksName = "Staffing-Processes"
        r = WorksheetFunction.Match(wksName, rng, 0)
      Case 2 'Target Sheet
        Set rng = wks.Range("$A$1:$A$14") 'TestBranch (ComboBox) selections (Col A)
        wksName = TestBranch
        r = WorksheetFunction.Match(wksName, rng, 0)
    End Select
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Bosquedeguate said:
Regarding changes to TestBranch combo box initialization, and
setting Engine cell values to "NEW" ...
When I am doing software engineering I try to eliminate hardcoding values and use configuration data (like we have in Sheet7) to set values.
This way if/when there are changes you do not have to dig through your code looking for hardcoded values that might have to change.
This is why I have, also, suggested creating named ranges in Sheety for Column A, ColumnB, and all of the data A1:D13 - and using these named ranges
in the new code that has been added. This way if the lists change (increase or decrease in size) the only change that would need to be made is the
range(s) in the Name Manager.

So, to answer your question ... No, you do have to make the changes that I have suggested, but for your own sanity you should seriously consider them.




"Compile error: Can't find project or library"
Error was caused by wksName not being defined in the line
r = WorksheetFunction.Match(wksName, rng, 0)

Delete the following lines of code:
VBA Code: 
tmpWksName = TestBranch.Value
Set wks = Worksheets("Sheet1")
Set rng = wks.Range("$B$2:$B$13")
r = WorksheetFunction.Match(wksName, rng, 0)
values = wks.Range("A" & r & ":D" & r).Value


Cut and Paste the code below
Change "Sheet7" below to the sheet where you have the new data (Menu (combo box) text, Staffing sheet names, Refences, Engine sheet address range (An:An+2)

VBA Code: 
'-----------------------------------------------------------
' the code here is also dependent on the TestBranch (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") 
  For i = 1 To 2
    Select Case i:
      Case 1 'Always Update Staffing Processes
        Set rng = wks.Range("$B$1:$B$14") 'Sheet7 - Staffing sheet names (Col B)
        'Get value(s) from command button
        wksName = "Staffing-Processes"
        r = WorksheetFunction.Match(wksName, rng, 0)
      Case 2 'Target Sheet
        Set rng = wks.Range("$A$1:$A$14") 'TestBranch (ComboBox) selections (Col A)
        wksName = TestBranch
        r = WorksheetFunction.Match(wksName, rng, 0)
    End Select
Click to expand...
I have no formal training in rgards to training as most of my knowledge comes from watching YouTube videos or looking at other coding and trying to decipher the meaning and purpose. I don't fully understand what you mean about "creating named ranges"....but I will do some research and ensure that I acquire that knowleged ... thanks

so this the present coding that have.... but I am still getting an error

1690542451301.png
 
Upvote 0
Try modifying your Button_Click Event code with the following modifications
  1. Only 2 Sub calls in the Click Event code
  2. Two new subroutines to write the data: 1) one based on a sheet name, the other 2) based on the ComboBox (TestBranch) selection.
Both of these save the data using another new Sub "SaveFormToSheet()" - See code below. Replace your current Click event code with the following code
event code, plus the subroutines.

Let me know what happens.

VBA Code: 
Private Sub CommandButton1_Click()
'-----------------------------------------------------------
' 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
' ....
'-----------------------------------------------------------
  SaveDataUsingSheetName "Staffing-Processes" 'write data to "Staffing-Processes" sheet
  
  SaveDataUsingMenuTitle TestBranch.Text 'write data to Sheet linked to Menu (ComboBox) selection
  
End Sub

Sub SaveDataUsingSheetName(sheetname As String)
    
    Set rng = Range("BranchSheetNames") 'from Sheet7 - Staffing sheet names (Col B)
    r = WorksheetFunction.Match(sheetname, rng, 0)
    SaveFormToSheet (r)
End Sub

Sub SaveDataUsingMenuTitle(menutitle As String)
    Set rng = Range("BranchMenuList") 'TestBranch (ComboBox) selections (Col A)
    r = WorksheetFunction.Match(menutitle, rng, 0)
    SaveFormToSheet (r)
End Sub
Sub SaveFormToSheet(row As Long)
  
  Dim wks As Worksheet, wksEngine As Worksheet
  Dim engAddr As String
  Dim TargetRow As Long 'Row in sheet where data will be written
  Dim strRef 'reference string from Sheet7, Column C
  Dim values
  
  Set wks = Worksheets("Sheet7")
  
  values = wks.Range("A" & row & ":D" & row).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" (Row number) and Increment it
  Else
    TargetRow = engVals(3, 1) ' the value "A4" should be driven from Engine sheet for Staffing-Processes.
  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

End Sub
 
Upvote 0
You will need to create two Named Ranges:
  1. BranchMenuList - Range: Sheet7!$A$1:$A$13 - TestBranch ComboBox selections, (menu selections from Col A, Sheet7)
  2. BranchSheetNames - Range: Sheet7!$B$1:$B$13 - sheetnames from Col B, Sheet7

1690550230190.png
 
Upvote 0
Bosquedeguate said:
You will need to create two Named Ranges:
  1. BranchMenuList - Range: Sheet7!$A$1:$A$13 - TestBranch ComboBox selections, (menu selections from Col A, Sheet7)
  2. BranchSheetNames - Range: Sheet7!$B$1:$B$13 - sheetnames from Col B, Sheet7

View attachment 96146
Click to expand...
I copy/paste the code you provided and added the two named ranges but I still get an error

1690554215621.png
 
Upvote 0
Add as first line after the Sub declaration (see if that helps).
VBA Code: 
Dim rng As Range
 
Upvote 0
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
 
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,887
Messages
6,175,199
Members
452,617
Latest member
Narendra Babu D

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