PDA

View Full Version : [SOLVED] Automate Menu Options Insert - Name - Define



Anne Troy
03-28-2005, 02:22 PM
The minute I use this macro (which I made myself, I might add!), it brings up the dialog, and I put a name in and say OK...


Sub MakeName()
Application.Dialogs(xlDialogDefineName).Show
End Sub

Then, my arrow keys don't work no more!!
What's up with that?

Anybody else?

Jacob Hilderbrand
03-28-2005, 02:36 PM
What macro?

Anne Troy
03-28-2005, 02:44 PM
What? You can't see the macro I put up there? (hee hee, sorry!!)

Jacob Hilderbrand
03-28-2005, 02:47 PM
I tried running the macro from a button and Tools | Macro | Macros... and added a name and pressed Ok. After that my arrow keys still work fine and I can move around to different cells.

Excel 2003.

Anne Troy
03-28-2005, 02:50 PM
MAYBE it's cause I have assigned it to a textbox from the drawing toolbar? Mind testing? Hmm...I'll test in 2003 while I'm at it.

Doh! I am in 2003

johnske
03-28-2005, 02:58 PM
The minute I use this macro (which I made myself, I might add!), it brings up the dialog, and I put a name in and say OK...

Then, my arrow keys don't work no more!!
What's up with that?

Anybody else?

Nope, worx fine on mine (2000)...

Jacob Hilderbrand
03-28-2005, 03:26 PM
Wow, that is a very strange error. After running the macro from a Drawing Toolbar Text Box the arrow keys break.

Maybe the Text Box keeps the focus even though I can select another cell with the mouse and still cannot use the arrow keys.

Zack Barresse
03-28-2005, 03:42 PM
Why can't you just use the keyboard shortcut: Ctrl + F3 ?? (That's what I use.)

Jacob Hilderbrand
03-28-2005, 04:45 PM
That's two buttons. She wants to be more efficient and reduce that to one click. :)

geekgirlau
03-29-2005, 06:53 PM
I think this one goes into the Excel Twilight Zone file, however I found a solution!



Sub MakeName()
Application.Dialogs(xlDialogDefineName).Show
Application.TransitionNavigKeys = Not Application.TransitionNavigKeys
Application.TransitionNavigKeys = Not Application.TransitionNavigKeys
End Sub


This sets the Transition Navigation Keys option (Tools, Options, Transition) to the opposite of its current setting, then back again. No idea why it should work but it does :dunno

johnske
03-29-2005, 07:07 PM
I think this one goes into the Excel Twilight Zone file, however I found a solution!



Sub MakeName()
Application.Dialogs(xlDialogDefineName).Show
Application.TransitionNavigKeys = Not Application.TransitionNavigKeys
Application.TransitionNavigKeys = Not Application.TransitionNavigKeys
End Sub


This sets the Transition Navigation Keys option (Tools, Options, Transition) to the opposite of its current setting, then back again. No idea why it should work but it does :dunno


Hi geekgirl,
Good one! :thumb But I found the second iteration not necessary (2000)... Let's see if it's the same for the others...
John

Anne Troy
03-29-2005, 07:36 PM
VERY interesting!!

I'm working on a project where I may be inserting bunches of named ranges, so I just GOTTA have the button... :)

geekgirlau
03-30-2005, 12:51 AM
Hi John,

The purpose of the second iteration is to reset back to the original transition keys setting.