CODEWITHSUNDEEP
vbaexcel
kissinger chen
kissinger chen

Reputation: 67

what's the best way to store named range information?

What's the best practice to store the named range information?

The background is: We retrieve data from web service according to some parameters, such as data source, start date and end date. We retrieve the data to a named range, such as A1:D10. The User can refresh the named range later, or share the workbook with his co-workers.

Currently we store the name of named range, and other parameters, such as data source, start date and end date to the sheet custom property. Later when the workbook is opened again, we read the named range's name, data source, start date and end date from custom property. So the end user can refresh the data of the named range when needed.

Definitely we cannot store the named range address(R1C1) since user may insert/delete rows/columns, so the named range address will be changed. then we have to store the name of named range to the custom property.

But the problem is: the name of named range can also be changed. User can click Formulas --> Name manager to change the name. Currently we have to educate our end user do not do it. But absolutely this is not the best solution.

So could you give me some suggestion? What's the best practice to store the named range information and its associated information? Thanks.

Upvotes: 0

Views: 225

Answers (2)

kissinger chen
kissinger chen

Reputation: 67

In Google group hidden range is recommended to use. From UX perspective it is seems better than locking the sheet.

see https://groups.google.com/forum/#!topic/exceldna/U8qeOpHiuI8.

Any comment? Thanks.

Upvotes: 1

OldUgly
OldUgly

Reputation: 2119

Based on what you have provided ...

I would look into protecting the sheet(s) that contain the named range(s). Protecting the sheet makes changing the name of a named range unavailable to the user. You can make it so many things are still available: all cells unlocked, ability to insert rows/columns, etc.

For example, consider a workbook with Sheet1 having a named range for A1:A3 call Numbs. After running this macro ...

Sub test()
Dim mySht As Worksheet
    Set mySht = Worksheets("Sheet1")
    With mySht
        .Unprotect
        .Cells.Locked = False
        .Protect UserInterfaceOnly:=True, AllowInsertingRows:=True, _
            AllowDeletingRows:=True, AllowInsertingColumns:=True, _
            AllowDeletingColumns:=True
    End With
End Sub

The Name Manager dialog box looks like the following (note the New..., Edit..., and Delete buttons are grayed out)

enter image description here

So the named range cannot be changed. However, all cells can still be edited, and rows/columns inserted or deleted.

On the negative side, new named ranges cannot be added to that sheet either.

Upvotes: 1

Related Questions