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.

 

[Nimrod]

Disadvantage of Ienze's solution is that it really doesn't prevent the user from doing what he is not supposed to do.
Ienze's solution does give a pop-up notice but the user can still enter data into B1-5 without entering data to A1.
The validation method gives a warning Pop-up and REFUSES to allow data entry int B1-5 until A1 has data in it.
This message was edited by Nimrod on 2002-05-07 18:12

 

[NateO]

If one builds a little on Lenze's solution, it will not allow the user to enter information if A1 is empty:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, [b1:b5]) Is Nothing And _
    [a1].Value = "" Then
    Target.ClearContents
    MsgBox "Please enter a job code in cell A1"
End If
Application.EnableEvents = True
End Sub

Right-click on the worksheet in question, select 'view code' and paste the code above in the worksheet module.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>

This message was edited by NateO on 2002-05-07 19:03

 

[Nimrod]

Another thing you will have to be careful of with Lenze's solution is that it will fire no matter what sheet your on. So be careful if you use this solution. After all it fires everytime you change any cell in the workbook. No matter what sheet your on in workbook it won't let you enter data in CellB1-5 unless there is data in A1 first.
As far as I can see typing in =NOT(ISBLANK($A$1)) in the built in function for this exact purpose is the easier route.
Why re-invent the wheel ?
This message was edited by Nimrod on 2002-05-07 19:44

 

[Lt. Krul]

Thanks All... Nimrod, your validation rule seems to work OK... I noticed that if you place a value in the A1, then place values in B1,B2 etc. you can then delete the value from A1 and the B1, B2 values stay.

I'll Try the suggested macro also, but I don't like macros since then the user opens his worksheet, that darn macro disable message comes on, and I fear they could disable them!

 

[NateO]

On 2002-05-07 19:42, Nimrod wrote:
Another thing you will have to be careful of with Lenze's solution is that it will fire no matter what sheet your on.

Not sure how this could be true, especially if you follow Lenze's instructions:

Place in the WorkSheet Module

Short way of saying: right click on sheet, click view code.....Lenze was on the right track in terms of a worksheet event procedure. No harm intended, just don't want anyone following this thread to be confused.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>

This message was edited by NateO on 2002-05-07 19:49

 

[Nimrod]

Hi NateO:
If you mean the firing on any sheet , the way I discovered this problem is copied to a workbook and tested it. Sure enough any sheet I went to would require info in A1 before B1. Try it yourself and see .

Anyway ... Kurl's happy with his solutions.. we've seen different ways of solving the same problem ... it's win/win all the way around. Cheers Mate !

 

[NateO]

Oh yeah, all's well. I'm more curious as to where you pasted this macro (I would like to try), I just can't picture it firing on every sheet (without re-writing it).

It's all about the OP getting the answer (& several ways to pluck a duck). Incidentally, I'm a big fan of data validation (you're (Nimrod) on target with this response). Have a good one.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>

This message was edited by NateO on 2002-05-07 20:12

 

[Ivan F Moala]

On 2002-05-07 20:07, NateO wrote:
Oh yeah, all's well. I'm more curious as to where you pasted this macro (I would like to try), I just can't picture it firing on every sheet (without re-writing it).

It's all about the OP getting the answer (& several ways to pluck a duck). Incidentally, I'm a big fan of data validation (you're (Nimrod) on target with this response). Have a good one.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>

This message was edited by NateO on 2002-05-07 20:12

The Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
... will fire on ALL sheets...


The Private Sub Worksheet_Change(ByVal Target As Range) ......won't fire on ALL sheets.

But Lenze was explicit on this ???

Nimrod how were you getting it to fire on ALL
sheets ??


_________________
Kind Regards,<font size=+2><font color="red"> I<font color="blue">van<font color="red"> F M</font color="blue">oala</font><MARQUEE>Have a Nice day

</MARQUEE>
This message was edited by Ivan F Moala on 2002-05-08 21:52

 

[Lt. Krul]

I appreciate all the help, and I'm sorry that I cannot reciprocate (I'm a Novice when it comes to the Excell and VBA programming.

Another Query:
Let's say my A1 list menu gets information from another (hidden) sheet in the file. This hidden sheet contains two columns of information which must taken together.
I need to allow the user to use the pull down list that displays the first column, places that value in A1 AND automatically, the value from the second column is placed in B1 (on the active sheet.) Like this:

Column1 Column2 (hidden sheet)
"91002" "Jobname for 91002"

User selects A1, gets the list dropdown, then selects "91002"
When he's done his current sheet looks like this:

A1 B1 (active sheet)
"91002" "Jobname for 91002"

Any Ideas on how to achieve this 2 for 1 special?

 

[lenze]

Krul:

You had an interesting problem with a lot of good discussion. Now for your next question.

This can be done using VLOOKUP function.
Give the range of dat(both columns a name) such as 'list' Then the formula in B1 would be =VLOOKUP(A1,list,2,0). This will look up the value in A1 in the list and return the value in the 2nd column of the list to B1. The last 0 in in formula requires that an exact match be found.

HTH
This message was edited by lenze on 2002-05-08 10:19