Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Forcing cell with user function to update on workbook open?
In a cell I call this function:
Function CalcILFPath() As String CalcILFPath = Workbooks("CalcILF Add-In.xla").Path End Function Sometimes people have copied the add-in to their add-ins folder, and aren't loading it from the public location. They then don't get updates. We want to be able to display its location. My problem is this cell does not update when opening the workbook. First I tried this block of code: ' force recalc on user's add-in path Application.EnableEvents = False Application.Iteration = False Range("AddinPathUser").Calculate Application.EnableEvents = True That didn't work. So I tried: Range("AddinPathUser").Formula = "=CalcILFPath()" That didn't work either, though I tried this a while back and I can't remember just what its problem was. Ideally I would have the formula in a cell and not call a user function. But I don't know how to do this. Any suggestions? Sometimes I get this error later in my code: The macro "Calc ILF Add-In.xla!'Auto_Add' cannot be found. Note single double quote. What is this error? If I press continue the code precedes normally. Don <www.donwiss.com (e-mail link at home page bottom). |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Forcing cell with user function to update on workbook open?
Don,
Check out Application.Volatile As your function has not parameters, it will not be re-evaluated on a calculation. You could add a dummy parameter that points to a cell with say Now() in it, so it runs each time. NickHK "Don Wiss" wrote in message ... In a cell I call this function: Function CalcILFPath() As String CalcILFPath = Workbooks("CalcILF Add-In.xla").Path End Function Sometimes people have copied the add-in to their add-ins folder, and aren't loading it from the public location. They then don't get updates. We want to be able to display its location. My problem is this cell does not update when opening the workbook. First I tried this block of code: ' force recalc on user's add-in path Application.EnableEvents = False Application.Iteration = False Range("AddinPathUser").Calculate Application.EnableEvents = True That didn't work. So I tried: Range("AddinPathUser").Formula = "=CalcILFPath()" That didn't work either, though I tried this a while back and I can't remember just what its problem was. Ideally I would have the formula in a cell and not call a user function. But I don't know how to do this. Any suggestions? Sometimes I get this error later in my code: The macro "Calc ILF Add-In.xla!'Auto_Add' cannot be found. Note single double quote. What is this error? If I press continue the code precedes normally. Don <www.donwiss.com (e-mail link at home page bottom). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Forcing cell with user function to update on workbook open?
On Wed, 4 Apr 2007 10:18:22 +0800, NickHK wrote:
Don Wiss wrote: In a cell I call this function: Function CalcILFPath() As String CalcILFPath = Workbooks("CalcILF Add-In.xla").Path End Function Sometimes people have copied the add-in to their add-ins folder, and aren't loading it from the public location. They then don't get updates. We want to be able to display its location. My problem is this cell does not update when opening the workbook. First I tried this block of code: ' force recalc on user's add-in path Application.EnableEvents = False Application.Iteration = False Range("AddinPathUser").Calculate Application.EnableEvents = True That didn't work. Check out Application.Volatile I had tried that, and it didn't work. As your function has not parameters, it will not be re-evaluated on a calculation. You could add a dummy parameter that points to a cell with say Now() in it, so it runs each time. The lack of an argument makes sense. Having it point to a cell with =NOW() worked for me. Though I do have to use the above code on the =NOW() cell to force it to change, which forces the user function to be called. Having the above code point to the cell with the user function didn't do the trick. For someone else this did not work until Application.Volatile was added. Though all this has a quirk. Before it updates (at least in the instances when it changes) it briefly flashes #NAME? in the cell. I haven't figured out why. Sometimes I get this error later in my code: The macro "Calc ILF Add-In.xla!'Auto_Add' cannot be found. Note single double quote. What is this error? If I press continue the code precedes normally. I did some more searching on the web for this one. I found the solution. After an Addins.Installed = True is issued, the add-in has the focus. My crashing line of code was expecting my workbook to have it. So issuing a ThisWorkbook.Activate right after the turning on the add-in solved this. Don <www.donwiss.com (e-mail link at home page bottom). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Forcing a workbook to open on a certain page | Excel Discussion (Misc queries) | |||
update workbook on open | New Users to Excel | |||
forcing excel to update the Cell Link when copying Combo Boxes | Excel Worksheet Functions | |||
forcing the opening cell to open at top? | Excel Programming | |||
Saving a Workbook: Forcing User to Rename before Saving | Excel Programming |