Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update custom formulas automatically
I was kindly given some code to count the incidence of a background colour in
a range of cells. Is there any way of having the answer automatically update as now if I change a background colour the formula does not update automatically. I'm pressing f2 on formula and pressing return to update but I don't want to have to do this as there are many incidences of the formula. Any help appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update custom formulas automatically
Hi there,
Format changes do not trigger any events in Excel, so there is no direct way of capturing color change of a cell. Some of the work-arounds could be: - to include Me.Calculate into the ChangeSelection event of the worksheet - run ActiveSheet.Calculate, say, every second, using the OnTime method but I wouldn't recommend that, especially given that, as you said, "there are many incidences of the formula". Regards, KL " Inserting an option button in Word" soft.com wrote in message ... I was kindly given some code to count the incidence of a background colour in a range of cells. Is there any way of having the answer automatically update as now if I change a background colour the formula does not update automatically. I'm pressing f2 on formula and pressing return to update but I don't want to have to do this as there are many incidences of the formula. Any help appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update custom formulas automatically
maybe the UDF this not contain the following line of code at the start
of the function Application.Volatile True DM Unseen |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update custom formulas automatically
This will not force a recalculation on a colour change.
If you have to do this, and you have found it's major flaw, what I do is to add a button to the toolbars that is used to set the colour. It provides a colour picker, and when OKed, forces a recalculation. -- HTH RP (remove nothere from the email address if mailing direct) "DM Unseen" wrote in message oups.com... maybe the UDF this not contain the following line of code at the start of the function Application.Volatile True DM Unseen |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update custom formulas automatically
Hi DM Unseen,
This won't help since, as I said, color change doesn't trigger any event (incl. recalculation) so Application.Volatile will only fire out upon a real change on sheet. Regards, KL "DM Unseen" wrote in message oups.com... maybe the UDF this not contain the following line of code at the start of the function Application.Volatile True DM Unseen |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update custom formulas automatically
KL, Bob
thanks for the update. This means a recalculation needs to be forced (and volatile should be set) The button trick should work, and is probably the easiest. PS for real die-hards |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update custom formulas automatically
Bob, can you provide an example of that code. TIA
Greg |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update custom formulas automatically
Okay, but be warned, it is incredibly complex.
Sub SetCellColour() Application.Dialogs(xlDialogPatterns).Show ActiveSheet.Calculate End Sub LOL! If you are using a UDF for the colorindex, don't forget you will need Application.Volatile, otherwise the recalc doesn't invoke it. -- HTH RP (remove nothere from the email address if mailing direct) "GregR" wrote in message oups.com... Bob, can you provide an example of that code. TIA Greg |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update custom formulas automatically
Bob, LOL!!!! I am sure it took you all morning to write that code.
Thanks Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically update month | Excel Discussion (Misc queries) | |||
Update one worksheet tab to automatically update all other tabs? | Excel Worksheet Functions | |||
using weekday formulas to automatically update a date range | Excel Discussion (Misc queries) | |||
custom lists and custom formulas? | Excel Discussion (Misc queries) | |||
Update custom outlook form from within Excel | Excel Programming |