Make multiple cells mandatory if another cells (text) in row is filled

[Dieguete]

Hi all,

I need some help here.

I need an error msg to prompt out and prevent user from saving the file two particular cells are not filled in when another one has an specific value.

Meaning if cell N15 is filled up with "Weight" value, Cells T15 & U15 will have to be filled up before the user can save the file. So far I have the following two rules that I couldn't compile in one.

If Sheets("Service Schedule").Range("N15").Value = "Weight" And Sheets("Service Schedule").Range("T15").Value = "" Then
MsgBox "You must have a value in Service Schedule Price Per Tn. File was not saved."
Cancel = True
End If

If Sheets("Service Schedule").Range("N15").Value = "Weight" And Sheets("Service Schedule").Range("U15").Value = "" Then
MsgBox "You must have a value in Service Schedule Price Per Tn. File was not saved."
Cancel = True
End If

The same applies when cell N16 is filled up with same value, in this case requiring cells T16 & U16 to be filled in. This goes on for circa 100 lines.

I've done it individually as above but the programme is too long and VBD cannot compile it.

Thank you in advance!

Dieguete

 

[Micron]

Hard to suggest too much with the given information, but putting commonalities together is one way to reduce code. In this case, 2 decisions are made that both apply to the same sheet (Service Schedule) so the sub decisions could be nested within the outer decision about Service Schedule:

With Sheets("Service Schedule")
   If .Range("N15") = "Weight" Then
   If .Range("T15") = "" Or .Range("U15") = "" Then
      MsgBox "You must have a value in Service Schedule Price Per Tn. File was not saved."
      Cancel = True
    End If
End With

With blocks can also eliminate a lot of repetition.
If this exercise applied to a set of rows for N, T and U and those rows are consecutive (e.g. rows 15 to 50) then a counter could run from 15 to 50 and replace 15, 16, 17, etc. code lines with the counter values. That might eliminate a lot of repetition.

 

[Dieguete]

Hard to suggest too much with the given information, but putting commonalities together is one way to reduce code. In this case, 2 decisions are made that both apply to the same sheet (Service Schedule) so the sub decisions could be nested within the outer decision about Service Schedule:

With Sheets("Service Schedule")
   If .Range("N15") = "Weight" Then
   If .Range("T15") = "" Or .Range("U15") = "" Then
      MsgBox "You must have a value in Service Schedule Price Per Tn. File was not saved."
      Cancel = True
    End If
End With

With blocks can also eliminate a lot of repetition.
If this exercise applied to a set of rows for N, T and U and those rows are consecutive (e.g. rows 15 to 50) then a counter could run from 15 to 50 and replace 15, 16, 17, etc. code lines with the counter values. That might eliminate a lot of repetition.

Hi Micron,

Thanks for your prompt reply and solution. I used it in the programme with an adjustment (there was an "End If" missing) and works perfectly.

With Sheets("Service Schedule")
If .Range("N15") = "Weight" Then
If .Range("T15") = "" Or .Range("U15") = "" Then
MsgBox "You must have a value in Service Schedule Price Per Tn. File was not saved."
Cancel = True
End If
End If
End With

How would you use the counter? Haven't used this function before.

Dieguete

 

[Micron]

See what happens when you don't indent properly?
Don't know what you've got for sheets so I'll just throw something out here. This assumes it's 15 to 50 ranges you want to check, not sheets.

Dim i As Integer
Dim bolMissing As Boolean

For i = 15 to 50 'step 1 is default
     With Sheets("Service Schedule")
        If .Range("N" & i) = "Weight" Then
            If .Range("T" & i) = "" Or .Range("U" & i) = "" Then
               bolMissing = True
            End If
         End If
      End With
Next
If bolMissing = True Then
   MsgBox "You must have a value in Service Schedule Price Per Tn. File was not saved."
   Cancel = True
End If

You'd move the msgbox and cancel out of the loop, otherwise you'd get a message for every missing value. Could also string together a list of sheet names where the value was missing with such a loop.

Please use code tags (vba button on posting toolbar) when posting code. It maintains indentation and readability.

 

[Dieguete]

Thank you Micron! It works perfectly!

 

[Micron]

Great! I expected my guesses would be incorrect. Can you mark your thread as solved then?
Thanks.