Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Defined Name Box Shortcut

I got this from Chip Pearson=B4s site, but don=B4t works for me (Win XP,
Excel 2003 portuguese).

Public Declare Function SetFocus Lib "user32" (ByVal hwnd As Long) As
Long
Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Public Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA"
_
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, ByVal lpsz2 As String) As Long

Sub SetFocusNameBox()
Dim Res As Long
Res =3D SetFocus( _
FindWindowEx( _
FindWindowEx( _
FindWindow("XLMAIN", Application.Caption) _
, 0, "EXCEL;", vbNullString) _
, 0, "combobox", vbNullString))
End Sub

Then I tried this and worked:

Sub DefinedNames()
Application.Dialogs(xlDialogDefineName).Show , "=3D" & ActiveSheet.Name &
"!" & ActiveCell.Address
End Sub

What am I missing?

Thanks
Hamilton R. Romano


Post your answer or comment

comments powered by Disqus
Is there a keyboard shortcut to get you into the Name Box? (the left-most field in the Formula Bar).

If not, is there a way to create one? I use that field all the time, and I hate picking up my mouse to write in it.

Hi, I have been looking all over Google to see if there is shortcut Key to move the curser into the NAME BOX just above A column. I first went to MS, and searched here, but sometimes either I don’t structure my searches well enough or I don’t read well enough, but it seems to me as much as the NAME BOX is used to name a range you would think there is a shortcut for that… but I can find one… am I missing something?

Thanks
Ken

Hi there,
I am trying to give certain cells in EVERY sheet the same name. When I originally set this up, it worked out fine. Now that I am trying to add new names to each sheet, it seems to only want to save to the active sheet. I tried a 'select all' sheets and then go to 'insert - name - define', but it only saves to one sheet.. I am wondering if anyone knows how to save the same name to say, cell A1, in every sheet. I have attached a screen shot of my define names box, and you'll see the name jw21.5 isn't saved quite the same. Thanks for any help!

I've inherited a workbook with some 100 or so defiined names, each of them
tailored to refer to vertical single-column ranges in different sheets in the
workbook. At some point I'll just recreate the whole thing to eliminate many
of the names, but for now I need to change the range references to all of
them so the final row is the same (row 1000). Is there a quicker way to do
this than manually changing the reference to each in the "Define Names" box?

Help!

I have a excel file that has 10 tabs. I need to define named range for each tab but i need it to be named the same.

For example my tabs are named 2/1/2007, 3/1/2007, 4/1/2007 and so on.
I need the name to show up in the Define name box with the name and the tab that it is related to.

I have an old file that someone else created that has this done correctly. For some reason I can not figure it out.

This is hard to explain so if you need more details let me know.

Any help would be appreciated.

I have defined a dynamic range using the offset and counta functions in the
define name box. The name works fine in my formulas, but does not appear in
the drop-down name box on the formula bar. Is there a way to get this name
to appear there? Thanks.

Dave

Background Info:
I have to repeatedly create specific names in an Excel worksheet. These worksheets are laid out with specific formatting, so I cannot change the layout or add cells, etc. All I need to do is add cell names, by mouse clicking on a cell (1x1 in size) then mouse clicking in the "name box" then entering the name I want. I then press "Enter" on the keyboard and then "Tab" or "Enter" to go to the next cell I want.

The Problem:
Once I have a cell selected, I cannot use the keyboard to get my cursor into the "name box". I have to use the mouse to click in the "name box". It slows me down. I don't want to use the mouse. Is there a keyboard shortcut that will move the cursor into the name box so I can simply type the name of the cell (essentially naming the cell)? I then press "Enter" or "Tab" to the next cell, hit the shortcut again, then enter the next cell name to define it, repeat that process a gazzillion times. I know there are shortcuts to creating named cells like CTRL-SHIFT-F3, but these names are not what I want. I have very specific names for each cell, not the auto created names that Excel will create for you.

Please Help!!!!! Thank you soooooo much in advace for replying.

Maxx57

I am stuck. I am trying to use the "SUMIF" function in conjunjunction with "NAME" references and a combo box. For example, I have groups of cells aligned in columns that are named. I have a combo box that I created using 'cell validation' containing the months. An example of the formula I am trying to get to work is as follows:

=SUMIF(COSTCENTER,A11,MAR)

COSTCENTER is a group of cells that have a defined name as well as MAR.
The combo box displays 'TEXT' and I want it to be referenceing the NAME instead.

Can anyone help? If you need to see the excel spreadsheet to understand, I can send.

I'm a fan of INSERT>NAME>DEFINE as a way to interact with range names.

My users however prefer the NAME BOX in the upper left hand corner... Am wondering to make it wider... Thanks!

I know that pressing F2 will put you in the formula bar. However, is there a
keyboard shortcut for putting you in the name box (I have a large number of
cells to name). Thanks.

