Procedure Too Large

[ScherzoVivo]

So I work at a hotel and have written a relatively simple spreadsheet for assigning rooms to housekeeping (our software has a terrible interface for it).

Anyway, the spreadsheet was working perfectly until we hired an additional housekeeper and I had to add additional code to include her. Problem is that now I'm getting a "Procedure Too Large" error. And it makes perfect sense. I definitely programmed this the LONG way, but I'm still trying to learn the more advanced code that would probably solve this issue.

Anyway, hopefully someone can give me some pointers on what to do!

[MRUTOR]

Hello,

replace your long code where you write the whole code for each room with following which is using an For loop. You can do it like this since the line numbers are continuously.
Try to use variables.

Please Login or Register  to view this content.

[ScherzoVivo]

This is fantastic! Thanks a ton!

[torachan]

I put this to one side as a rainy day challenge as it was a OMG moment when I saw all that hard coded naming.
If you look at the attached file I have not named the captions of any of the option buttons.
To save you the arduos task of naming 200 option buttons and their relevant coding I have used a naming sequence that will enable you in future to do this with one entry.
All the option buttons follow a i.d. sequence (opt_100_1) where the '100' is the room number and the '1' is the column of the housekeeper header in Sheet3(Sheet1).
If housekeepers change all you have to do is change the header name and ensure the individuals sheet tab is spelled the same (case sensitive).
The only other alteration made was to the 'Unassigned' sheet, now named 'N_A' to align with the 'header' and 'option button' caption.
Not able to use '/' in tab naming as it is a reserved character.
Any questions just ask on this site.
torachan.