Too Much Code

Hi flahsdisk,

I have a golf spreadsheet where I enter hole strokes into a userform. Here is the code that uses an INDEX number of the controls on the userform to add up the first 9 holes scores.

Please Login or Register  to view this content.

The idea is that each control on a userform has an index number. So you do a loop through the index numbers to add stuff up. Hope this example helps.

The idea is that each control on a userform has an index number. Instead of asking for the name of each one you can use this control index number and simply run through all of them in a single loop. You should attach your userform to a workbook and post it with your thread so we can see exactly what you have. Then it might make more sense to both of us.

To attach a file, click on "Go Advanced" and then on the paper clip icon above the advanced message area.

Hi flashdisk,

You have a very involved userform. Type the following into the immediate window while looking at your userform code and see what happens.

Please Login or Register  to view this content.

What I'm trying to get to is using the INDEX of the control instead of the name. If you can figure out what all those controls indexes are you might loop through them and not need to know their names.

BTW - I can't run your code as I'm on 2013 VBA and missing some of the reference libraries needed to run your code. Sorry...

Hi flashdisk,

Sorry but I don't have the Calendar Control on my machine. Your code crashes each time I get to a calendar command.

Hi flashdisk,

I've asked others, who have Excel 2003 to help with this question. Your Userform is very elaborate and almost beyond the scope of most of us here. Let's see if another guru will come to our rescue.

Hi flashdisk

What version of Excel are you using. You've posted an .xlsm file but your profile shows Excel 2003.

I haven't looked at the file yet but you can put the code into a separate routine and call that from each textbox rather than replicating the code

you could also simplify by creating a separate calendar form instead of using multiple calendar controls with mostly the same code and validations

add a separate routine

Please Login or Register  to view this content.

and in each textbox's exit event add

Please Login or Register  to view this content.

no that has to be in the userform

Hi flashdisk

If JosephP's solution works PLEASE let us know.

I've looked at your User Form...still trying to understand...you've a lot of "stuff" going on.

First blush...I'm fairly confident a Class Module (one procedure to handle all the requisite TextBoxes) can handle all those TextBox Change Events (and perhaps other elements of your Code).

I'll look at it a bit...get back to you.

May need to rename TextBox12 and TextBox13...save me time looking...do you have a TextBox20 or a TextBox21?

AGAIN, if JosephP's solution works PLEASE let us know...don't wish to beat a dead horse.

Tested the code now but i get a run time error on the line below when exiting textbox31

Please Login or Register  to view this content.

@Jaslake

Looking through no i don't have any textbox's named textbox21 or textbox20

But yes there is alot of code which is why im now looking at ways to reduce it

what error specifically?

Run-Time error '13':

Type Mismatch

Edit: have attached test workbook with your test code

typo

Please Login or Register  to view this content.

has a superfluous comma-it oughta be

Please Login or Register  to view this content.

yes-it will run with every keystroke in those textboxes if you do

my pleasure :-)

you still have a lot of code in there though!