I have created a custom function and would like it to be
activated from a button on my toolbar. I have tried
recording a macro to run the function but this did not
work as the define range box was not presented.
You'd need to generate that yourself, then pass the selected range to
the function. You can do that by having an Application.InputBox method
in your macro. (Note: NOT the standard InputBox function; it needs to
be the Application.Input box one which allows you to ask for a range
for input.) This is an extract from on-line help note Type 8):
InputBox Method
Displays a dialog box for user input. Returns the information entered
in the dialog box.
expression.InputBox(Prompt, Title, Default, Left, Top, HelpFile,
HelpContextId, Type)
expression Required. An expression that returns an Application
object.
Prompt Required String. The message to be displayed in the dialog
box. This can be a string, a number, a date, or a Boolean value
(Microsoft Excel automatically coerces the value to a String before
it's displayed).
Title Optional Variant. The title for the input box. If this
argument is omitted, the default title is "Input."
Default Optional Variant. Specifies a value that will appear in the
text box when the dialog box is initially displayed. If this argument
is omitted, the text box is left empty. This value can be a Range
object.
Left Optional Variant. Specifies an x position for the dialog box in
relation to the upper-left corner of the screen, in points.
Top Optional Variant. Specifies a y position for the dialog box in
relation to the upper-left corner of the screen, in points.
HelpFile Optional Variant. The name of the Help file for this input
box. If the HelpFile and HelpContextID arguments are present, a Help
button will appear in the dialog box.
HelpContextId Optional Variant. The context ID number of the Help
topic in HelpFile.
Type Optional Variant. Specifies the return data type. If this
argument is omitted, the dialog box returns text. Can be one or a sum
of the following values.
Value Meaning
0 A formula
1 A number
2 Text (a string)
4 A logical value (True or False)
8 A cell reference, as a Range object
16 An error value, such as #N/A
64 An array of values
