Making cells mandatory to fill in if a previous cell contains info

I have seen the making cells mandatory to fill in comment and it worked, but
I need the same only if a previous cell has been filled. I have various
columns not all of them will be filled but if a cell is filled the complete
row will have to be filled, how do I do this, if one cell is filled then the
complete row should be filled?
thanks,
leonardo

I would use a helper column that put a warning in big red letters:

=if(and(counta(b2:f2)>0,counta(b2:f2)<5)),"Please fix this row!","")

If there's anything in B2:F2, but they're not all filled in (B:F is 5 columns),
then put that warning message.

leonardo wrote:
>
> I have seen the making cells mandatory to fill in comment and it worked, but
> I need the same only if a previous cell has been filled. I have various
> columns not all of them will be filled but if a cell is filled the complete
> row will have to be filled, how do I do this, if one cell is filled then the
> complete row should be filled?
> thanks,
> leonardo

--

Dave Peterson

hi, I used this code and it worked, but I would like to be able to apply this
code to all my worksheets. The name of my worksheets are: X340, X342n and
X642e? How can I modify this code so it applies to all my worksheets?

thanks,
p.d. thanks for the other tip!

"Dave Peterson" wrote:

> I would use a helper column that put a warning in big red letters:
>
> =if(and(counta(b2:f2)>0,counta(b2:f2)<5)),"Please fix this row!","")
>
> If there's anything in B2:F2, but they're not all filled in (B:F is 5 columns),
> then put that warning message.
>
> leonardo wrote:
> >
> > I have seen the making cells mandatory to fill in comment and it worked, but
> > I need the same only if a previous cell has been filled. I have various
> > columns not all of them will be filled but if a cell is filled the complete
> > row will have to be filled, how do I do this, if one cell is filled then the
> > complete row should be filled?
> > thanks,
> > leonardo
>
> --
>
> Dave Peterson
>

This is a formula that sits in a cell.

You'll have to put the formula in a cell in each worksheet.

leonardo wrote:
>
> hi, I used this code and it worked, but I would like to be able to apply this
> code to all my worksheets. The name of my worksheets are: X340, X342n and
> X642e? How can I modify this code so it applies to all my worksheets?
>
> thanks,
> p.d. thanks for the other tip!
>
> "Dave Peterson" wrote:
>
> > I would use a helper column that put a warning in big red letters:
> >
> > =if(and(counta(b2:f2)>0,counta(b2:f2)<5)),"Please fix this row!","")
> >
> > If there's anything in B2:F2, but they're not all filled in (B:F is 5 columns),
> > then put that warning message.
> >
> > leonardo wrote:
> > >
> > > I have seen the making cells mandatory to fill in comment and it worked, but
> > > I need the same only if a previous cell has been filled. I have various
> > > columns not all of them will be filled but if a cell is filled the complete
> > > row will have to be filled, how do I do this, if one cell is filled then the
> > > complete row should be filled?
> > > thanks,
> > > leonardo
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

sorry, this is the code I am reffering to: and I would like this code to work
for all worksheets: X340, X342n and X642e,
thnaks,

Dim cell As Range
For Each cell In Sheets("X340").Range("L2,L58")
If Len(Trim(cell.Text)) = 0 Then
MsgBox "Please make sure that column B, K & L are filled"
Application.Goto cell
Cancel = True
Exit For
End If
Next cell

"Dave Peterson" wrote:

> This is a formula that sits in a cell.
>
> You'll have to put the formula in a cell in each worksheet.
>
> leonardo wrote:
> >
> > hi, I used this code and it worked, but I would like to be able to apply this
> > code to all my worksheets. The name of my worksheets are: X340, X342n and
> > X642e? How can I modify this code so it applies to all my worksheets?
> >
> > thanks,
> > p.d. thanks for the other tip!
> >
> > "Dave Peterson" wrote:
> >
> > > I would use a helper column that put a warning in big red letters:
> > >
> > > =if(and(counta(b2:f2)>0,counta(b2:f2)<5)),"Please fix this row!","")
> > >
> > > If there's anything in B2:F2, but they're not all filled in (B:F is 5 columns),
> > > then put that warning message.
> > >
> > > leonardo wrote:
> > > >
> > > > I have seen the making cells mandatory to fill in comment and it worked, but
> > > > I need the same only if a previous cell has been filled. I have various
> > > > columns not all of them will be filled but if a cell is filled the complete
> > > > row will have to be filled, how do I do this, if one cell is filled then the
> > > > complete row should be filled?
> > > > thanks,
> > > > leonardo
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Are you trying to look at all the rows 2:58 or just row 2 and row 58?

I'm gonna guess that you want all the rows:

Dim cell As Range
dim myRow as long

