Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 21

Thread: Need Triggering event with cells calculate resulting in mess

  1. #11
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #12
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Code:
    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, NateO

    [ This Message was edited by: NateO on 2002-05-07 19:03 ]

  3. #13
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #14
    New Member
    Join Date
    May 2002
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  5. #15
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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, NateO

    [ This Message was edited by: NateO on 2002-05-07 19:49 ]

  6. #16
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 !

  7. #17
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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, NateO

    [ This Message was edited by: NateO on 2002-05-07 20:12 ]

  8. #18
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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, NateO

    [ 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,
    Ivan F Moala
    Have a Nice day



    [ This Message was edited by: Ivan F Moala on 2002-05-08 21:52 ]

  9. #19
    New Member
    Join Date
    May 2002
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?


  10. #20
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •