Creating a dialog via Excel DNA function - but keeps popping up on every refresh

[saurabh....@gmail.com]

So here's my setup:

* I have an Excel DNA function ([ExcelFunction]) that takes different keywords, and returns different results according to the keyword.

** For e.g., let's call my function "myFunc", 

       calling =myFunc("states") would return a list of US states, which are then displayed on the worksheet using a 2-dimensional object array.

       calling =myFunc("cities") would return a list of US cities

* One of the keywords is "HELP" (i.e. =myFunc("HELP") ) --> this brings up a modal dialog box that allows people to lookup available keywords, read some helptext about the keyword etc.

Here's the problem:

* When a user uses the =myFunc("HELP") to bring up the help dialog, that works fine, dialog comes up, user interacts with it, closes it, all good.

* However, the function call (i.e. =myFunc("HELP") ) gets embedded in the cell. Next time the user recalculates the worksheet (we advise them to use Ctrl + Alt + Shift + F9) - it recalculates all the formulas, *INCLUDING* the one that brings up the dialog box, and promptly brings up the help dialog box, even though the user did not explicitly invoke it.

My question is- what do I do to prevent this behavior? I tried creating a seperate excel function called =myFuncHelp that returns void - that simply puts a FALSE in the cell, but the formula still stays as is. I guess I need some way so that the excel function is not really embedded in the cell as a formula, but instead simply shows the dialog and leaves the cell empty.

Any pointers are appreciated.

[Govert van Drimmelen]

It sounds like you're abusing user-defined worksheet functions to do the work of macros.

Instead of making functions that your user calls by typing into a cell formula, perhaps you need to make macros that they can run from a ribbon extension, shortcut keys or a context menu.

Excel-DNA allows you to easily make a macro (with a menu under Add-Ins and the shortcut key Ctrl+Shift+G) like this:

[ExcelCommand(MenuName="My Commands", MenuText="Show Help", ShortCut="^+H")

public static void ShowHelp() { .....}

To properly use the ribbon with images and keyboard tips takes a few more steps - there are some samples here: https://github.com/Excel-DNA/ExcelDna/tree/master/Distribution/Samples/Ribbon

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of saurabh....@gmail.com [saurabh....@gmail.com]
Sent: 15 September 2016 09:26 PM
To: Excel-DNA
Subject: [ExcelDna] Creating a dialog via Excel DNA function - but keeps popping up on every refresh

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[saurabh....@gmail.com]

Thanks Govert for the quick response. 

Okay, I tried it out, definitely much better than using it via the function. I will fiddle around more and try to incorporate a better look & feel. Thank you for the pointer.

To post to this group, send email to exc...@googlegroups.com.