with sheets("x340")
For Each cell In .Range("a2:a58").cells 'just define the row
myrow = cell.row
if trim(.cells(myrow,"B")) = "" _
or trim(.cells(myrow,"K")) = "" _
or trim(.cells(myrow,"L")) = "" then
MsgBox "Please make sure that column B, K & L are filled on this row"
Application.Goto cell
Cancel = True
Exit For
End If
Next cell
end with

But you could use "L2,L58" if that's what you wanted.

As a user, I think I'd rather receive that immediate feedback that the worksheet
formula gives me--rather than waiting until I was saving the workbook.

leonardo wrote:
>
> sorry, this is the code I am reffering to: and I would like this code to work
> for all worksheets: X340, X342n and X642e,
> thnaks,
>
> Dim cell As Range
> For Each cell In Sheets("X340").Range("L2,L58")
> If Len(Trim(cell.Text)) = 0 Then
> MsgBox "Please make sure that column B, K & L are filled"
> Application.Goto cell
> Cancel = True
> Exit For
> End If
> Next cell
>
> "Dave Peterson" wrote:
>
> > This is a formula that sits in a cell.
> >
> > You'll have to put the formula in a cell in each worksheet.
> >
> > leonardo wrote:
> > >
> > > hi, I used this code and it worked, but I would like to be able to apply this
> > > code to all my worksheets. The name of my worksheets are: X340, X342n and
> > > X642e? How can I modify this code so it applies to all my worksheets?
> > >
> > > thanks,
> > > p.d. thanks for the other tip!
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > I would use a helper column that put a warning in big red letters:
> > > >
> > > > =if(and(counta(b2:f2)>0,counta(b2:f2)<5)),"Please fix this row!","")
> > > >
> > > > If there's anything in B2:F2, but they're not all filled in (B:F is 5 columns),
> > > > then put that warning message.
> > > >
> > > > leonardo wrote:
> > > > >
> > > > > I have seen the making cells mandatory to fill in comment and it worked, but
> > > > > I need the same only if a previous cell has been filled. I have various
> > > > > columns not all of them will be filled but if a cell is filled the complete
> > > > > row will have to be filled, how do I do this, if one cell is filled then the
> > > > > complete row should be filled?
> > > > > thanks,
> > > > > leonardo
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

And if you wanted to loop through those worksheets...

Dim wks As Worksheet
Dim cell As Range
Dim myRow As Long
Dim FoundAnError As Boolean

FoundAnError = False
For Each wks In Worksheets(Array("x340", "x342n", "x642e"))
If FoundAnError Then
Exit For
End If
With wks
For Each cell In .Range("a2:a58").Cells 'just define the row
myRow = cell.Row
If Trim(.Cells(myRow, "B")) = "" _
Or Trim(.Cells(myRow, "K")) = "" _
Or Trim(.Cells(myRow, "L")) = "" Then
MsgBox "Please make sure that column B, K & L are filled on this row"
Application.Goto cell
'Cancel = True
FoundAnError = True
Exit For
End If
Next cell
End With
Next wks


leonardo wrote:
>
> sorry, this is the code I am reffering to: and I would like this code to work
> for all worksheets: X340, X342n and X642e,
> thnaks,
>
> Dim cell As Range
> For Each cell In Sheets("X340").Range("L2,L58")
> If Len(Trim(cell.Text)) = 0 Then
> MsgBox "Please make sure that column B, K & L are filled"
> Application.Goto cell
> Cancel = True
> Exit For
> End If
> Next cell
>
> "Dave Peterson" wrote:
>
> > This is a formula that sits in a cell.
> >
> > You'll have to put the formula in a cell in each worksheet.
> >
> > leonardo wrote:
> > >
> > > hi, I used this code and it worked, but I would like to be able to apply this
> > > code to all my worksheets. The name of my worksheets are: X340, X342n and
> > > X642e? How can I modify this code so it applies to all my worksheets?
> > >
> > > thanks,
> > > p.d. thanks for the other tip!
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > I would use a helper column that put a warning in big red letters:
> > > >
> > > > =if(and(counta(b2:f2)>0,counta(b2:f2)<5)),"Please fix this row!","")
> > > >
> > > > If there's anything in B2:F2, but they're not all filled in (B:F is 5 columns),
> > > > then put that warning message.
> > > >
> > > > leonardo wrote:
> > > > >
> > > > > I have seen the making cells mandatory to fill in comment and it worked, but
> > > > > I need the same only if a previous cell has been filled. I have various
> > > > > columns not all of them will be filled but if a cell is filled the complete
> > > > > row will have to be filled, how do I do this, if one cell is filled then the
> > > > > complete row should be filled?
> > > > > thanks,
> > > > > leonardo
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson