Is it possible configure Excel (maybe a check box Option available), so that, when I create a named range using the "shortcut" in the formula bar, the scope is set by default to the active worksheet, instead of to the whole workbook?
2 Answers
Unfortunately no, it's not possible to change that default in Excel.
However, Define Name
exists as a command in its own right. This means that one thing you can do is to add a custom ribbon button to go directly to the Define Name
dialog.
This saves navigating to the Name Manager and drilling down within that to get to this dialog.
Changing default setting of the SCOPE OF NAMED RANGE to specific worksheet is looking difficult.
A Name can be either global to a workbook or local to a worksheet. The standard method is create a Range name as a global names, but yes you can set the SCOPE OF NAMED RANGE to any specific worksheet.
Method 1:
- While creating Named Range, you can pick a worksheet from the list, then Excel will restrict it's scope to the sheet only.
Method 2:
You may use this VBA code to change the SCOPE of the NAMED RANGE.
Sub ChangeScope() Application.Calculation = xlManual Names("MyName").Delete Range("Sheet1!$A$1:$A$10").Name = "Sheet1!MyName" Application.Calculation = xlAutomatic End Sub
N.B.
1. This single line can do it also.
Sheet1.Names.Add("Myname", Sheet1.Range("A1:A10"))
2. In this code you may adjust cell references and NAME of the Range as you need.
3. Code Change Scope
can be used as Workbook Open event
(calling Macro) also.
-
Thanks for the answer, Rajesh. Since both answers were good, I had to choose the first one posted. Jul 30, 2020 at 13:13
-
Hey @sergiotrajano,, glad to help you now you may up vote also,, for details Check this ,, keep asking ☺ Jul 30, 2020 at 16:07
-
I just upvoted, but since my reputation is less than 15, my upvote do not shows up to everyone, but is counted :) Jul 30, 2020 at 19:25
-