Which Dialog Button? (XL95 in XL2007!)

[Anthony Berglas]

Excel 95 dialogs normally associate a macro with buttons other than OK
and Cancel. The macro runs when the button is pressed, all is good.

Except that in Excel 2007, if the *current* workbook does not have
macros enabled, then the macro does not run. This is regardless of
the the fact that the dialog is run from an authrorized, installed add-
in.

So I was hoping to be able to simply mark the button as Dismis, and
then test which button was pressed from the code that executed the
Show. But I cannot find any way to do that. Application.caller does
not have this information. No obvious Button property.

Any ideas most welcome.

Anthony

(There are good reasons to use Excel 95 dialogs, even today. Fonts
within boxes, references etc. Besides the obvious one of not wanting
to have to change a lot of code.)

 

[Jan Karel Pieterse]

Hi Anthony,

Except that in Excel 2007, if the *current* workbook does not have
macros enabled, then the macro does not run. This is regardless of
the the fact that the dialog is run from an authrorized, installed add-
in.

I don't think this is correct, for me it works without a problem.
Download my Autosafe from my site (see below), it is an addin that still uses
a dialog sheet (look on the addins tab to open the settings screen of Autosafe)
and it works.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Anthony Berglas]

Hi Anthony,


I don't think this is correct, for me it works without a problem.
Download my Autosafe from my site (see below), it is an  addin that still uses
a dialog sheet (look on the addins tab to open the settings screen of Autosafe)
and it works.

Regards,

Jan Karel Pieterse
Excel MVPhttp://www.jkp-ads.com
Member of:
Professional Office Developer Associationwww.proofficedev.com

To be clear, if the current workbook has no macros at all, then all is
well. But if the current workbook does have a macro in it, and it has
not been explicitly enabled by the user, then add-in functions
triggered by a Dialog button press will not run, and produces an
obscure error message (function not found). Definitely a bug in Excel
2007, but I cannot see a work around.

Anthony

 

[Jan Karel Pieterse]

Hi Anthony,

To be clear, if the current workbook has no macros at all, then all is
well. But if the current workbook does have a macro in it, and it has
not been explicitly enabled by the user, then add-in functions
triggered by a Dialog button press will not run, and produces an
obscure error message (function not found). Definitely a bug in Excel
2007, but I cannot see a work around.

I tested this on my Excel 2007 (ouch, now I recall I have a beta SP
installed!!!) and there it works flawlessly. If it fails to work on your
machine I guess you have to wait until that Service pack is released.

Or stop using dialog sheets altogether, but use a Userform instead. I recommend
you to do that anyway. Dialog sheets may soon no longer be supported, since
they were superseded by userforms a long time ago with Excel 97.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Jan Karel Pieterse]

Hi Anthony,

To be clear, if the current workbook has no macros at all, then all is
well. But if the current workbook does have a macro in it, and it has
not been explicitly enabled by the user, then add-in functions
triggered by a Dialog button press will not run, and produces an
obscure error message (function not found). Definitely a bug in Excel
2007, but I cannot see a work around.

Forget my last message, I was wrong, it also fails on my system.

What happens if you pre-pend the macroname with the workbook name (when
assigning the macro to the button)?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Jan Karel Pieterse]

Hi Anthony,

I filed a bug report at MSFT.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Anthony Berglas]

Hi Anthony,


Forget my last message, I was wrong, it also fails on my system.

What happens if you pre-pend the macroname with the workbook name (when
assigning the macro to the button)?

The workbook name seems to be prepended. And this seens to be the
case when viewed from Excl 2007. Ie.

Debug.Print ActiveWorkbook.DialogSheets("configdialog").Buttons
("Button 65").OnAction
shows
SDetective_Library.xls!Public.fsUpdateLicense

Sadly the "new" Excel 97 VBA dialogs do not support the same
functionality as the old 1995 ones. So an upgrade is a trade off. I
think that I'll just live with the rather obscure bug having added a
note to the docs (which will never be read!).

Thanks for filing a bug report with Microsoft, although I doubt if it
will be acted upon.

Anthony

 

[Jan Karel Pieterse]

Hi Anthony,

Sadly the "new" Excel 97 VBA dialogs do not support the same
functionality as the old 1995 ones.

I know, but most of those can be worked around.
Sometimes using one of the windows common controls helps (but those have there
own "problems").

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com