Require Cell Entry based on condition

I am working on enhancing our expense report to require the users to enter a
code in a specified cell if the dollar value of certain cells is greater than
zero.

Column E contains the code that will be required if any expense amounts are
entered in columns L thru Q of that same row. I would like to validate each
row before proceeding to entering the next row. This validation needs to
be run on 20 rows.

Any suggestions would be greatly appreciated.

Hmm.. I don't think this will be your final answer, however I could conceive
that you use one of the default functions on the sheet that deals with
changes in cells. If the data in a cell changes to a value that does not
meet your validation criteria, you store the row of the change. Then if the
user tries to select a different row to be active, the user is alerted and
the activecell becomes a predesignated column in the row that is a problem.
The user should be informed how to get around this if they need to select a
different cell for data review.

Not the program to do it, but it's the idea you are looking for, I think.


"Kathy - Lovullo" wrote:

> I am working on enhancing our expense report to require the users to enter a
> code in a specified cell if the dollar value of certain cells is greater than
> zero.
>
> Column E contains the code that will be required if any expense amounts are
> entered in columns L thru Q of that same row. I would like to validate each
> row before proceeding to entering the next row. This validation needs to
> be run on 20 rows.
>
> Any suggestions would be greatly appreciated.

In theory this would work, but I guess I am looking for a little more
instruction on some of the code to do this.

What functions could I use for the validation? How do I validate that the
sum of Column L thorugh Q is not greater than zero using VBA Code. My
thoughts are that if it is greater I would return a Msgbox informing them
they need to enter a code in column E of that row and return them to that
cell.

Can anyone provide me with some help or tips on the proper functions and VBA
coding to use?

Thanks


"GB" wrote:

> Hmm.. I don't think this will be your final answer, however I could conceive
> that you use one of the default functions on the sheet that deals with
> changes in cells. If the data in a cell changes to a value that does not
> meet your validation criteria, you store the row of the change. Then if the
> user tries to select a different row to be active, the user is alerted and
> the activecell becomes a predesignated column in the row that is a problem.
> The user should be informed how to get around this if they need to select a
> different cell for data review.
>
> Not the program to do it, but it's the idea you are looking for, I think.
>
>
> "Kathy - Lovullo" wrote:
>
> > I am working on enhancing our expense report to require the users to enter a
> > code in a specified cell if the dollar value of certain cells is greater than
> > zero.
> >
> > Column E contains the code that will be required if any expense amounts are
> > entered in columns L thru Q of that same row. I would like to validate each
> > row before proceeding to entering the next row. This validation needs to
> > be run on 20 rows.
> >
> > Any suggestions would be greatly appreciated.

I wrote a dirty version of what you are looking for, because I can not access
VBA helpfiles right now. The message box that is created, also shows the
total of the sum. Thought I would include it so that you could chose not to
include it if you wish. But the following code would be added to the code of
the worksheet in which you want to do validation.



Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Sum As Double

Sum = 0

Sum = Sheet1.Cells(Target.Row, "L").Value
Sum = Sum + Sheet1.Cells(Target.Row, "M").Value
Sum = Sum + Sheet1.Cells(Target.Row, "N").Value
Sum = Sum + Sheet1.Cells(Target.Row, "O").Value
Sum = Sum + Sheet1.Cells(Target.Row, "P").Value
Sum = Sum + Sheet1.Cells(Target.Row, "Q").Value

If Sum <= 0 Then
MsgBox ("The sum of the values between Columns L and Q is Less than
or equal to zero. (" & Sum & ") Either revise the numbers to total greater
than zero or Enter the appropriate data in Column E")
Sheet1.Cells(Target.Row, "E").Select
End If
End Sub

"Kathy - Lovullo" wrote:

> In theory this would work, but I guess I am looking for a little more
> instruction on some of the code to do this.
>
> What functions could I use for the validation? How do I validate that the
> sum of Column L thorugh Q is not greater than zero using VBA Code. My
> thoughts are that if it is greater I would return a Msgbox informing them
> they need to enter a code in column E of that row and return them to that
> cell.
>
> Can anyone provide me with some help or tips on the proper functions and VBA
> coding to use?
>
> Thanks
>
>
> "GB" wrote:
>
> > Hmm.. I don't think this will be your final answer, however I could conceive
> > that you use one of the default functions on the sheet that deals with
> > changes in cells. If the data in a cell changes to a value that does not
> > meet your validation criteria, you store the row of the change. Then if the
> > user tries to select a different row to be active, the user is alerted and
> > the activecell becomes a predesignated column in the row that is a problem.
> > The user should be informed how to get around this if they need to select a
> > different cell for data review.
> >
> > Not the program to do it, but it's the idea you are looking for, I think.
> >
> >
> > "Kathy - Lovullo" wrote:
> >
> > > I am working on enhancing our expense report to require the users to enter a
> > > code in a specified cell if the dollar value of certain cells is greater than
> > > zero.
> > >
> > > Column E contains the code that will be required if any expense amounts are
> > > entered in columns L thru Q of that same row. I would like to validate each
> > > row before proceeding to entering the next row. This validation needs to
> > > be run on 20 rows.
> > >
> > > Any suggestions would be greatly appreciated.