Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function won't update a cell
Hi, This has been asked before but I've not found anything recent. I'm
using Excel 2003 with either VSTO or VBA. I have an RTD server which works fine, but as we know RTD does not trigger the worksheet's change event. There have been lots of suggestions that don't work around this, but I thought I was getting close with this one. Create a function which points at the cell to monitor and then do something, something like this: ---------------------------------------------------------------------------------------------------------------- Public Function Test(r As range) As String On Error GoTo Failed: r.Font.ColorIndex = 3 Test = "5" CleanExit: Exit Function Failed: Debug.Print Err.Description End Function ----------------------------------------------------------------------------------------------------------------- The function fires at the correct time, yay I thought, except you'll note the line which changes the color. This does not work. No error, it just doesn't do anything to the range. I've tried all sorts of things, but I don't seem to be able to affect other cells from this function. Does anyone have any ideas of why? If it's just designed that way, does anyone else have any idea of how to generate an event when RTD data arrives? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function won't update a cell
Hi
VBA functions cannot change properties, only return a value. you CANNOT change a font property for example. regards Paul On Jun 6, 10:58*am, DeveloperX wrote: Hi, This has been asked before but I've not found anything recent. I'm using Excel 2003 with either VSTO or VBA. I have an RTD server which works fine, but as we know RTD does not trigger the worksheet's change event. There have been lots of suggestions that don't work around this, but I thought I was getting close with this one. Create a function which points at the cell to monitor and then do something, something like this: ---------------------------------------------------------------------------*------------------------------------- Public Function Test(r As range) As String On Error GoTo Failed: r.Font.ColorIndex = 3 Test = "5" CleanExit: Exit Function Failed: Debug.Print Err.Description End Function ---------------------------------------------------------------------------*-------------------------------------- The function fires at the correct time, yay I thought, except you'll note the line which changes the color. This does not work. No error, it just doesn't do anything to the range. I've tried all sorts of things, but I don't seem to be able to affect other cells from this function. Does anyone have any ideas of why? If it's just designed that way, does anyone else have any idea of how to generate an event when RTD data arrives? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function won't update a cell
On Jun 6, 11:03*am, wrote:
Hi VBA functions cannot change properties, only return a value. you CANNOT change a font property for example. regards Paul On Jun 6, 10:58*am, DeveloperX wrote: Hi, This has been asked before but I've not found anything recent. I'm using Excel 2003 with either VSTO or VBA. I have an RTD server which works fine, but as we know RTD does not trigger the worksheet's change event. There have been lots of suggestions that don't work around this, but I thought I was getting close with this one. Create a function which points at the cell to monitor and then do something, something like this: ---------------------------------------------------------------------------**------------------------------------- Public Function Test(r As range) As String On Error GoTo Failed: r.Font.ColorIndex = 3 Test = "5" CleanExit: Exit Function Failed: Debug.Print Err.Description End Function ---------------------------------------------------------------------------**-------------------------------------- The function fires at the correct time, yay I thought, except you'll note the line which changes the color. This does not work. No error, it just doesn't do anything to the range. I've tried all sorts of things, but I don't seem to be able to affect other cells from this function. Does anyone have any ideas of why? If it's just designed that way, does anyone else have any idea of how to generate an event when RTD data arrives? Thanks That is hugely annoying. Thank you for your prompt reply. If anyone has figured out how to generate an event when RTD data arrives, please let me know. Cheers DevX |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function won't update a cell
Link a cell to one of the RTD cells, and use the worksheet_calculate.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DeveloperX" wrote in message ... On Jun 6, 11:03 am, wrote: Hi VBA functions cannot change properties, only return a value. you CANNOT change a font property for example. regards Paul On Jun 6, 10:58 am, DeveloperX wrote: Hi, This has been asked before but I've not found anything recent. I'm using Excel 2003 with either VSTO or VBA. I have an RTD server which works fine, but as we know RTD does not trigger the worksheet's change event. There have been lots of suggestions that don't work around this, but I thought I was getting close with this one. Create a function which points at the cell to monitor and then do something, something like this: ---------------------------------------------------------------------------**------------------------------------- Public Function Test(r As range) As String On Error GoTo Failed: r.Font.ColorIndex = 3 Test = "5" CleanExit: Exit Function Failed: Debug.Print Err.Description End Function ---------------------------------------------------------------------------**-------------------------------------- The function fires at the correct time, yay I thought, except you'll note the line which changes the color. This does not work. No error, it just doesn't do anything to the range. I've tried all sorts of things, but I don't seem to be able to affect other cells from this function. Does anyone have any ideas of why? If it's just designed that way, does anyone else have any idea of how to generate an event when RTD data arrives? Thanks That is hugely annoying. Thank you for your prompt reply. If anyone has figured out how to generate an event when RTD data arrives, please let me know. Cheers DevX |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function won't update a cell
On Jun 6, 12:05*pm, "Bob Phillips" wrote:
Link a cell to one of the RTD cells, and use the worksheet_calculate. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DeveloperX" wrote in message ... On Jun 6, 11:03 am, wrote: Hi VBA functions cannot change properties, only return a value. you CANNOT change a font property for example. regards Paul On Jun 6, 10:58 am, DeveloperX wrote: Hi, This has been asked before but I've not found anything recent. I'm using Excel 2003 with either VSTO or VBA. I have an RTD server which works fine, but as we know RTD does not trigger the worksheet's change event. There have been lots of suggestions that don't work around this, but I thought I was getting close with this one. Create a function which points at the cell to monitor and then do something, something like this: ---------------------------------------------------------------------------***------------------------------------- Public Function Test(r As range) As String On Error GoTo Failed: r.Font.ColorIndex = 3 Test = "5" CleanExit: Exit Function Failed: Debug.Print Err.Description End Function ---------------------------------------------------------------------------***-------------------------------------- The function fires at the correct time, yay I thought, except you'll note the line which changes the color. This does not work. No error, it just doesn't do anything to the range. I've tried all sorts of things, but I don't seem to be able to affect other cells from this function. Does anyone have any ideas of why? If it's just designed that way, does anyone else have any idea of how to generate an event when RTD data arrives? Thanks That is hugely annoying. Thank you for your prompt reply. If anyone has figured out how to generate an event when RTD data arrives, please let me know. Cheers DevX Thanks Bob. I was trying to avoid that approach as it means comparing lots of cells that could change as opposed to worrying about only the cells that change. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function won't update a cell
You might be able to get away with one comparison, like
=COUNTIF(A1:M1000,"X") shouldn't matter what you test for, the fact that one of those cells changes should trigger a recalc for that formula, and thus your code. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DeveloperX" wrote in message ... On Jun 6, 12:05 pm, "Bob Phillips" wrote: Link a cell to one of the RTD cells, and use the worksheet_calculate. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DeveloperX" wrote in message ... On Jun 6, 11:03 am, wrote: Hi VBA functions cannot change properties, only return a value. you CANNOT change a font property for example. regards Paul On Jun 6, 10:58 am, DeveloperX wrote: Hi, This has been asked before but I've not found anything recent. I'm using Excel 2003 with either VSTO or VBA. I have an RTD server which works fine, but as we know RTD does not trigger the worksheet's change event. There have been lots of suggestions that don't work around this, but I thought I was getting close with this one. Create a function which points at the cell to monitor and then do something, something like this: ---------------------------------------------------------------------------***------------------------------------- Public Function Test(r As range) As String On Error GoTo Failed: r.Font.ColorIndex = 3 Test = "5" CleanExit: Exit Function Failed: Debug.Print Err.Description End Function ---------------------------------------------------------------------------***-------------------------------------- The function fires at the correct time, yay I thought, except you'll note the line which changes the color. This does not work. No error, it just doesn't do anything to the range. I've tried all sorts of things, but I don't seem to be able to affect other cells from this function. Does anyone have any ideas of why? If it's just designed that way, does anyone else have any idea of how to generate an event when RTD data arrives? Thanks That is hugely annoying. Thank you for your prompt reply. If anyone has figured out how to generate an event when RTD data arrives, please let me know. Cheers DevX Thanks Bob. I was trying to avoid that approach as it means comparing lots of cells that could change as opposed to worrying about only the cells that change. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation lists update orginal cell with list update | Excel Worksheet Functions | |||
Forcing cell with user function to update on workbook open? | Excel Programming | |||
How to make a function in a cell update | Excel Discussion (Misc queries) | |||
how to using Function update cell value | Excel Programming | |||
function need to return value and update one cell value | Excel Programming |