Custom Function Category - Excel 4 Macro Sheet
The following code will add a custom category to the excel function wizard, and then assign all of my custom built functions to that category. The routine even solves a problem which microsoft states as having no solution.
However, it uses a test1 and test2 function in order to create the category and evaluate it's Category ID.
The problem is that I cannot seem to hide or delete the Test1 and Test2 functions once the custom category has been created. I do not want the Test1 and Test2 functions to be visible in the functions wizard.
I know that it is something to do with the fact that they were created in the Excel 4 macro sheet, but how do I stop them from showing.
And now the code.....
Sub AddFunctionToCategory()
Dim TheCategoryID As Integer
Dim TheNewCategory As String
Dim TheCurrentCategory As String
TheNewCategory = "My New Category"
Workbooks.Add
Worksheets.Add Type:=xlExcel4MacroSheet
'add a test function to create the new category
ActiveWorkbook.Names.Add Name:="Test1", RefersTo:="sheet1!$a$1", _
Category:=TheNewCategory, MacroType:=xlFunction
'add a second function to the User Defined category
ActiveWorkbook.Names.Add Name:="Test2", RefersTo:="sheet1!$a$1", _
Category:="User Defined", MacroType:=xlFunction
For i = 1 To 100
'Set the category of function 2 to i and see if the
'category name matches the new category
TheCategoryID = i
ActiveWorkbook.Application
Category:=TheCategoryID
TheCurrentCategory = ActiveWorkbook.Application
If TheCurrentCategory = TheNewCategory Then
Exit For
End If
Next i
ActiveWorkbook.Application
ActiveWorkbook.Application
ActiveWorkbook.Names("Test
ActiveWorkbook.Names("Test
ActiveWorkbook.Close (False)
End Sub
However, it uses a test1 and test2 function in order to create the category and evaluate it's Category ID.
The problem is that I cannot seem to hide or delete the Test1 and Test2 functions once the custom category has been created. I do not want the Test1 and Test2 functions to be visible in the functions wizard.
I know that it is something to do with the fact that they were created in the Excel 4 macro sheet, but how do I stop them from showing.
And now the code.....
Sub AddFunctionToCategory()
Dim TheCategoryID As Integer
Dim TheNewCategory As String
Dim TheCurrentCategory As String
TheNewCategory = "My New Category"
Workbooks.Add
Worksheets.Add Type:=xlExcel4MacroSheet
'add a test function to create the new category
ActiveWorkbook.Names.Add Name:="Test1", RefersTo:="sheet1!$a$1", _
Category:=TheNewCategory, MacroType:=xlFunction
'add a second function to the User Defined category
ActiveWorkbook.Names.Add Name:="Test2", RefersTo:="sheet1!$a$1", _
Category:="User Defined", MacroType:=xlFunction
For i = 1 To 100
'Set the category of function 2 to i and see if the
'category name matches the new category
TheCategoryID = i
ActiveWorkbook.Application
Category:=TheCategoryID
TheCurrentCategory = ActiveWorkbook.Application
If TheCurrentCategory = TheNewCategory Then
Exit For
End If
Next i
ActiveWorkbook.Application
ActiveWorkbook.Application
ActiveWorkbook.Names("Test
ActiveWorkbook.Names("Test
ActiveWorkbook.Close (False)
End Sub
Hi Leedurrant,
I've tested your code, and the lines you posted/used seem to work correctly for me:
ActiveWorkbook.Names("Test
ActiveWorkbook.Names("Test
The behaviour is strange though, the delete seems to delete the names, but they remain in the Function Wizard Categories List. The Visible = False propery hides the names in my test.
I've tested your code, and the lines you posted/used seem to work correctly for me:
ActiveWorkbook.Names("Test
ActiveWorkbook.Names("Test
The behaviour is strange though, the delete seems to delete the names, but they remain in the Function Wizard Categories List. The Visible = False propery hides the names in my test.
ASKER
I have tried both of these recommendations, but the problem still remains.
For your information, the code is called from the ThisWorkbook Workbook_Open of an xla file.
Do either of you have any other ideas that will resolve this problem?
Thanks
Lee
For your information, the code is called from the ThisWorkbook Workbook_Open of an xla file.
Do either of you have any other ideas that will resolve this problem?
Thanks
Lee
Hi Leedurrant,
I've put your code in the auto-open event of an xla file, and without any problem your original code works perfectly.
Afterwards I've tested this, works also, and maybe a solution:
Sub AddFunctionToCategory()
Dim TheCategoryID As Integer
Dim TheNewCategory As String
Dim TheCurrentCategory As String
TheNewCategory = "My New Category"
Workbooks.Add
Worksheets.Add Type:=xlExcel4MacroSheet
'add a test function to create the new category
ActiveWorkbook.Names.Add Name:="MyFunction1", RefersTo:="sheet1!$a$1", _
Category:=TheNewCategory, MacroType:=xlFunction
'add a second function to the User Defined category
ActiveWorkbook.Names.Add Name:="MyFunction2", RefersTo:="sheet1!$a$1", _
Category:="User Defined", MacroType:=xlFunction
For i = 1 To 100
'Set the category of function 2 to i and see if the
'category name matches the new category
TheCategoryID = i
ActiveWorkbook.Application
Category:=TheCategoryID
TheCurrentCategory = ActiveWorkbook.Application
If TheCurrentCategory = TheNewCategory Then
Exit For
End If
Next i
ActiveWorkbook.Close (False)
End Sub
In the Workbook open event I just placed this code:
Private Sub Workbook_Open()
AddFunctionToCategory
End Sub
Hope this helps
Calacuccia
I've put your code in the auto-open event of an xla file, and without any problem your original code works perfectly.
Afterwards I've tested this, works also, and maybe a solution:
Sub AddFunctionToCategory()
Dim TheCategoryID As Integer
Dim TheNewCategory As String
Dim TheCurrentCategory As String
TheNewCategory = "My New Category"
Workbooks.Add
Worksheets.Add Type:=xlExcel4MacroSheet
'add a test function to create the new category
ActiveWorkbook.Names.Add Name:="MyFunction1", RefersTo:="sheet1!$a$1", _
Category:=TheNewCategory, MacroType:=xlFunction
'add a second function to the User Defined category
ActiveWorkbook.Names.Add Name:="MyFunction2", RefersTo:="sheet1!$a$1", _
Category:="User Defined", MacroType:=xlFunction
For i = 1 To 100
'Set the category of function 2 to i and see if the
'category name matches the new category
TheCategoryID = i
ActiveWorkbook.Application
Category:=TheCategoryID
TheCurrentCategory = ActiveWorkbook.Application
If TheCurrentCategory = TheNewCategory Then
Exit For
End If
Next i
ActiveWorkbook.Close (False)
End Sub
In the Workbook open event I just placed this code:
Private Sub Workbook_Open()
AddFunctionToCategory
End Sub
Hope this helps
Calacuccia
Any better, Leedurrant ?
ASKER
Sorry Calacuccia,
I did type out a comment, but it seems to have dissappeared.
Your previous suggestion is the way in which I am actually calling the macro at the moment.
The problem is still unsolved. I am not sure why it is that it doesn't delete/hide the functions for me.
I did type out a comment, but it seems to have dissappeared.
Your previous suggestion is the way in which I am actually calling the macro at the moment.
The problem is still unsolved. I am not sure why it is that it doesn't delete/hide the functions for me.
Leedurrant:
I suggest you delete this question and ask a new one to get new attention if the problem persists..
Calacuccia (out of ideas myself)
I suggest you delete this question and ask a new one to get new attention if the problem persists..
Calacuccia (out of ideas myself)
ASKER
Calacuccia,
I resolved this problem about a month ago. Sorry, I didn't realize that the question was still outstanding.
FYI, I changed the function names and found that the Test1 and test2 names still appeared. From this I realized that the functions were actually kind of imbedded into the add-in file from when I didn't call the delete line.
I simply copied all of the coding into a new add-in and hey-presto it worked.
Thanks for your help on this one anyway
I resolved this problem about a month ago. Sorry, I didn't realize that the question was still outstanding.
FYI, I changed the function names and found that the Test1 and test2 names still appeared. From this I realized that the functions were actually kind of imbedded into the add-in file from when I didn't call the delete line.
I simply copied all of the coding into a new add-in and hey-presto it worked.
Thanks for your help on this one anyway
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Comment accepted as answer
ASKER
Go on then, I think you've helped me enough whilst I've been building this add-in.
Thanks again....
Thanks again....
My Pleasure, LeeDurrant.
Hope to be able to help you again later, and allow me to learn from it, as I certainly did with your custom function categories.
Cheers !
Calacuccia
Hope to be able to help you again later, and allow me to learn from it, as I certainly did with your custom function categories.
Cheers !
Calacuccia
ActiveWorkbook.Names.Item(
ActiveWorkbook.Names.Item(