Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: Data Validation

  1. #11
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Brian

    If you give me a realistic example I'll set it up so that it's error proof. Nothing worse than a Runtime error for an Excel user.





  2. #12
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave,

    The cells that have the sum formulas are I7 , I8, I12, I16 but they all have different > values, but I can figue that out.

    Brian

  3. #13
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here you go

    Private Sub Worksheet_Change(ByVal Target As Range)
    With WorksheetFunction
    If .Sum(Range("I7")) <> 1800 Or _
    .Sum(Range("I8")) <> 1800 Or _
    .Sum(Range("I12")) <> 1800 Or _
    .Sum(Range("I16")) <> 1800 Then

    MsgBox "Invalid value", vbCritical
    Target.Select
    End If
    End With
    End Sub

  4. #14
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave,

    Never mind I took out the < and just left the > and it works, except the use needs to use the delete key to move on. But that's minor hopefully.

    Mahalo for the time and effort and patience.

    Brian

    [ This Message was edited by: Brian from Maui on 2002-03-16 19:47 ]

  5. #15
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Brian

    You could add:

    Application.Undo
    Just before the: Target.Select

    This would eliminate the need to delete and would return the cell to it's original value


    Private Sub Worksheet_Change(ByVal Target As Range)
    With WorksheetFunction
    If .Sum(Range("I7")) > 1800 Or _
    .Sum(Range("I8")) > 1800 Or _
    .Sum(Range("I12")) > 1800 Or _
    .Sum(Range("I16")) > 1800 Then

    MsgBox "Invalid value", vbCritical
    Application.EnableEvents = False
    Application.Undo
    Target.Select
    Application.EnableEvents = False
    End If
    End With
    End Sub



    And if all these cells should total 1801 each or greater you could use:

    Private Sub Worksheet_Change(ByVal Target As Range)
    With WorksheetFunction
    If .Sum(Range("I7,I8,I12,I16")) > 1800 * 5 Then

    MsgBox "Invalid value", vbCritical
    Application.EnableEvents = False
    Application.Undo
    Target.Select
    Application.EnableEvents = False
    End If
    End With


  6. #16
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave,

    I tried itthe first time and it worked, now it doesn't.

  7. #17
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Oops!!! That's because of one of my typos, sorry.

    The second part should read:
    Application.EnableEvent=True

    EG

    Private Sub Worksheet_Change(ByVal Target As Range)
    With WorksheetFunction
    If .Sum(Range("I7,I8,I12,I16")) > 1800 * 5 Then

    MsgBox "Invalid value", vbCritical
    Application.EnableEvents = False
    Application.Undo
    Target.Select
    Application.EnableEvents = True
    End If
    End With


    To get it running again just place this in a standard Module and run it:

    Sub ResetEvents()
    Application.EnableEvents=True
    End sub



  8. #18
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave,

    I'm sorry, but is my first time doing this, I right click on the tab and copy and paste in the work book,then how do I get to the amn module?

    Brian

  9. #19
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry Brian, I shouldn't assume. Just go to Insert>Module



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
  •