Multiple comboboxes in one userform

Y

yiyirz

New Member
Joined
Jul 21, 2023
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hi

I insert 40 combobox and user has to choose the process and create a flow. From the selection, they can generate and excel with the process flow details paste according to the flow selected. however the printed value does not follow sequence of the combobox. how do i fix this?
here are my codes.

basically i want the select case from combobox one first, then only move to combobox 2 and so on

now the result seems random.

thanks

1689913805664.png
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
yiyirz said:
Hi

I insert 40 combobox and user has to choose the process and create a flow. From the selection, they can generate and excel with the process flow details paste according to the flow selected. however the printed value does not follow sequence of the combobox. how do i fix this?
here are my codes.

basically i want the select case from combobox one first, then only move to combobox 2 and so on

now the result seems random.

thanks

View attachment 95660
Click to expand...
When you post code can you please use the Quick-wrap selection as VBA option on the ribbon.

What are these Combobox controls named?
 

Attachments

  • quick wrap.JPG
    quick wrap.JPG
    61.9 KB · Views: 6
Upvote 0
Herakles said:
When you post code can you please use the Quick-wrap selection as VBA option on the ribbon.

What are these Combobox controls named?
Click to expand...
Sorry i'm new to this forum.

my comboboxes are named combobox1 until combobox40

VBA Code: 
For Each ctrl In Me.Controls
        If TypeOf ctrl Is MSForms.ComboBox Then
            Select Case ctrl.Value
         
                Case "Material Issue"
               
                Sheets("Single").Range("A21:P21").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                
                Case "Dry Film Lamination"
                
                    Sheets("Single").Range("A22:P25").Copy
                    PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                    Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                    
                Case "Exposure"
                
                    Sheets("Single").Range("A26:P29").Copy
                    PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                    Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                
                  
                Case "Develop"
                  
                    Sheets("Single").Range("A30:P31").Copy
                    PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                    Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                  
                   
                Case "Etching"

                    Sheets("Single").Range("A32:P40").Copy
                    PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                    Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
               
                   
                Case "Stripping"
                
                Sheets("Single").Range("A41:P41").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                   
                Case "PTH"
                
                Sheets("Single").Range("A42:P42").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                 
                Case "AOI"
               
                Sheets("Single").Range("A45:P47").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
               
                  
                Case "Target Punch (CVH)"
               
                Sheets("Single").Range("A48:P49").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
    
                    
                Case "Lay Up Bonding Film"
                    
                Sheets("Single").Range("A50:P54").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
            
                Case "Lay Up Coverlay"
                
                Sheets("Single").Range("A55:P57").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                
                Case "Coverlay Lamination"
                
                Sheets("Single").Range("A58:P62").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                   
                Case "Kiss Lamination"
                
                Sheets("Single").Range("A63:P65").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                    
                Case "Lay Up Stiffener"
               
                Sheets("Single").Range("A66:P71").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
        
                Case "Stiffener Lamination"
                
                Sheets("Single").Range("A72:P75").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                    
                Case "Baking"
                 
                Sheets("Single").Range("A76:P77").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
        
                    
                Case "Legend Printing"
                
                Sheets("Single").Range("A78:P81").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                    
                Case "Target Punch 2 (TH)"
               
                Sheets("Single").Range("A82:P83").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert

                 
                Case "Post Treatment"
               
                Sheets("Single").Range("A84:P85").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
       
                  
                Case "Electroless Nickel Immersion Gold"
             
                Sheets("Single").Range("A86:P88").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                    
                Case "Electrolytic Nickel Gold Plating"
            
                Sheets("Single").Range("A89:P91").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                
                    
                Case "Lay Up PSA"
                
                Sheets("Single").Range("A92:P93").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                Case "Hot Roll"
               
                Sheets("Single").Range("A96:P96").Copy
               PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                
                Case "Electrical Test"
              
                Sheets("Single").Range("A97:P97").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                    
                Case "Subpanel"
                
                Sheets("Single").Range("A98:P100").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                    
                Case "Piercing"
                
                Sheets("Single").Range("A101:P103").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                Case "Tie bar"
                
                Sheets("Single").Range("A104:P107").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                Case "Kiss Cut"
                
                Sheets("Single").Range("A108:P111").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                Case "Outline"
                
                Sheets("Single").Range("A114:P117").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
               
                    
                Case "AX OSP"
                
                Sheets("Single").Range("A118:P118").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                
                Case "Impendance Test"
                
                Sheets("Single").Range("A19:P24").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                Case "Visual Inspection"
            
                Sheets("Single").Range("A127:P125").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
    
                   
                Case "Outgoing Inspection"
                    
                Sheets("Single").Range("A128:P131").Copy
               PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
        
                
                Case "Packing"
                
                Sheets("Single").Range("A32:P33").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                    
                Case "Finish Good Store"
                
                Sheets("Single").Range("A34:P34").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
            
                Case "Panel Cutting"
                
                Sheets("Single").Range("A43:P43").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                    
                Case "Panel Measurement"
                
                Sheets("Single").Range("A44:P44").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                
                Case "Masking"
                
                Sheets("Single").Range("A94:P94").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                
                Case "Unmasking"
                
                Sheets("Single").Range("A95:P95").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert
                
                Case "Piercing (Laser)"
                
                Sheets("Single").Range("A112:P113").Copy
                PasteCell = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Worksheets("Template").Range("A" & PasteCell).Offset(1).Insert

            End Select
        End If
    Next
 
