Launch Name Manager function via VBA - Excel 2010 2

[markronz]

Hello all-
I am currently using Excel 2010. I have added a button to a form, and I've assigned it to a macro. Within that macro, I would just like to launch the built in Name Manager in Excel. Does anyone know how to code such a thing in VBA? I obviously know how to click on the button on the ribbon to launch the Name Manager, but I don't know how to launch it via VBA code. Any ideas for me?

Thanks!

 

[PHV]

Did you try the macro recorder ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[markronz]

Hi, thanks for the response. Yes I did try the macro recorder, but simply clicking on the button to launch the Name Manager does not record any actions in the macro. I tried to take some action within the Name Manager too, but all it records is the code to perform the change, not the code to launch the Name Manager. So that didn't work unfortunately.

 

[SkipVought]

What is it that you want to do in the Name Manager? Please explain IN DETAIL,

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[markronz]

I do not want to do anything within the Name Manager. I mean I know that I can create/edit/delete names within VBA code directly, but that's not what I want to do. All I want my button to do is to launch the Name Manager, not take any actions within it.

The reason for this is, I have a need for an average user to work with named ranges. They do not know VBA, but they are smart enough to figure out how to use the Name Manager tool to update name ranges. Now for myself, I had to customize my ribbon action bar in Excel to even get the Name Manager button there. I assume that this ribbon is specific to a users machine. So, by default, there is no button on the default ribbon to launch Name Manager. So I'm trying to circumvent that. I don't want to walk every user through adding the Name Manager to their ribbon. I made my own button, and my intention was to simply make it launch Name Manager without it being on the ribbon.

 

[SkipVought]

    Application.Dialogs(xlDialogDefineName).Show

and check out other dialogs as well in VBA HELP.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I think xlDialogNameManager is what you want.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[markronz]

Perfect! Thank you very much!!!

 

[SkipVought]

[tt]
Name Value Description
xlDialogApplyNames 133 Apply Names dialog box
xlDialogCreateNames 62 Create Names dialog box
xlDialogDefineName 61 Define Name dialog box
xlDialogDeleteName 110 Delete Name dialog box
xlDialogNameManager 977 NameManager dialog box
xlDialogNewName 978 NewName dialog box
xlDialogPasteNames 58 Paste Names dialog box
xlDialogWorkbookName 386 Workbook Name dialog box
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

I had to star this one as well. I haven't needed this yet, but it's something nifty to think about.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57