How can 1-Msgbox be used in place of Many?

Hello everyone;

Does anybody have any ideas hoiw to simplify this lengthy macro?

In an attempt of using 1-msgbox looking at a range of cells, I ended up with many message boxes with each one looking at a seperate cell.
They are intended to prevent posting an incomplete record by flagging any blank cells in the intended range.

Some cells use Drop-down list boxes and some require typing something in the cell; so it is hoped that the same msgbox can be used for all of them.

As written, they all have seperate titles that identify the cell at fault but can share the same generic tile such as "Cell is Empty" and the message might read "Input or Select a value and try again"

Please Login or Register  to view this content.

As can be seen, the cells that need some kind of data are sequentially-listed in the same column and as such, it was hoped that a range specifier could be used like "AJ7:AJ18"

When all cells have data, the macro resumes execution by posting the new record.

Any ideas on this one will be greatly appreciated;

Thanks
54Libra

Just a quick thought/suggestion:

Please Login or Register  to view this content.

(I'll have time to think slower after work.)

This is one way to do the data entry part. The headerArray needs to be ajusted to match your current code. If you need them to choose from a dropdown, the "department" inputbox can be bypassed.

Please Login or Register  to view this content.

Hello there mikerickson

Thanks for the kick-back and the code

It looks like it will work well but am not sure where to call it from in my macro in order for it to exit intelligently; by this, when the Cancel button is used, it should abort the post request without posting a blank record in favor of allowing the user another chance. I don't see how to avoid this.

I plan to call the new code from a module instead of making it part of the existing macro. I figured it will be more logical this way.

Thanks again but I need some guidance.

I think my gyros are off or something...

Thanks again

I would replace the indicated section with the code I posted. As written, Cancel will exit Sub copy_emp1 before any copying.

Please Login or Register  to view this content.

I was looking at your protection scheme. Are AJ7:AJ18 locked? If so, coding would be easier if you set UserInterfaceOnly to True. (Actualy even if they are unlocked, coding is easier with UserInterfaceOnly = True.)

Hi again mikerickson

Thank you for the code; when I get a chance later, I will try it out but for now I just wanted to clarify a couple of things

The cells AJ7:AJ18 are unlocked and the rest of the the sheet is. It is a scheme I thought of doing that allows access only to the cells in use at the moment. You could hit the enter, tabs or the arrow keys all-day-long and the cursor will stay within only those unlocked cells.

As soon as macros are invoked however, sheet protection is removed and the data gets manipulated freely; except in critical areas, where sheet protection is enabled momentarily to protect formulas near where data gets pasted. It is a method I use to prevent dumb mistakes from happening. Some of the formulas I use are kind of lengthy and I would hate to have to re-write them because of a missentry or something.

Well, there you have it; the methods to my madness.

For now, I have to take care of some other business; I'll write back and let you know how the code works out.

Thank you again

54Libra

Rather than unprotecting, check the help system about the UserInterfaceOnly argument. If its set to True, VB can alter a sheet even though the user (keyboard and mouse) can't.

Hello mikerickson

Thanks for that tip. It is odvious I have a lot to learn about Excel.

Remember Lotus? It was logical and it made sense. Although the last time I used it was in DOS, it worked well enough to get me a couple of promotions; but that was in another life.

Thanks again; after I tie up some more loose ends, I'll tackle my project again and see how your code works.

Until then; Cheers
54Libra