+ Reply to Thread
Results 1 to 3 of 3

Worksheet change event with multiple column cell value change

  1. #1
    Registered User
    Join Date
    09-28-2015
    Location
    Malaysia
    MS-Off Ver
    2010
    Posts
    3

    Worksheet change event with multiple column cell value change

    Here is my plan:

    For Column A, if cell value > 1, prompt MsgBox, then go to check Column B.
    For Column B, if cell value > 2, prompt MsgBox, then go to check Column C.
    For Column C, if cell value > 20, prompt MsgBox, End Sub

    To test this code:

    First of all, I insert value of 0, 1, 21 into any blank cell in the worksheet. (Eg: D2, E2, F2)
    Then I copy and paste value of D2, E2, F2 into A2, B2, C2.
    However, it repeats the Column A & B Msgbox although the values are not exceeded.
    Byright, it should only prompt Column C MsgBox since the value is >20.

    The code is working perfectly if I key in value one by one into A2, B2, C2.
    But it is not working for copy and paste method.
    Somehow it will repeats previous column MsgBox.

    Please help me find errors in my code. Thanks for helping!

    Here is my code:

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim aCell As Range
    Dim bCell As Range
    Dim cCell As Range

    Set aCell = Range("A2:A10")
    Set bCell = Range("B2:B10")
    Set cCell = Range("C2:C10")

    On Error GoTo Whoa

    Application.EnableEvents = False

    If Not Intersect(Target, aCell) Is Nothing Then
    For Each aCell In Target
    If aCell.Value > 1 Then
    If MsgBox("A Temperature Exceeded", vbExclamation, "Warning") = vbOK Then GoTo Line1
    End If
    Next
    End If

    Line1:
    If Not Intersect(Target, bCell) Is Nothing Then
    For Each bCell In Target
    If bCell.Value > 2 Then
    If MsgBox("B Temperature Exceeded", vbExclamation, "Warning") = vbOK Then GoTo Line2
    End If
    Next
    End If

    Line2:
    If Not Intersect(Target, cCell) Is Nothing Then
    For Each cCell In Target
    If cCell.Value > 20 Then
    If MsgBox("C Temperature Exceeded", vbExclamation, "Warning") = vbOK Then
    End If
    End If
    Next
    End If

    Letscontinue:
    Application.EnableEvents = True
    Exit Sub
    Whoa:
    MsgBox Err.Description
    Resume Letscontinue
    End Sub

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Worksheet change event with multiple column cell value change

    Try this...

    Please Login or Register  to view this content.
    Or this...
    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    09-28-2015
    Location
    Malaysia
    MS-Off Ver
    2010
    Posts
    3

    Re: Worksheet change event with multiple column cell value change

    Ok, I have tried both of your VBA Codes.

    When I copy & paste 2 rows of cells into A1:C2, where all the values are out of condition.

    Example:

    Column A Column B Column C
    Row 1: 100 100 100
    Row 2: 100 100 100

    The first code will only prompt MsgBox 3 times. (Maybe is because of you have added "Then Exit For" to "For Each" function, therefore it exit "for each" function when the condition meets)
    The second code will prompt MsgBox 6 times in total, for each cell value which are out of the condition.

    For my application, I just want to prompt MsgBox only 1 time for each column, to notify me which column is having error, then I can focus checking data on that particular column. Since I have to copy and paste a huge amount of data into this worksheet, if there are many cell value which are out of range, then there will be a lot of MsgBox prompted. So I will be using your first code.

    Thanks for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to prevent worksheet change invoking System X Control change event
    By dmw2014 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 09-13-2014, 10:35 PM
  2. Same WorkSheet Change Event Code Across Multiple Sheets Help
    By jaylove in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-03-2014, 06:01 PM
  3. [SOLVED] Worksheet change event applying to multiple ranges
    By MaddyG in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-29-2013, 12:04 PM
  4. [SOLVED] Worksheet Change Event - Multiple Cell Selection
    By mojo249 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-02-2012, 08:30 PM
  5. Worksheet Change Event for Multiple Rows
    By Dean England in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-19-2007, 05:04 PM
  6. [SOLVED] [SOLVED] Worksheet change event-single column
    By jasminesy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-17-2006, 01:30 PM
  7. [SOLVED] Cell value change to trigger macro (worksheet change event?)
    By Neil Goldwasser in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-10-2006, 10:00 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1