Upvote 0
I assume that you want to loop through them in the order 1 to 40.

Is there any logic between the item selected from the combobox and the range to be copied?

How are you going to stop someone from making a selection twice and copying the data twice?
 
Upvote 0
Herakles said:
I assume that you want to loop through them in the order 1 to 40.

Is there any logic between the item selected from the combobox and the range to be copied?

How are you going to stop someone from making a selection twice and copying the data twice?
Click to expand...
yes yes correct. i want to loop them in order.

no restriction. they can select same thing twice, just want ti to be in selection order
 
Upvote 0
Is there any logic between the item selected from the combobox and the range to be copied?
 
Upvote 0
Herakles said:
Is there any logic between the item selected from the combobox and the range to be copied?
Click to expand...
sorry if i understand the question wrongly

based on the item select in combobox, i will just copy the data which is written in another sheet named "single" into the sheet named "template".

just copy paste directly. basically they choose which/what to be copied into the template in a sequence/order
 
Upvote 0
yiyirz said:
sorry if i understand the question wrongly

based on the item select in combobox, i will just copy the data which is written in another sheet named "single" into the sheet named "template".

just copy paste directly. basically they choose which/what to be copied into the template in a sequence/order
Click to expand...

The data to be copied depends on what item is selected e.g. Sheets("Single").Range("A78:P81").Copy

Can this range be determined by applying a logic in code rather than hard coding it?
 
Upvote 0
Herakles said:
The data to be copied depends on what item is selected e.g. Sheets("Single").Range("A78:P81").Copy

Can this range be determined by applying a logic in code rather than hard coding it?
Click to expand...
I'm not sure, but i guess no...
 
Upvote 0
So how have you determined which ranges to copy?

There must be a logic to your decision.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Check one Combobox value against another Combobox value
Replies
6
Views
242
Pacman52
P
P
  • Solved
ComboBoxes - check if a value is selected.
Replies
3
Views
302
dreid1011
dreid1011
T
  • Question
Refer to a sheet selected by combobox
Replies
2
Views
96
dmt32
D
B
  • Solved
Populating combobox from table based on the header selected in another combobox (some data on different sheets)
Replies
7
Views
254
BWL388
B
C
  • Solved
Excel VBA Userform multi-select listbox (edit/update)
Replies
5
Views
140
cerberus1845
C

Forum statistics

Threads
1,215,133
Messages
6,123,234
Members
449,092
Latest member
SCleaveland

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