VBA Select Case error: procedure too large

[angela022687]

Hi,

I have 30 different cases working well in one module so far. Now I need to add some more cases, but the system doesn’t allow me to start defining any new cases under Select – Case, it shows: compile error: procedure too large.

I’m thinking maybe there’s a way to combine multiple cases like Case 30, 31, 32 in one statement, and use if, else to define each one?? So the coding won’t return the “Procedure Too Large” error? see my original coding as below:

counter = 0
colkey = 0
For Each cont In PriceForm.HaveFrame.Controls
If TypeName(cont) = "CheckBox" Then
counter = counter + 1
check = cont.Value
If check = True Then
colkey = colkey + 1
Select Case counter
Case 1
have.Add Item:="Product 1", Key:=CStr(colkey)
Case 2
have.Add Item:=" Product 1", Key:=CStr(colkey)
……………
Case 31
have.Add Item:=" Product 31", Key:=CStr(colkey) '''After I added this case, it returned” Procedure Too Large” error
End Select
End If
End If
Next cont

Does anyone know what’s the problem here?

Thanks in advance!

 

[Norie]

Do you really need the Select Case?

Every case appears to be doing the exact same thing.

 

[DrSynapse]

For this section...

If TypeName(cont) = "CheckBox" Then
counter = counter + 1
check = cont.Value
If check = True Then
colkey = colkey + 1
Select Case counter

Can you try using an else ?

If TypeName(cont)="CheckBox" Then
     counter=counter+1
else
     colkey=colkey+1

But maybe I've misunderstood exactly what you mean. I had this error as well at first and I think you're on the right track by trying to use else statements and loops. Maybe another user can provide more feedback!

 

[angela022687]

Do you really need the Select Case?

Every case appears to be doing the exact same thing.

Thanks Norie. Yes, I think Select Case is needed, because the purpose of this VBA is to automate the product price quote process. I have a UserForm built in the VBA as well, with the check box for each product(1,2..31), so after choosing the products that customers want in the pop-up UserForm, it returns to the price quote. I think now VBA prob will return to error anyway if I define and add more cases, so I'm trying to combine this new case with the previous one.

 

[angela022687]

For this section...

If TypeName(cont) = "CheckBox" Then
counter = counter + 1
check = cont.Value
If check = True Then
colkey = colkey + 1
Select Case counter

Can you try using an else ?

If TypeName(cont)="CheckBox" Then
     counter=counter+1
else
     colkey=colkey+1

But maybe I've misunderstood exactly what you mean. I had this error as well at first and I think you're on the right track by trying to use else statements and loops. Maybe another user can provide more feedback!

Thanks DrSynapse. I tried using the loop you provided, it returned the same error. It's a nice thought though. I think I need to figure out a way to use if else in the Select Case and other case related coding in the module to avoid adding more cases.

 

[Norie]

You appear to have this code for each case so I can't see why you need the Select Case.

have.Add Item:="Product 1", Key:=CStr(colkey)

Even if the number following Product is different for each case I don't see why you need the Select Case.

You could probably replace it with this.

have.Add Item:="Product " & counter, Key:=CStr(colkey)

 

[DrSynapse]

This may be a silly suggestion, but are you applying the same thing to each case? If so, why not apply multiple cases?



Select Case counter
Case 1 to 31
have.Add Item:="Product name", Key:=CStr(colkey)
End Select

The product name would be the only tricky part for a newbie like myself, but I'm positive it's doable!

 

[angela022687]

You appear to have this code for each case so I can't see why you need the Select Case.

have.Add Item:="Product 1", Key:=CStr(colkey)

Even if the number following Product is different for each case I don't see why you need the Select Case.

You could probably replace it with this.

have.Add Item:="Product " & counter, Key:=CStr(colkey)

Thanks. Actually, there's multiple product names, followed by different numbers, for example, shirt1, shirt2, shoes1, shoes2, bagA, bagB. Guess I'll still need the Select Case for each product line? Also, all of the cases have identical coding in the later part of the module, so based on which case you select, the relevant codes will run to finish the action. If I don't use Select Case, not sure how I'm going to redo the rest of the coding.

 

[Norie]

Do you have a checkbox for each product?

If you do why not use a multiselect listbox?

 

[angela022687]

Do you have a checkbox for each product?

If you do why not use a multiselect listbox?

Yes, I have a checkbox for each product. Hmm, I've never tried multiselect listbox before... can it solve my current issue? what's good about using it?