Guidance for VBA Data entry

When you design a project you should divide your logic clearly into cause, condition and effect. Each division requires its own coding.

• You did identify the condition: When A1 = "Bob"
• You have identified the effect: Populate some cells in Sheet2
• You haven't considered the cause: How will your macro be called?

Of course, the way to call a macro doesn't appear important before you have it, just as the way you travel to Tahiti isn't important before you decide to go there. But if you were counting on taking a bus or train, or perhaps buy a motor cycle, you are building hurdles into your plan even before you have made it. Same for programming. You should know whether it will be a button, an event or a UDF. This depends upon your work flow and affects the usefulness of what you plan for.

With that decison made, or sketched out at least, you turn toward the effect. You can make a very detailed plan for what you will do in Tahiti, including how to get there, and then add the condition, "if I have the time and money" later. It's the same for the effect of your programming. Sheet1 will have to be designed the same - but perhaps not quite the same - regardless of whether you enter Bob, Tom or Mary in A1. Same for Sheet2. The cells to receive the data have to be prepared (formatted) and, perhaps most importantly, the data to be used if Bob, Tom or Mary is selected in Sheet1!A1 must be ready in machine-readable format. The same rule applies - in spades - to code as to worksheet functions: data belong in the sheet, not the code. The latter just manipulates them. So, where are the lists of data? Perhaps a Sheet3 with a row for each of Bob, Tom, Mary and others from which you can retrieve data with VLookup?

Which, of course, gets you to the point of deciding whether or not you need VBA at all. Obviously, if all you need is VLookup to get data from your database to a form in Sheet2, then why bother with VBA? I presume that you have actually thought about this already. Therefore the point I want to make is that in the process of designing the dataflow you often find better solutions not recognizable before. But, assuming that there is no other way to go but forward, now is the time to start your macro recorder. It will set you on your way to get code that does the actual work. If you have questions along the way, publish your code here together with any question you might have.

Once the action code is ready you can make its running conditional in both the mechanical and practical senses. Make the code do the same thing differently depending upon the contents of Sheet1!A1 and never do anything until called upon - by a button or an event or by a worksheet calculation.