Need Triggering event with cells calculate resulting in mess

Lt. Krul

New Member
Joined
May 6, 2002
Messages
32
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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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 !
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top