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:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  Dim iRow As Long
  Dim iColumn As Long
  
  Dim bNeedNastyMessage As Boolean
  
  Dim sValueColumnA As String
  Dim sValueColumnB As String
  
  'Process only if the cell selected is in Column 'C' thru Column 'P'
  If Not Intersect(Target, Range("C:P")) Is Nothing Then
  
    iRow = Target.Row
    iColumn = Target.Column
  
    'Get the values in Column 'A' and Column 'B' (removing leading and trailing SPACES)
    sValueColumnA = Trim(Cells(iRow, "A").Value)
    sValueColumnB = Trim(Cells(iRow, "B").Value)

    'Identify if Column 'A' or Column 'B' is Blank
    'Put the Focus on the first Blank Column of 'A' or 'B'
    If Len(sValueColumnA) = 0 Then
      bNeedNastyMessage = True
      Cells(iRow, "A").Select
    ElseIf Len(sValueColumnA) = 0 Or Len(sValueColumnB) = 0 Then
      bNeedNastyMessage = True
      Cells(iRow, "B").Select
    End If

    'Display a message if Column 'A' or Column 'B' is Blank
    If bNeedNastyMessage = True Then
      MsgBox "Both Columns 'A' and 'B' must contain data" & vbCrLf & _
             "before data can be entered in columns 'C' thru 'P'."
    End If
  
  End If
  
End Sub

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

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.

Perfect, thank you for your help

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

Try:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  Dim iRow As Long
  
  Dim bNeedNastyMessage As Boolean
  
  Dim sValueColumnA As String
  Dim sValueColumnB As String
  Dim sValueColumnE As String
  Dim sValueColumnF As String
  Dim sValueColumnG As String
  Dim sValueColumnH As String
  Dim sValueColumnL As String
  
  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  'Do Not Allow Data Entry in Columns 'C' thru 'P' if Column 'A' or Column B' is Blank
  'Do Not Allow Data Entry in Columns 'Q' if Columns 'E', 'F', 'G', 'H', or 'L' is Blank
  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
  'Get the Row Number
  iRow = Target.Row
  
  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  'Column 'A' and Column B' Processing
  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  'Process only if the cell selected is in Column 'C' thru Column 'P'
  If Not Intersect(Target, Range("C:P")) Is Nothing Then
      
    'Get the values in Column 'A' and Column 'B' (removing leading and trailing SPACES)
    sValueColumnA = Trim(Cells(iRow, "A").Value)
    sValueColumnB = Trim(Cells(iRow, "B").Value)

    'Initialize the 'Nasty Message' Flag
    bNeedNastyMessage = False
    
    'Identify if Column 'A' or Column 'B' is Blank
    'Put the Focus on the first Blank Column of 'A' or 'B'
    If Len(sValueColumnA) = 0 Then
      bNeedNastyMessage = True
      Cells(iRow, "A").Select
    ElseIf Len(sValueColumnB) = 0 Then
      bNeedNastyMessage = True
      Cells(iRow, "B").Select
    End If

    'Display a message if Column 'A' or Column 'B' is Blank
    If bNeedNastyMessage = True Then
      MsgBox "Both Columns 'A' and 'B' must contain data" & vbCrLf & _
             "before data can be entered in columns 'C' thru 'P'."
    End If
  
  
  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  'Column 'Q' Processing
  'NOTE: This sections gets processed ONLY if Column 'A' and Column 'B' contain data
  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  ElseIf Not Intersect(Target, Range("Q:Q")) Is Nothing Then
    
    'Do Not Allow Data Entry in Columns 'Q' if Column 'E', 'F', 'G', 'H', or 'L' is Blank
    'Get the values in Column 'A' and Column 'B' (removing leading and trailing SPACES)
    sValueColumnE = Trim(Cells(iRow, "E").Value)
    sValueColumnF = Trim(Cells(iRow, "F").Value)
    sValueColumnG = Trim(Cells(iRow, "G").Value)
    sValueColumnH = Trim(Cells(iRow, "H").Value)
    sValueColumnL = Trim(Cells(iRow, "L").Value)
  
    'Initialize the 'Nasty Message' Flag
    bNeedNastyMessage = False
  
    'Identify if Column 'E', 'F', 'G', 'H', or 'L' is Blank
    'Put the Focus on the first Blank Column of 'E', 'F', 'G', 'H', or 'L'
    If Len(sValueColumnE) = 0 Then
      bNeedNastyMessage = True
      Cells(iRow, "E").Select
    ElseIf Len(sValueColumnF) = 0 Then
      bNeedNastyMessage = True
      Cells(iRow, "F").Select
    ElseIf Len(sValueColumnG) = 0 Then
      bNeedNastyMessage = True
      Cells(iRow, "G").Select
    ElseIf Len(sValueColumnH) = 0 Then
      bNeedNastyMessage = True
      Cells(iRow, "H").Select
    ElseIf Len(sValueColumnL) = 0 Then
      bNeedNastyMessage = True
      Cells(iRow, "L").Select
    End If
  
    'Display a message if if Column 'E', 'F', 'G', 'H', or 'L' is Blank
    If bNeedNastyMessage = True Then
      MsgBox "Columns 'E', 'F', 'G', 'H', and 'L' must contain data" & vbCrLf & _
             "before data can be entered in column 'Q'."
    End If
  
  End If
  
End Sub

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