Does anyone know if there is a shortcut keystroke to bring your insertion
point into the Name Box?

Thanks!
--
L Lightner

I have created a utility that contains named constants used for data validation on a particular sheet. As an enhancement requirement from the users, they want to be able to copy another workbookā€™s sheet(s) into the utility. While using some sample files provided from the users, I have encountered name constants are being copied into the utility.

There is a routine that I can write to eliminate unneeded name constants, while keeping the required ones?

How can I get a listing of the named constants listed in the Define Name dialog box and only those names (not the Refers Toā€¯) along with the associated index

Any assistance would be greatly appreciated

Does anyone know if there is a shortcut keystroke to bring your insertion
point into the Name Box?

Thanks!
--
L Lightner

I want to set an if statement on my macro, if a cell meets a certain condition, do not run macro, else run macro

unimportant background info
I have a macro that i'm writing where the user selects a work package executes the macro then that macro selects a range of cells and copies them, then pastes the values then selects the cell in column A 6 rows down, the macro then executes again doing the same thing as long as the user holds down the shortcut key. I'm also writing an alternate macro that does the exact same thing except instead of replacing the cells with values it replaces the cells with the formulas. I don't want the macro to be automatic at this time, i want it to have to be ran on each work package, that way the user can easily run it on the whole list of work packages if they hold the shortcut key down or it can easily be executed on just one work package by hitting the shortcut key once.

the idea is that the user starts at the top, holds down the shortcut key for the macro and the macro keeps executing until it has executed on the last work package. After executing in the last work package the cell it selects 6 rows down will have a defined name of "STOP"

when it reaches that last cell with the name of STOP rather than executing it should cancel itself/not execute and instead return a message that says "End of worksheet reached, Macro can not be executed from this cell". It doesn't need to be a special form control text box, just if there is an easy way to make it show that message in the standard grey excel macro debug text box that usually pops up on errors would be great so that the user knows what's going on or that they reached the end.

/unimportant background info

this is my code:

Sub PasteValue()
'
' PasteValue Macro
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
    ActiveCell.Offset(0, 3).Range("A1:DL4").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.Offset(0, -3).Range("A1").Select
    ActiveCell.Offset(6, 0).Range("A1").Select
End Sub
this is what i want it to do
So I want it to be something along the lines of:

If Active.Selection (or currently selected cell/don't know the syntax) = "STOP" (defined name of the cell is stop, not the cell value/contents)

Do not run macro and return message saying you have reached end of spreadsheet and macro cannot be executed from this cell.

ElseIf Active.Selection <> "STOP" Then

Execute the macro/above lines of code as it's written.

So if defined name = stop, don't run macro, else if, run macro

and if i wanted to how would i make such code check multiple condtions. Like an if statement with (and) or (or) in it

Attached is a sample spreedsheet i'm working with. I only have two work packages added at the moment but a user could have only a few or a couple hundred in the spreadsheet which is why i want the user to be able to hold down the macro shortcut key and have it keep executing till it reaches the end and then stop without the user having to pay particular attention or worry about breaking the spreadsheet by running the macro after it has reached the end.

Thanks

and is there a good online resource for how to set conditions or if statements for macros. I'm quite good with if statements but i have no idea how to put if statements into macro form. So i can record most of my macros but don't know how to take it to the next step and make them conditional or error trap them.

I'm trying to get my head around Excel Programing using VBA. I'm
reading various books, some of which provide examples on CD.
On the subject of named formulas, when I look in the examples using the
define name dialog box, the actual formula completely fills the 'Refers
to' box.

Is there a way of expanding this box so I can see the contents in
total.

The same thing happens in the Data Validation dialog box when I select
List, I cant view the complete entry in the source box.

It's probably something very elementary and your help would be
gratefully received.

Thanks

The edit box used to make or edit ranges for Defined Names is VERY difficult to use for long char strings. The arrow keys do not move the cursor within the edit box & the end key does not work.

How to edit range names that are 100-200 characters long?

Are there settings to alter behavior of arrow keys withing these "range definition boxes"?

**** Penny

Hi all,

I am trying to create a graph where I can toggle on and off some definind names. Ideally, I would click a command box that would open a userform with list of the defined names I want turn on and off in the graph. I discovered a post laying out a method for toggle on/off series in a graph. However this method just changed the line graph color to transparent and this does not work very well with stacked bar graphs.

Any suggestions? Thanks in advance

I need some help on the chart I'm using.

I'm using this formula as a Define named range:

=OFFSET(Table!$E:$E,19,0,COUNT(Table!$E:$E)-1,2)

I implented the proper name range in the pie chart. But if you look at the sample attachment below in worksheet tab "Table" in cell E21 that has the name "Hose" it shows up multiples in the pie chart legend box. Why is That? Should I use a different formula?

Hi,

I have a list of Sales Managers. Each month sales managers may be added to or be removed (Therefore the list shrinks and grows).

When using my report, I have a drop down box which only allows valid Sales Managers from the Sales Managers list to be entered. (It's the inbuilt Data Validation rule).

To make all this work I have a list of Sales Managers in one work sheet. I then defined the cells containing the names as "SalesManagers".

This works fine for a month. However once a new manager joins I need to update my defined name so that it picks up the extra cell(s). I have some code already, so it would be very nice to simply add a few lines to update this change. I have added the code as follows:


	VB:
	
ActiveWorkbook.Names.Add Name:="SiteManagers", RefersToR1C1:"=SiteList!R1C1:R"& LastDataRow - 2 & "C1" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However I get an error message: "Expected named parameter". It highlights "RefersToR1C1", so this is where the problem lies.

Please could someone help me modify this line of code??!!!

If you want I can attach an example?

Thanks!!!

Greetings,

This is my first time posting and I am quite stumped with this one. I am writing a userform that requires a list box to reference a range with a defined name. I want this range to change but the name to stay the same (as the required range for this listbox is always changing as the user enters in new data). I have set up a counter to count the number of rows I need in the range. The problem I am having is I don't know how to incorporate that number so that I can redefine the named range. I have ran a recorded macro that does the following:

ActiveWorkbook.Names.Add Name:="new_name", RefersToR1C1:= _
"=Sheet1!R1C1:R13C2"

The first thing I suppose is that I don't understand how to use R1C1. I know it refers to row and column but if I want that "R13" to change to "R33" or something like, I have to reference my counter variable (which in this example would have a value of 33). I've tried changing it to the follow:

ActiveWorkbook.Names.Add Name:="new_name", RefersToR1C1:= _
"=Sheet1!R1C1:Rnew_name_counterC2"

...with very little success as I'm sure you would guess. However, this is how I want it to read so that when the new_name_counter variable changes, the range changes but the name for that range stays the same.

Does anyone have any suggestions?

Thanks a bunch,
kippy

Hi,

I am reviewing a spreadsheet that has a few of dozen defined names in it that is slowing my precedent checking. I have tried using the delete button in Insert/Name/Define box, but all the formulas give error values as they can no longer find the defined names. Is there any way I can delete the defined names so the the formulas in the spreadsheet will revert to the cell references?

I have searched threads and see that VBA code can be used to delete names, unfortunately the VBA on this spreadsheet is password protected and I don't know the password. This means I will need to remove the defined names in excel.

Thanks

I'm trying to create a formula that will add up all the rows above it. I'm using

=SUM(INDIRECT("R1C"&COLUMN()&":R"&(ROW()-1)&"C"&COLUMN(),FALSE))

which works but when I try to define it as a name (by pressing CTRL+F3) and calling it "total" the function =TOTAL doesn't work. I don't really need to call it "total" I could just leave the formula, but I'm trying to keep my spreadsheet clean.

Any help?

Thanks,
Matt

Hi. I am hoping someone will be able to assist me with the syntax for a formula I'm working on? The array formula itself is relatively simple (below).

{=SUM(IF(GLAccountNumber4500Class=$B12,GLAccountBalance4500Class,""))}

The defined names in the formula reference the following:

GLAccountNumber4500Class - General ledger account numbers on a separate tab named 4500
GLAccountBalance4500Class - General ledger account balances on the same separate tab named 4500

As you can guess, the value in cell $B12 will be the general ledger account # to be searched and matched.

The source data is loaded on a separate tab from where the formula resides (there are multiple separate tabs for with similar data sets which need to be sourced/referenced). The challenge is to have the formula change the tab/defined name reference based upon another field (i.e. E12). In other words, if the user selects 4501 from a drop down box in field E12, I need the formula to change the '4500' reference in the formula's defined name dynamically based upon the fund number value selected in E4. For example, if the user selects '4501' from the drop down list in E12, the formula should now read:

{=SUM(IF(GLAccountNumber4501Class=$B12,GLAccountBalance4501Class,""))}

I could change this reference via a macro, however, I was hoping to avoid a macro and do this within the formula. There are too many variations to use an If formula and hard code in the fund numbers into the formula.

Please let me know if you need any further clarification. Any help is greatly appreciated.

I just learning to set up and use dynamic named ranges, unfortunately when I
set them up using the Insert>Name>Define method (giving the range a name in
the Names in Workbook box type), I noticed that the range name I established
doesn't appear in the standard name box immediately to the left of the
formula bar. This makes it difficult to confirm the range is correct. Am I
right about this fact? Is there any way around this so that I can visually
look at my range like I can when I set up a range name the old fashioned way?
Thanks everyone!
DakotaSteve


No luck finding an answer? You could always try Google.