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?
Unfortunately no, it's not possible to change that default in Excel.
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.
- While creating Named Range, you can pick a worksheet from the list, then Excel will restrict it's scope to the sheet only.
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
1. This single line can do it also.
2. In this code you may adjust cell references and NAME of the Range as you need.
Change Scope can be used as
Workbook Open event (calling Macro) also.