2

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?

0

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.

enter image description here

This saves navigating to the Name Manager and drilling down within that to get to this dialog.

enter image description here

| improve this answer | |
0

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.

enter image description here

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.

| improve this answer | |
  • Thanks for the answer, Rajesh. Since both answers were good, I had to choose the first one posted. – sergio trajano Jul 30 at 13:13
  • Hey @sergiotrajano,, glad to help you now you may up vote also,, for details Check this ,, keep asking ☺ – Rajesh S Jul 30 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 :) – sergio trajano Jul 30 at 19:25
  • @sergiotrajano,, it's okay no issue ☺ – Rajesh S Jul 31 at 5:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.