Setting (Name) property programmatically

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I need to be able to set the (Name) property of a worksheet
programmatically (using VBA)- NOT the Name property.

I have tried via the VBE object but have had no luck so far.

Any ideas?
 
Anne

My problem is that the Worksheet object has two very similar
properties.

The 'Name' property which is displayed on the sheet tab and is user
editable

The '(Name)' property which is only visible fom the VBE and is the one
that I can change manually but not (yet) programmatically.

So I am referring to Worksheet properties as opposed to Named Ranges.

Chris
 
ThisWorkbook.VBProject.VBComponents("Sheet1").Properties("_CodeName").Value
= "NewCodeName"
 
Chris, you might have a very valid reason to do so ( a vaild reason is
when duplicating sheets) but usually my comments on this practice
are...

don't change the CodeName of sheets/workbooks if you can avoid it. It
is actually an *object reference* to the sheet/workbook, and this
should be set only once, and from then on only be used from VBA code.
example:

rename the codename from "sheet1" of your first sheet *called* "Mysheet
to "StartSheet"

Now you can use VBA code:

StartSheet.Activate ' always works, fast and easy

from anywhere in your VBA project. instead of

Thisworkbook.Sheets("Mysheet").Activate ' will not work if enduser
changed sheetname from e.g. "Mysheet" into "JohnsSheet"

This trick speeds up your code writing and running.

I see a lot of people thinking to use it as an extra variable, but that
is *not* it's use. You can trigger macro warnings (only in some
circumstances) because you are actually changing de VBA code object
behind the sheet (this is more or less viral behavior and should only
be done in special circumstances) If another (invisible)
name/identifier is required for a sheet or workbook you can create an
invisible named range,named formula,value, or use the custom
documentproperty linked e.g. to a cell.

DM Unseen
 
Back
Top