Named Cells or Ranges

[NIWANut]

Hi,

Using Names for cells, ranges or variables is really useful especially using them in macros but does anyone know if they can be restricted to use on just one sheet, or perhaps some sheets but not others. Generally they are available on all sheets in the workbook.

Thanks

George

George

 

[macropod]

If you're writing macros to work across multiple sheets, then it's up to you of course to decide whether to use a named range or some other range description in your code.

If you're trying to prevent users from accessing range names from other than a specified set of worksheets, you could use WorksheetChange code to test for the named ranges in those worksheets. This is easily defeated, however, if the user can add new worksheets, since the WorksheetChange code needs to be in the target sheets. Note too that Excel automatically substitutes a named range for a selection that spans the same range, which would cause problems for users who try to access those ranges via cell references.

Cheers

 

[PHV]

Perhaps you may take a look at the Visible property of the object Name.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244