How to create a loop?

I have a portion of code from a workbook with pages and pages of code that interacts with several other workbooks.

In this section of code for a worksheet, I have 2 cells and people input code values of defects in either 1 or both cells. The cells are populated by pushbuttons on a userform.

The codes individuals can choose are a predetermined list on the userform... and they can only fill out the worksheet with up to 2 codes.

the prepared worksheet (after user form code is process) is copied and that page with the choosen codes entered is sent downstream via outlook.

Hopefully that all makes sense.

The issue I have is that that I was brand new to VBA when I first wrote the code and now I have more experience I think this can likely be done with less code.
There are 32 code options and each option is coded as below with just changing the number from 1 to 32 ... can't I create something called a loop to make the code not be so extremely long?? Each of the "subs" is a seperate click button ( i.e. 32 clickable buttons for this portion of code on the userform)

Please Login or Register  to view this content.

I tried to read through the link and googled the concept but was pretty lost. I am pretty much self taught by just monkeying around with code examples from net examples and books and seeing others code etc. I haven't ever had a computer class or anything really.

It's hard to do an example file because this one is so huge. I tried to purge the majority of the code and only keep revelant information. The file is kind of buggy but I hope the intent is there enough that my question of how to streamline the code somewhat can be answered.

Hello LiLi1,

An alternative to the Class module is create a single Sub that can be used by all of the buttons. The code below replaces the button code you now have.

The way it works is by extracting the buttons sequence number (1 to 32) from the button's name. This value is then inserted into the appropriate error box.

Please Login or Register  to view this content.

Hello LiLi1,

The changes I made will not significantly impact the execution time of the macro. It does however condense the code and make it easier to manage.

No relation to the pet website. Leith is not a common name in the US. There are only about 4,000 people who have the name as forename or surname. The numbers are higher in Scotland.

Hello LiLi1,

In VBA is it seldom necessary to select an object before performing an action on it. For example, changing the value of a cell.

Please Login or Register  to view this content.

There are other ways to do this also. This is just one that will make a difference in execution speed.

Hello LiLi1,

RegExp is short for "Regular Expression". This is powerful scripting language that is used for string parsing and pattern matching.

Hello LiLi1,

When referencing an object, like a Range, on a worksheet, if the object is not qualified then the active sheet is assumed.

Let's say that "Sheet3" is the ActiveSheet.

Please Login or Register  to view this content.

This will clear the contents of "A1:A10" on "Sheet3". If the user changes sheets before the macro is run to "Sheet1" Then he macro will clear the contents of the cells "A1:A10" on "Sheet1".

To ensure that only the cells "A1:A10" on "Sheet3" are cleared, the code can be written this way. By making the code reusable, your code will be easier to read, maintain, and provide you with more options. If you perform a function 3 or more times in your code, you should consider making it a separate procedure.

Please Login or Register  to view this content.

Hello LiLi1,

I don't know what i was thinking. Change the ErrorCheck routine to what is below.

Please Login or Register  to view this content.

I am begining to think I am going to qualify for longest thread ever so I hate to even ask .. but that takes care of reduces the code for the displaying the box code what about the coloring them part? Can I also get rid of typing the code 32 times for the color? The color used to "highlight" the cells is always the same yellow the only thing in this portion of code that changes is where the 10 is currently it goes from 1 to 32. These are not individual subs ... it's the form where it pastes the info from the cells... so all the code just runs one after the other ... perhaps I really have stumbled onto a true loop question LOL ... it's amazing how much you can learn in each thread I post. I feel as if I have come full circle

Please Login or Register  to view this content.

Hello LiLi1,

The cells color can be changed by using the DisplayBoxCodes as an index into a Collection object. This allows for random access to the cells. This is much faster than testing each condition.

The collection is declared Public in the UserForm. The UserForm_Initialize event loads it up with the cells and the DisplayBoxCodes (1 to 32). When SendRFTEmail is clicked, the textbox values are checked and the appropriate cell is colored. The attached workbook has all the changes made.