Need Triggering event with cells calculate resulting in mess

[Lt. Krul]

My Timesheet contains rows for Project Codes and Hours spent during the week with the end cell calculating the hours for that project.
I need a way to Keep users from entering hours on a row (which results in a calculated last row with value <>0)without selecting a Jobcode first. A message would pop up warning "Any time spent must have an associated Job code. Please enter Job code."

Any Help here would be appreciated.

[James]

Can you supply us with a little more info?
cell addresses etc?

James

[Nimrod]

Excel has a built in functioon for this very purpose called data validation.

Let me give you an example.
Lets say you want A1>0 before you allow a user to enter anything into B1. then do the following.
1- select B1
2- goto toolbar
3- now select data.. validation
4- data validation window will popup
6- in the "allow" drop down select "custom"
7- in the "formula" txtbx type "=A1>0"
8- select the "error alert" tab
9- type in the message you want displayed when someone does not enter data to A1 first.

Boomba ... your done !

[datastream]

I'm not sure of a function in a worksheet that would check the value in another cell first.

You could create a macro with an input box, checking the validity of the inputs and then automatically posting the results on the sheet. If the user entered hours or project code alone, your message could pop up and keep them from posting without complete information.

[Lt. Krul]

Nimrod, I like your Idea, but unfortunately I tried it with some glitches...
My A1 is already a validated list from another sheet, and the cell is formated for text. my B1 cell is really B1 through B5.
I'm not very good at function language, so If you could take another shot at this I think we'll have it.

Thanks.

[Nimrod]

1- Tell me exactly which cell should be tested. ie. A1 etc. In other words which cell needs to have a value first. I.e. cell position for job code.
2- tell me exactly what value will be in the cell
3- What cell will be the cell your testing from.
I will work the formula from that

[ This Message was edited by: Nimrod on 2002-05-07 13:46 ]

[ This Message was edited by: Nimrod on 2002-05-07 13:48 ]

[Lt. Krul]

Nimrod - good to see you...

1. A1 needs to have something in it before I'll let the other cells (b1 through b5) allowed values.

2. A1 itself is allowed (via validation) only text values from a list on another sheet (such as "01", "Admin.", "VA" and the like) there are 43 allowable values for A1.

3. Cells B1 through B5 are the ones I'm selecting prior to click the DATA VALIDATION menu. These cells are numbers (hours, like 5.0 or 3.5)

Thanks...

[lenze]

I'm sure Nimrod will come up with a way using data validation, but you could also use this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1:B5")) Is Nothing Then
If Range("$A$1").Value = "" Then MsgBox ("You can't do that")
End If
End Sub

Place in the WorkSheet Module

[ This Message was edited by: lenze on 2002-05-07 14:54 ]

[Nimrod]

[Nimrod]

Hi Krul:
Sorry to take so long but I was at work and got called into .... action ...
Anyway here is the validation formula that will work: =NOT(ISBLANK($A$1))

You will put this in your validation window for cells B1 to B5.

IMPORTANT: Since you want this rule to fire even when the evaluation cell is blank (i.e. A1) make sure you UNCHECK the "ignore blank" option in the validation window. If you don't uncheck this the rule will not be fired when A1 is empty.

Remember to enter a "error alert" message in the validation window as well.

[ This Message was edited by: Nimrod on 2002-05-07 18:03 ]