Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: VBA to read cell value rather than the cell formula

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to read cell value rather than the cell formula

    I would like a message box to pop up when a cell in a range changes. The cells in the range all have IF formulas in them. My problem is that the code i am using seems to only read the formula, which doesn't change, hence nothing happens. How can i get my code to read the cell value, which does change, rather than the formula which doesn't?

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Application.Intersect(Range("ED45:EO83"), Target) Is Nothing) Then
    MsgBox "Cell " & Target.Address & " has changed.", vbInformation, "Kutools for Excel"
    End If
    End Sub

  2. #2
    New Member
    Join Date
    Apr 2017
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to read cell value rather than the cell formula

    Hi,

    Instead of cell use cell.value

    Hope it helps

  3. #3
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,698
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA to read cell value rather than the cell formula

    Hi

    How does the cell value change?
    The event procedure is only fired when the cell is written.

    Please elaborate.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  4. #4
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,047
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to read cell value rather than the cell formula

    Hi
    To detect formula changes, use the Worksheet_Calculate event

    dave

  5. #5
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,698
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA to read cell value rather than the cell formula

    Quote Originally Posted by dmt32 View Post
    To detect formula changes, use the Worksheet_Calculate event
    Hi Dave

    The Worksheet_Calculate event will run when any calculation is performed. You'd then have to figure out what exactly happened.
    The Worksheet_Change event will tell you which cell was modified which seems to be in this case more efficient and direct.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  6. #6
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,047
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to read cell value rather than the cell formula

    The Worksheet_Calculate event will run when any calculation is performed. You'd then have to figure out what exactly happened.
    The Worksheet_Change event will tell you which cell was modified which seems to be in this case more efficient and direct.
    thanks but I am fully aware event does not have Target Parameter - there are workarounds using Calculate event - just waiting for OPs response

    Dave
    Last edited by dmt32; Apr 5th, 2019 at 05:41 AM.

  7. #7
    New Member
    Join Date
    May 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to read cell value rather than the cell formula

    Do you mean like in the below?

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Not (Application.Intersect(Range("ED45:EO83"), Target) Is Nothing) Then
    MsgBox "Cell.value " & Target.Address & " has changed.", vbInformation, "Kutools for Excel"
    End If
    End Sub

  8. #8
    New Member
    Join Date
    May 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to read cell value rather than the cell formula

    This is what's in the target cell:

    =VALUE(IF(COUNTIF($P$3:$P$42,R7), 1,2))

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

    Default Re: VBA to read cell value rather than the cell formula

    Do i just change the below?:

    Private Sub Worksheet_
    Calculate(ByVal Target As Range)
    If Not (Application.Intersect(Range("ED45:EO83"), Target) Is Nothing) Then
    MsgBox "Cell " & Target.Address & " has changed.", vbInformation, "Kutools for Excel"
    End If
    End Sub

  10. #10
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,047
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to read cell value rather than the cell formula

    Quote Originally Posted by austicks84 View Post
    Do i just change the below?:

    Private Sub Worksheet_
    Calculate(ByVal Target As Range)
    If Not (Application.Intersect(Range("ED45:EO83"), Target) Is Nothing) Then
    MsgBox "Cell " & Target.Address & " has changed.", vbInformation, "Kutools for Excel"
    End If
    End Sub
    Worksheet_Calculate event has no parameters & you cannot (as far as I am aware) modify then in manner shown.

    To detect which cell in your range has changed using this event, a workaround is to use the Range.ID property to store the cell value which you use to compare changes against

    try following

    Code:
    Private Sub Worksheet_Calculate()
        Dim rng As Range, cell As Range
        Dim CellHasChanged As Boolean
        
        Set rng = Me.Range("ED45:EO83")
        For Each cell In rng.Cells
            With cell
               CellHasChanged = CBool(.ID <> .Value)
               If CellHasChanged Then
                .ID = .Value
                MsgBox "Cell " & cell.Address & " has changed.", vbInformation, "Cell Value Changed"
                Exit For
               End If
            End With
        Next cell
        Set rng = Nothing
    End Sub
    A slight limitation of this approach is that the values stored in ID property are lost when workbook closed but hopefully, will not be an issue for you.


    Dave
    Last edited by dmt32; Apr 8th, 2019 at 01:39 AM.

Some videos you may like

User Tag List

Tags for this Thread

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
  •