If Statement to stop Dim code from running

[rlh]

Attached is an example of the program. The purpose of this program is to allow someone with very little knowledge about the information to enter data. The first tab labeled "Instructions" is will have two macros assigned to the buttons at the bottom of the page. The first macro labeled "Move Data" will move data will clear data in the shaded cell on the "Entry Form" sheet and fill those cells in with '----. The second macro labeled "Generate and Save" will save a master copy of the program, save a historical copy of the program by date, and print/save a pdf copy of the "Reports" sheet.

Each one of the historical (the last 8 sheets of the program) sheet receives data from the "Entry Form" and then moves the data by date into the table for archiving purposes.

If you look at the code for the "Entry Form". I have a couple of subs. The first being Worksheet_change. the first section of this code is to force certain cells to be uppercase. This part of the code is working fine. The second part of the code is the Dim MyValues. This part is working fine also, but...

My issue is... When I run the move data macro from the "Instructions" sheet the Dim MyValues reads these as changes to the cells and wants to push the data to the historical sheets.

What I would like is an IF Statement above the Dim MyValues part of this code that stops the Dim MyValue part of the code running if those values are blank, 0, or have '---- entered into them.

Thanks for any help in advance

Test.xlsm

[mc84excel]

The buttons on the Instructions form don't work. Was this removed from the Workbook before uploading?


Some (unrequested) advice:

1. You don't need to have the user enter Thursday date - you could use this formula

   Please Login or Register  to view this content.

2. Never use tab names to refer to worksheets in your code. Always use the sheet code name instead.
3. Use With...End With. Your code will be easier to read and run more efficiently.

[rlh]

Yes, I removed the macros from the Instructions page. They are not relevant to my question. I referenced them in the original post so you experts could see the full scope of what I am trying to do. Thank you for the advice on the date formula. As you can tell by the code in this program it is basic. I am still trying to learn how to and the best way to do things. I will make those changes you suggested in 2 and 3.

Can you or someone help me out with the if statement? Below is the code... I have put a statement where I would like the code to be.

Please Login or Register  to view this content.

[TMS]

You would make life a lot easier for yourself if you gave your variables brief but meaningful names to reflect the data that is in them, or going to go in them.

You don't need, for example:

Please Login or Register  to view this content.

If you are storing the value if a single cell, you just need:

Please Login or Register  to view this content.

But how you remember which is which ... oh, yes, you repeat the comment everywhere you use it. Seems like hard work.

Dim only defines a variable. It's the later statements that use them to store data or accumulate totals or whatever.

After saying all that, I'm not sure I understand what you want to do. If I understand correctly, you are storing the data from one sheet into the variables and then you are copying/moving it to other sheets. When you change the data on the other sheets, it fires the Change Events on those sheets ... so you need to use EnableEvents = False and EnableEvents = True before copying the data to the other sheets. That will stop the event handler firing.

Regards, TMS