PDA

View Full Version : Displaying a UDF within insertFunction



chrisjones
07-09-2010, 08:55 AM
When I run the code below I get the following error mesage

Run-time error '1004' Application-defined or object-defined error

what am I doing wrong?



Function TestMacro()
MsgBox ActiveWorkbook.Name
End Function

Sub AddUDFToCustomCategory()
Application.MacroOptions Macro:="TestMacro", Category:="My Custom Category"
End Sub

austenr
07-09-2010, 09:42 AM
Runs clean for me in 2003.

chrisjones
07-09-2010, 09:55 AM
Yeh Im in 2003 too, It runs cleans for me when I put in a new module. However it gave me that error when I put that code in VBAProject -> Sheet1

Any reason for that?

Bob Phillips
07-09-2010, 09:57 AM
When I run the code below I get the following error mesage

Run-time error '1004' Application-defined or object-defined error


Using Excel 2007.

Simon Lloyd
07-09-2010, 10:36 AM
Yeh Im in 2003 too, It runs cleans for me when I put in a new module. However it gave me that error when I put that code in VBAProject -> Sheet1

Any reason for that?The code also isn't worksheet event code so no point in putting it in sheet1 code module.

chrisjones
07-11-2010, 08:48 AM
Ok, so do you put event handling code in sheets and business logic in modules?

Simon Lloyd
07-11-2010, 04:25 PM
Ok, so do you put event handling code in sheets and business logic in modules?If you need a specific sheet event to trigger then put the event code in the pertinent sheet code module, if it's global then but it in the thisworkbook code module, pretty much all other code should be in a standard module.