Been habing a chat with a very experienced Excel user today and the consensus is that you have two options, both of which use Macros and VBA sadly.
First, as it is not possible to call either a macro or create a message box directly from an Excel function/formula, you can create your own UDF (user defined function) and this can be included into a formula that you create. The UDF's capabilities are limited but would include the ability to display a message box. The problem is that you may well fall foul of POI's forumla parser when creating the formulae to add to the worksheet; simply put, this is because POI does not recognise the name of the formula and so might throw an exception. I do not know this for a fact and you would have to try it by creating a UDF and then using it in a formula. Also, as POI is unable to create or edit macros, you would need to use Excel to create a template file that contained the UDF and which could be opened and populated using POI. Secondly, there are certain events that are always triggered when the user interacts with the workbook. It would be possible to create code in one of these events to check the change the user had made and ensure it met with your criteria. There are three possible problems with this technique however. Firstly, as POI cannot be used to create or edit a macro, you will have to create a template contaiing the macro. Secondly, when the user opens the completed workbook, they will see a message telling them the workbook contains macros and offering them the opportunity to disable them. Itis possible to circumvent this by signing the macro but even this option would not be available to you if you were creating a template for the older binary format file (.xls). Finally, you do not want the macro to check verey cell interation and so it is typical to create a list of cells that should be listened to. You will need to ensure this list is complete and accurate. Yours Mark B -- View this message in context: http://apache-poi.1045710.n5.nabble.com/Formula-Support-in-Apache-POI-tp3374589p3376004.html Sent from the POI - User mailing list archive at Nabble.com. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
