How to correct procedure too long in VBA

Question

I finally fixed all my problems now the procedure is too long.
I've seen other fixes on this site but there a little vague.
My code starts as such:

Private Sub ComboBox6_Change()

If ComboBox1.Value = "CONVERTING" And ComboBox2.Value = "SHEETING" And ComboBox3.Value = "LITHO" And ComboBox4.Value = "LESS THAN 50BS" And ComboBox5.Value = "WIDTH > 46in" And ComboBox6.Value = "LENGTH > 60in" Then
UserForm1.ListBox1.AddItem "SHEETING (LITHO, LESS THAN 50BS, WIDTH > 46in, LENGTH > 60in) FG $0.0389/LB"
End If
If ComboBox1.Value = "CONVERTING" And ComboBox2.Value = "SHEETING" And ComboBox3.Value = "LITHO" And ComboBox4.Value = "LESS THAN 50BS" And ComboBox5.Value = "WIDTH > 46in" And ComboBox6.Value = "60in >= LENGTH > 22in" Then
UserForm1.ListBox1.AddItem "SHEETING (LITHO,  LESS THAN 50BS, WIDTH > 46in, 60in>= LENGTH > 22in) FH $0.0318/LB"
End If

And so on... I have 6 different comboboxes any help would be appreciated.
Thanks

????? You could look at grouping the ones that are the same, like `If ComboBox1.Value = "CONVERTING" And ComboBox2.Value = "SHEETING" And ComboBox3.Value = "LITHO" And ComboBox4.Value = "LESS THAN 50BS" And ComboBox5.Value = "WIDTH > 46in" then` then another if after, but not sure what you are asking... — Nathan_Sav, Nov 28 '17 at 14:32
Possible duplicate of [Getting error Procedure too large in VBA Macros (Excel)](https://stackoverflow.com/questions/11450232/getting-error-procedure-too-large-in-vba-macros-excel) — ashleedawg, Nov 28 '17 at 14:34
In all my years I have never seen this error. I guess I just factor code and modularise it. — S Meaden, Nov 28 '17 at 14:49
Is this an error message you are receiving? The code above is not too long. Confused. What is your specific question? — QHarr, Nov 28 '17 at 15:22

Harassed Dad · Answer 1 · 2017-11-28T18:06:54.463

1

I think you could use

 Private Sub ComboBox6_Change()
 Dim s as string
 If ComboBox1.Value = "CONVERTING" then
      s = ComboBox2.Value & "(" & ComboBox3.Value & "," & ComboBox4.Value
      s = s & "," &  ComboBox5.value & "," & Combobox6.value
      Select case ComboBox6.index
           case 1
               s = s & ") FG $0.0389/LB"
           case 2
               s = s & ") FH $0.0318/LB"

etc

edited Nov 28 '17 at 18:06

answered Nov 28 '17 at 15:25

Harassed Dad

4,669
1
10
12

I really like your answer but have you noticed that you have two IFs? Also you didn't include the UserForm1.ListBox1.AddItem s – Xabier Nov 28 '17 at 15:31
whoops! Thanks for spotting that. I've edited out the two if's – Harassed Dad Nov 28 '17 at 18:07
Thank you for your answer I will try this and let you know how if it works. – Dennis Nov 29 '17 at 14:18

How to correct procedure too long in VBA

1 Answers1