#REF error that I can't figure out

I'm really stuck on this one. I have an error that consistently repeats, and I'm not sure how to keep it from happening. I'm not so sure it's anything with the code, but rather something with the way Excel runs.

The file that contains this error is attached. In a nutshell, the program creates a graphical time schedule by selecting a persons name. It does this by searching through a database of information and copying every instance that is associated with that name. The code then selects the block of time through a range of cells, merges the cells, and pastes the course information in the cells. You simply choose another name to create a schedule for someone else (and the program in-between unmerges and clears the cells, etc.). The user can then click on any empty cell to fill it in as another type of hour (office, online, etc.). When you open the file it opens to a page with instructions on how to use it. It works perfectly every single time with the exception of one specific name. To create this error, simply go to the "Graphical Schedule" tab, choose the name 'Douglas Mabry' and click "Create Schedule". I would recommend you choose a couple of other names first to see how the software works. Once you've chosen the name that gives the error, you'll see that the error is a #REF error in the total hours areas. These errors are being carried over from another worksheet called "Office Hours", and although I hide this sheet typically I left it visible so it would be easily found. There are several other worksheets that can be "unhidden" if needed.

The "Office Hours" worksheet is the sheet that tallies the total hours other than classes (such as the above-mentioned office, online and so-on). Thus it directly references the cells on the "Graphical Schedule" worksheet. Now... as mentioned before, some of the cells on the "Graphical Schedule" worksheet get merged, and I could see where this might throw a #REF error (for instance, if cells A14:A17 are merged and a formula was referencing A16, would that throw a #REF error?). However, this doesn't happen with any other name you choose, and they reference cells that are "merged away". So why do I get the #REF error for this user?

Would the easiest thing be to create some type of error handling? Right now, if that name is chosen, the #REF errors are there to stay. You have to close and reopen the spreadsheet to be able to create schedules for another user (assuming you didn't save the file with the #REF error, in which case you're really stuck).

NOTE--the worksheets automatically password protect themselves to keep someone from accidentally overwriting formulas. I also protect the workbook. The password to unprotect any of the sheets, VBA code, etc., is 5,4,3,2,1 (just the numbers... no commas).

6String--

I'm not sure why the password isn't working for you... wait! That password WAS different so I've unprotected it and will re-upload the file here. Also, I didn't protect the workbook, so it's already unprotected. As for the protections and unprotections, I've never made something like this before, but every time I have to do anything it's a real pain. I realized the need for such a developer utility!

What other options could I use instead of merging cells? I tried using textboxes instead, but even with code pulling them to the corners the alignment didn't look the greatest. Is there some way to make them look merged without actually merging them?

OK here's your problem. Mabry has two classes that overlap the same half-hour slot on Thursday from 8:00-8:30. When you create the second one on the schedule, the merging gets all screwed up because your merged range overlaps into another range that is already merged. That causes the REF errors on your Office Hours sheet, which ripples back into your schedule sheet.

First class

"AHR 114 D02
8:15 PM -9:35 PM
PTI 123"

Second class

"AHR 210L D02
6:30 PM -8:07 PM
PTI 119"

You cannot center text vertically without merging, but if you merge you are going to get problems like this. Pick your poison.

Personally I would avoid merging. If you always use at least three rows for each class, use those for the three lines of text. Show the block with a color and border.

If you merge you are going to have to have a check in your code for an overlap like this. Then unmerge the top one, and remerge it without the bottom cell. Then proceed to merge the secodn one.

There was code to handle when classes overlap:

Please Login or Register  to view this content.

I can't honestly say I completely understand exactly what the code does, but someone on the forum suggested this code due to merged areas combining when there was an overlap. Hmmmm... I guess I could always check the database to make sure that times don't overlap. Perhaps have one set of times that are put into the cells, and another set that will be rounded and used to set which cells are highlighted that make sure they don't overlap.

As for # of cells for classes, many classes just use two cells--not three. I'll have to think on this and see how to approach it. It's unfortunate that you can't vertically "align text over range of cells", but it is what it is. It's also unfortunate that text boxes don't align perfectly to a cell range. Any specific suggestions on how to handle this would be welcome!