Replies

Gotcha. You are correct. I only want to replace formulas that contain one or more of these custom functions. Thanks again.

Why do you say that replacing formulas with their result values is not acceptable?

The previous post worked like a champ.

Thanks! That’s exactly what I was looking for.

Looking through those previous threads I saw mention of a program that would automatically click the OL wraning button for you. Do you know what the program is and or where to get it?

Thanks Again

Close. What you suggested does work however ultimately I want my referencing range of cells to be dynamic to the referred range. EX: If the name on worksheet ‘A’ called ‘Test’ consists of the range A1:A3 and on worksheet ‘B’ I have a named range consisting of cells A1:A3 that have an array formula set equal to A!Test then if the range ‘Test’ on worksheet ‘A’ is increased by one row due to a row insertion I would like for the named range on worksheet ‘B’ to reflect the same.

Great tip! The problem though is that the other Add-In is going to run regardless of what I do so I do not want to call the individual subs and functions from my Add-In and then have that Add-In run those same procedures again. Ideally I’d like to hook into the Add-In so that when a procedure is executed my Add-In could intercept the call, do anything it needs to do and then return control back to the other Add-In’s code. I believe this would be similiar to sub-classing in VB.

Thanks again!
Ed

The Add-in is not protected. I can work with the code directly however I’m trying to avoid altering it as much ass possible to avoid problems with updates to the Add-in. The reason for what I am trying to do is that I do not believe the company that makes this Add-In will incorporate my added functionality into there product however i want to use that functionality for my own reports. If I can create an Add-in of my own that hooks into this Add-in then I can place all of my additional functionality into my own Add-in and not worry about having to add code again when the company sends out a new version of their Add-In.

I hope that’s enough info.

Thanks!

Thanks for the links. I have read over the list of file types that OL XP blocks and none of the Office product files except for Access are included. I swear I remeber reading that OL XP would block any of the Office file types. DO you recall seeing anything to that extent?
Since it appears that these files will come through OK can you think of a good reason for not upgrading from OL 2K to OL XP?

No and that is what has me confused. I’m tempted to upgrade OL on my system to see if I get the same results.

Office XP.

I’m still not clear as to whether OL XP is supposed to automatically block files of certain types like those of the office products. I have a co-worker who wanted to install OL XP and I recommended that he not do so because of these file blocks. He said he had XP at home and did not have this problem. After installing OL XP here at work we verified that an xls file could be sent and opened with no problem. This is contradictory to what I thought OL XP would do.

Thanks for the info. Now another Q. This is the first time I’ve tried using a combobox in Excel and I can’t figure what property to set for the values in the combobox. In VB this would be the rowsourse.

Also. If I want this same type of control to appear over ever row in this column when the user selects a cell in that column how would I do so?

Thanks to everyone for their help. I found the problem. I apologize for waiting everyone’s time on this one. It was my own fault. I had modified the original code from Andrew by including a statement to set the value of indx to 1 before the loop begin. The reason was the xla Add-in that I had placed the code in had the setting ‘Option Base1’ which starts arrays off at 1 instead of 0. I did this last week and now that I was back from vacation I wanted to do something similar as workbook macro so I copied and pasted the code from the xla Add-In. I couldn’t figure out why it was working in the Add-in other then maybe the Add-ins original author had created the Add-in in a way that just worked. The Add-in’s code is almost entirely Late-Binding and uses an older style of coding such as using symbols like $ (for string) so I figured that the thing was just ignoring the Subscript error somehow and moving forward. OH Well! Lesson learned.
This is not the first time I have been thrown off because of working with older coding styles. Just out of curiosity does anyone have an opinion about things like using ‘Option Base 1’? Every thing I have ever read about VB coding talks about staying away from the older style of writing code.

Thanks Again!

The only change I made to Andrews original code was to replace = with so…
ws.Range(“A1”) = “x” Then became..
ws.Range(“A1”) “x” Then which ends up adding all the sheets in the work book which is what I want.