Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Calculate on Format Change

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    rvences
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I created a custom function (using VBA) that returns the Excel Color Index of a particular cell. The function works fine, however, when changing the color format of my "target cell" my custom function does not update to indicate the target cell's new color index. I only get the update once I manually recalculate (F9) or the spreadsheet recalculates by entering data into another cell. My auto calculate is enabled, and I did use the "Application.Volatile" feature in my code. Apparently a format change alone does not cause Excel to recalculate the spread sheet. I've also tried adding "calculate" to my code, which doesn't work. How can I get my custom function to "refresh" after a format change to a cell?

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi rvences,

    Yes, calculation is not supposed to occur upon operations that do not change the contents of cells. However, you can capture such an event (it is not a real Excel event) using "trickery". Fortunately you asked at the right time (April 1st).

    First of all, you have to know how the cell format has been changed (i.e., manually vs. via a macro). If it is changed via a macro then it is easy: just re-calculate right after the macro changes the color using the worksheet object's Calculate method. If it has changed because the cell format has been manually set, it requires the trickery I referred to. You can take advantage of the fact that the format cannot be manually edited without selecting the cell first. What you have to do is use the worksheet's SelectionChange event to determine if the last (previous) cell selected was the one you are interested in. In your SelectionChange event routine you must save a pointer to the Target range. If the Target cell is the cell you are interested in you must also save the current value of the format property you are interested in (e.g., ColorIndex). Then when the next cell is selected, simply check to see if the saved cell pointer is the cell of interest. If and only if this is so the format could have been edited. So then check the format to see if it has been changed, and if so, force the worksheet to re-calculate.

    Here's an example that recalculates the sheet whenever the color fill of cell B4 is manually changed.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static LastRange As Range 'The last range selected
    Static LastColorIndex As Integer 'The color index of the last range selected
    If Not Intersect([B4], LastRange) Is Nothing Then
    If LastRange.Cells(1).Interior.ColorIndex <> LastColorIndex Then
    Calculate
    End If
    End If
    Set LastRange = Target
    LastColorIndex = Target.Interior.ColorIndex
    End Sub

    Incidentally, I should mention that the color fill property of a cell is not changed via Conditional Formatting, so none of this applies to Conditional Formatting, but this shouldn't matter anyway since Conditional Formatting occurs based on cell content changes that trigger worksheet re-calculation.
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    rvences
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the tip! How would I incorporate this into a custom vba function? Would I call the macro from within the function?

    Thanks,
    Rvences

  4. #4
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again rvences,

    You would not need to incorporate this into your function. Just put the code into the worksheet's event code module (right-click on the worksheet's tab, select View Code, and insert the code into the code pane). This routine will cause the worksheet to recalculate whenever the cell (in this example B4) fill color changes, and therefore (because you have Volatile set) your function will calculate and return the new color index (or whatever).

    Happy computing.

    Damon

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    rvences
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks once again. I hate to be a pest, but I have yet another question. I'm trying to use the custom function as an Add In so that I can pass it around to several users. You suggest adding your code (or a modified version of your code) to my workbook, would this work by adding the code to an XLA file, then passing out the Add IN? Or, will the code only work for one specific workbook (i.e. the custom function would not be "global")?

    I appreciate all your help and patience.

    Rvences

  6. #6
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again rvences,

    Unfortunately, the code would not work as an add-in because it is based on a worksheet event and so has to reside in the worksheet's event code module. In addition, I cannot envision any way to make it more general such that it could be made into an add-in or even a workbook-level macro.

    Damon

  7. #7
    New Member
    Join Date
    Feb 2002
    Location
    rvences
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I really apprecite the help. I'll just let the users know that they need to recalc the worksheet before the custom formula will work properly. Again, thanks for the tips!!

    Rvences

  8. #8
    New Member
    Join Date
    Jan 2010
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate on Format Change

    Damon,

    I found your old posting below which nearly does what I need -

    I would like to force a full recalculation if someone changes the colour of any cell - could your sub be modified to do this ?



    Quote Originally Posted by Damon Ostrander View Post
    Hi rvences,

    Yes, calculation is not supposed to occur upon operations that do not change the contents of cells. However, you can capture such an event (it is not a real Excel event) using "trickery". Fortunately you asked at the right time (April 1st).

    First of all, you have to know how the cell format has been changed (i.e., manually vs. via a macro). If it is changed via a macro then it is easy: just re-calculate right after the macro changes the color using the worksheet object's Calculate method. If it has changed because the cell format has been manually set, it requires the trickery I referred to. You can take advantage of the fact that the format cannot be manually edited without selecting the cell first. What you have to do is use the worksheet's SelectionChange event to determine if the last (previous) cell selected was the one you are interested in. In your SelectionChange event routine you must save a pointer to the Target range. If the Target cell is the cell you are interested in you must also save the current value of the format property you are interested in (e.g., ColorIndex). Then when the next cell is selected, simply check to see if the saved cell pointer is the cell of interest. If and only if this is so the format could have been edited. So then check the format to see if it has been changed, and if so, force the worksheet to re-calculate.

    Here's an example that recalculates the sheet whenever the color fill of cell B4 is manually changed.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static LastRange As Range 'The last range selected
    Static LastColorIndex As Integer 'The color index of the last range selected
    If Not Intersect([B4], LastRange) Is Nothing Then
    If LastRange.Cells(1).Interior.ColorIndex <> LastColorIndex Then
    Calculate
    End If
    End If
    Set LastRange = Target
    LastColorIndex = Target.Interior.ColorIndex
    End Sub

    Incidentally, I should mention that the color fill property of a cell is not changed via Conditional Formatting, so none of this applies to Conditional Formatting, but this shouldn't matter anyway since Conditional Formatting occurs based on cell content changes that trigger worksheet re-calculation.

  9. #9
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate on Format Change

    Hi again rvences,

    I haven't check out this code, but I think it should do it:

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
       Static LastRange As Range 'The last range selected
       Static LastColorIndex As Integer 'The color index of the last range selected
    
       If LastRange.Cells(1).Interior.ColorIndex <> LastColorIndex Then
          Application.CalculateFull
       End If
    
       Set LastRange = Target
       LastColorIndex = Target.Interior.ColorIndex
    
    End Sub
    Damon

  10. #10
    Board Regular
    Join Date
    Sep 2008
    Location
    Richmond Hill, Canada
    Posts
    134
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate on Format Change

    Hi rvences,

    Were you able to resolve your issue? I have a similar problem and not able to resolve it.

    Damon, thanks for your valuable in put
    Taha

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
  •