Programmatically Resize Name Manager Dialog Box

[Greg_M]

Hi All,

I use Excel's built-in Name Manager a LOT!

Each time Excel is started, the dimensions, column widths etc. of the Name Manager dialog box are reset to default values which (I suppose!) are stored somewhere. The problem is that these default values are unsuitable for me, and I would like to be able to resize the dialog box programmatically.

I've performed a quick check of registry value changes but have been unable to identify where these settings might be stored.

So, does anyone know how to programmatically resize the Name Manager dialog box using e.g. API calls, registry values, other methods???

Regards,

Greg M

Please note that this item was originally posted on Excel Forum at: Programmatically Resize Name Manager Dialog Box

 

[Tragic Shadow]

EDIT: Note that this code is language sensitive as it relies on the language of the button captions in the name manager dialog.

Hi Jaafar,
Thank you.
It works beautifully.
Your code is super awesome, as usual!

You IAccessible code is very clean and neat.
Passing and retrieving the Name string via nIDEvent is cool.
I really appreciate that you showed me how to loop through the listview via IAccessible interface and also how to directly push the 2nd button without having to search through AccessibleChildren of Name Manager. I love that part the most.

I was very confused about IAccessible stuff because the example I was following was quite complicated but your code just cleared it up like removing cataracts over my eyes...
I also have trouble executing the IAccessible methods and ended up crashing every time I tried to do that.
I know that they are there but just can't seem to be able to use them and you opened up myriads of possibilities for me. Thank you.

So you used the TimerProc to do everything.
As the TimerProc is going to be executed by another thread/process, it would get over the modal-ness of Name Manager.
In my code, I also tried the Application.OnTime to do just that but it still crashed. May be because I hooked or may be my IAccessible code is buggy.

But if we were on a very fast computer or a very slow computer, there is a chance that the timer might miss the creation of Name Manager window?
I'm not nitpicking here. Just curious.
Is there anyway of achieving the same thing without the Timer? Or is it going to be too complicated for me?

And the reason my code failed was because of the hooking part and/or the Name Manager being destroyed upon clicking Edit button?

If you don't mind, could you please go through my code. I have commented out the DoDefaultAction part so that it won't crash on your computer.
But my code is not 64bit ready as I am using Excel 2010, 32bit. So, if it is too troublesome, please disregard this request.
I just want to learn how to improve it and learn where I went wrong.

Lastly, why did you say your code is language sensitive when you just pressed exactly the second button?
Different language versions have different location of the buttons like Edit... is 1st, New... is second, etc.?

I apologize beforehand if you feel offended by my questions. I have utmost respect for you. I asked questions because I genuinely wanted to improve myself from my mistakes.
I am apologizing first, because, in my Asian culture, it is considered impolite to ask back to people who tried to help you.
So, please kindly understand that I meant no disrepect. I'd never do that.
Anyway, thank you so much for your kindness.

 

[Jaafar Tribak]

Hi Tragic Shadow,

If you don't mind, could you please go through my code. I have commented out the DoDefaultAction part so that it won't crash on your computer.

I am quite busy at the moment. I will take a look at the code when I am less busy.

As the TimerProc is going to be executed by another thread/process, it would get over the modal-ness of Name Manager.
In my code, I also tried the Application.OnTime to do just that but it still crashed. May be because I hooked or may be my IAccessible code is buggy.

Yes. unlike Application.Ontime ,the timer callback procedure runs asynchronously.

Lastly, why did you say your code is language sensitive when you just pressed exactly the second button?
Different language versions have different location of the buttons like Edit... is 1st, New... is second, etc.?

My mistake. As you say, the location of the buttons doesn't varie according to the language version. Just their captions do. So. yes, you are correct.

All the best.

 

[Tragic Shadow]

Thank you, Sir.
I am sorry that I kept bothering you. You must be busy.
Please forget about my selfish request to go through my code. I must do it myself.

 

[Jaafar Tribak]

Hi Trgaic Shadow,

I ported your code to x64bit but It kept crashing excel when reaching the pressButton routine .It seems that the FindAccessibleChildInWindow is the culprit. To be honest, I haven't spent too much time debugging the code because the way it is written is very tangled up plus coding active accessibility can be very unforgiving when an error exception occurs and crashes the entire application.

Regards.

 

[Tragic Shadow]

Hi Trgaic Shadow,

I ported your code to x64bit but It kept crashing excel when reaching the pressButton routine .It seems that the FindAccessibleChildInWindow is the culprit. To be honest, I haven't spent too much time debugging the code because the way it is written is very tangled up plus coding active accessibility can be very unforgiving when an error exception occurs and crashes the entire application.

Regards.

OMG. I thought you didn't download it. I even removed sharing from Box because I didn't want to bother you with my rubbish code, for which, I am much ashamed.
Please let it be. I gave it up and decided to use your code, which is way far superior. I will review mine later when I'm more capable... :P ...who knows when...
Please don't waste any more of your time on that, Sir. Nonetheless, I really appreciate your kind efforts and attention.

I have now moved on to another project and already managed to get stuck. :D
I am doing the Evaluate Formula enlargment project now.
Right now, I am facing issues with Excel automatically repainting(expected)/resizing(unexpected) my SetWindowPos-enlarged Evaluate Formula Dialog, each time a button on it was pressed.
Can't seem to be able to block resizing. Already subclassed it and check every other message related to WM_Size. Only handling WM_NCCALCSIZE seems promising. Working on it now.
May be I better start my own thread rather than hijacking this thread.
Thank you so much, Mr. Tribak. You're the best...

 

[corentint]

Thanks for the feedback and glad this was useful to you... I too learnt a few things in the process.

Hello,

Super, I was writing a request for exactly that - but then got wiser and made a pointed research and low and behold found your solution.

I have not yet implemented it, but everything looks good and I will test it on my side.

Thank you also for your efforts.

 

[Jaafar Tribak]

I have not yet implemented it, but everything looks good and I will test it on my side.

Thank you also for your efforts.

@corentint

You should install this (NameManagerAddin.xlam) addin from the link in post#15

I have done a small update , (albeit important) to the addin code in order to speed it up a bit. This is achieved by simply avoiding unnecessary repetitive savings of the user changes and limiting it only to when excel closes.

Thanks.

 

[Greg_M]

Hi Jaafar,

Just wanted to let you know that it's now been three years since you answered my request, and I still sing your praises every time I use the Name Manager dialog box!

Please keep up your great work.

Best regards,

Greg M

 

[Jaafar Tribak]

Hi Jaafar,

Just wanted to let you know that it's now been three years since you answered my request, and I still sing your praises every time I use the Name Manager dialog box!

Please keep up your great work.

Best regards,

Greg M

I am glad to hear that, Greg.
Thanks for the positive feedback