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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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: 8
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 Question
Check one Combobox value against another Combobox value
Replies
6
Views
506
Pacman52
P
J
  • Question Question
Filter ListBox on a UserForm using a ComboBox
Replies
1
Views
387
DanteAmor
DanteAmor
M
  • Question Question
sum or subtraction based on selected item from combobox on userform
Replies
3
Views
212
Mussa
M
V
  • Question Question
Userform, combobox: Repicking the same selection?
Replies
2
Views
115
vrod2000
V
R
  • Question Question
ComboBox Only Recognizes Text Items When Selecting a Value To Populate ListBox
2
Replies
10
Views
697
Artik
Artik

Forum statistics

Threads
1,224,823
Messages
6,181,169
Members
453,021
Latest member
Justyna P

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