Multiple comboboxes in one userform

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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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
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: 8
Upvote 0
When you post code can you please use the Quick-wrap selection as VBA option on the ribbon.

What are these Combobox controls named?
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
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?
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
Is there any logic between the item selected from the combobox and the range to be copied?
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
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

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
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?
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

Forum statistics

Threads
1,223,896
Messages
6,175,264
Members
452,627
Latest member
KitkatToby

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