+ Reply to Thread
Results 1 to 5 of 5

Message when cell changes

Hybrid View

  1. #1
    Registered User
    Join Date
    01-20-2014
    Location
    Scotland
    MS-Off Ver
    Excel 2003
    Posts
    59

    Message when cell changes

    I have the following code in my worksheet_calculate sub, and another sub:

    Private Sub Worksheet_Calculate()
    
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    ActiveSheet.Unprotect Password:="letsplaydarts"
    
      Dim myRange As Range
      Dim r2 As Range
      Dim vNewValue As Variant
      Dim vOldValue As Variant
      Dim i As Integer
      Dim iError As Long
      Dim sAddress As String
     
      'Define the range
      Set myRange = Range(sGblSheet1RANGE)
     
      'If a runtime error is generated because the old value array was not defined
      'then generate the old value array
      On Error Resume Next
      vOldValue = vGblSheet1CalculatedOldValues(1)
      iError = Err.Number
      If iError = 9 Then
        Call InitialzeSheet1SavedValuesForWorksheetCalculate
      End If
      On Error GoTo 0
      
      
      'Examine each value in the range
      For Each r2 In myRange
        i = i + 1
        vOldValue = vGblSheet1CalculatedOldValues(i)
        vNewValue = r2.Value
        
        'If the value in a cell was changed:
        'a. Output a message
        'b. Save the 'new value' as the 'old value'
        If vNewValue <> vOldValue Then
          sAddress = r2.Address(False, False)   'Get the address without the Dollar Signs '$'
        
          MsgBox "The value of cell '" & sAddress & "' was changed by FORMULA " & _
                 "from '" & vOldValue & "' to '" & vNewValue & "'.", , "Worksheet_Calculate()"
          vGblSheet1CalculatedOldValues(i) = vNewValue
        End If
        
      Next r2
    
     'Clear the range object
     Set myRange = Nothing
    Option Explicit
    
    'The following 'Global Variable' saves the 'Old' value of Cell 'AC6' 'Worksheet_Change()'
    Public vGblSheet1OldA1Value As Variant
    
    'The following defines the range that will be examined in 'Worksheet_Calculate()'
    Public Const sGblSheet1RANGE = "AC6"
    
    
    'The following is the array that contains the 'old values' for the range that will be examined in 'Worksheet_Calculate()'
    Public vGblSheet1CalculatedOldValues() As Variant
    
    
    Sub InitialzeSheet1SavedValuesForWorksheetCalculate()
    
      Dim myRange As Range
      Dim r As Range
      Dim i As Integer
      Dim iItemsInRange As Integer
     
      'Define the range
      Set myRange = Range(sGblSheet1RANGE)
     
      'Redimension the dynamic array to hold the number of items in the range
      'Save the original values in the range
      iItemsInRange = myRange.Count
      If iItemsInRange > 0 Then
        ReDim vGblSheet1CalculatedOldValues(1 To iItemsInRange)
      End If
     
      For Each r In myRange
        i = i + 1
        vGblSheet1CalculatedOldValues(i) = r.Value
      Next r
    
     'Clear the range object
     Set myRange = Nothing
      
    End Sub
    it's a bit of a complicated sheet here checkboxes are ticked, and when 3 in a certain area are ticked, then a cell value changes (AC6)

    This works very well, and the message is displayed.

    Any ideas how I would adapt this to include other cells? The cells I'd want to include are AC6, AC9, AC12, AC15, AC18, AC21, AC24.

    Thanks in advance

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Message when cell changes

    Try changing this line:

    Set myRange = Range(sGblSheet1RANGE)
    to this:

    Set myRange = Union(Range("A6"), Range("A9"), Range("A12"), Range("A15"), Range("A18"), Range("A21"), Range("A24"))

  3. #3
    Registered User
    Join Date
    01-20-2014
    Location
    Scotland
    MS-Off Ver
    Excel 2003
    Posts
    59

    Re: Message when cell changes

    Quote Originally Posted by stnkynts View Post
    Try changing this line:

    Set myRange = Range(sGblSheet1RANGE)
    to this:

    Set myRange = Union(Range("A6"), Range("A9"), Range("A12"), Range("A15"), Range("A18"), Range("A21"), Range("A24"))
    Not sure if I changed the correct part, or not, but it didn't seem to work. I've sent a pm with a link to the spreadsheet I'm using and more details if that will help

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,864

    Re: Message when cell changes

    At first sight - have you tried just:
    Public Const sGblSheet1RANGE = "AC6,AC9,AC12,AC15,AC18"
    etc
    Best Regards,

    Kaper

  5. #5
    Registered User
    Join Date
    01-20-2014
    Location
    Scotland
    MS-Off Ver
    Excel 2003
    Posts
    59

    Re: Message when cell changes

    Quote Originally Posted by Kaper View Post
    At first sight - have you tried just:
    Public Const sGblSheet1RANGE = "AC6,AC9,AC12,AC15,AC18"
    etc
    That seems to work perfectly. 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. Adding an input message to each cell to bring back corrsponding message
    By Nic31 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-01-2014, 09:28 AM
  2. [SOLVED] Conditional Format for blank cell to activate error message in following cell
    By tittbb in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-28-2014, 04:07 AM
  3. [SOLVED] VBA message box with cell values in the message
    By nenadmail in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-10-2012, 10:29 AM
  4. how to generate error message for an empty cell (mandatory input cell)
    By asankar24 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-20-2012, 12:37 PM
  5. [SOLVED] Intercept/replace standard 'cell protected' message with my own message?
    By KR in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-16-2006, 10:35 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