How can I make multiple cells mandatory if new rows are used with some cells left blank?

Hi All

I have a workbook, with multiple sheets, in one of those sheets however I wish to make Cells in Column A & B mandatory assuming data has been entered into a new row in any of the other corresponding cells ( C through P)

It would also then be useful if a message (as a reminder) was presented to inform users that data must be added to Columns A & B.

I am relatively new to VBA/ Macros, having done some basic routines, I have looked at this one for hours/ days and have been unable to progresss so far.

Thank you all in anticipation of your help

Mark

Hi markbarnett and welcome to ExcelForum.

The ideal solution would be to lock out columns 'C' thru 'P' until both 'A' and 'B' contained data.

Please let me know if you have any questions or problems. If you need a different solution, please let us know what you have in mind.

Try the following code in the 'Sheet module" of the sheet where you do data entry:

Please Login or Register  to view this content.

To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. Dim i as Integer). http://www.cpearson.com/excel/DeclaringVariables.aspx

Debugger Secrets:
a. Press 'F8' to single step (goes into subroutines and functions).
b. Press SHIFT 'F8' to single step OVER subroutines and functions.
c. Press CTRL 'F8' to stop at the line where the cursor is.
d. 'Left Click' the margin to the left of a line to set (or clear) a BREAKPOINT.
e. Press CTRL 'G' to open the IMMEDIATE WINDOW. 'debug.print' statements send their
output to the IMMEDIATE WINDOW.
f. Select View > Locals to see all variables while debugging.
g. To automatically set a BREAKPOINT at a certain location put in the line:
'Debug.Assert False'
h. To conditionally set a BREAKPOINT at a certain location put in lines similar to:
if i >= 20 and xTV20 > 99.56 then
Debug.Assert False
endif
i. A variable value will be displayed by putting the cursor over the variable name.

Lewis

Perfect, thank you for your help

Hmmmmm, sorry to ask again, in a second sheet - Work complete, once Column A&B are complete, using the same code as previously, I then would like to check that columns E,F,G,H,L are complete before column Q can be entered into.(other columns are not as important, after the initial entry)

To clarify then:
In order to complete work the user will enter work details in A&B, this will be mandatory (using code as above) then other columns should be used to log work/ handover details and various training dates etc, however prior to final sign off (Column Q) all of columns must be complete.

thank you and sorry.

No apologies are needed. Followup questions are allowed and encouraged.

Try:

Please Login or Register  to view this content.

Lewis

Fantastic, works a treat, thank you

Sorry to be a nuisance I have given this some further thought, as in two of the sheets the data is the same, sheet 1, entry sheet Columns A&B, then C&D will all appear in sheet 2 - completion sheet (C&D assuming A&B have data entered)
I now need if it can be done that A in sheet 1 will populate A (next available row) in sheet 2. Likewise B will populate B in sheet 2. C&D will likewise populate C*D in sheet 2.

Secondly I have a further issue, I had previously created a form to allow users to add new records quickly, so whilst if entering the sheet manually you must enter data in A&B prior to any of the other columns, if you were to enter data via a new record on the form, and or all of the fields are free, thus allowing a user to add entries into c,d,e etc and not A&B, when I have tested this if you then try and delete these records you can't without putting dummy data into A&B, so my question is, can the mandatory fields be added into forms, or should I just remove the option to populate in theis way?

Thank you

so my question is, can the mandatory fields be added into forms, or should I just remove the option to populate in this way?

What we have done so far is create a system based on rules that you created. Sometimes rules have unintended consequences.

Having said that, my personal belief is that rules are good. If you want, upload a sample workbook that contains desensitized sample sheets. Then, assemble a set of rules for data entry for each sheet. I'll see what I can come up with. It may take a few days after you upload the file.

To post a sample file, click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic

Lewis