CODEWITHSUNDEEP
vbaexcel
Tranman324
Tranman324

Reputation: 21

Renaming a sheet code name through VBA code (Not through properties)

I have a protected workbook A for the user which does not allow the user to copy over a sheet from another workbook B. In workbook A I consider worksheets 1-19 (only have 13 sheets as of now, I allowed 19 for future expansion of the workbook) as "system sheets" and cannot be deleted or modified. Sheets 20-30 are "non system sheets" where the user can delete and modify as needed.

I'm looking to have an import function where the user can import workbook B that contains 1 sheet into workbook A. workbook A would check to see how many "non system sheets" that currently exists and insert the new sheet at the end with a sheet number that is 20+. For example:

Total sheets in workbook A = 18

Total "system sheets" in A = 13 (sheet 1-sheet 13)

Total "non system sheets" in A = 5 (sheet 20-sheet 24)

If I were to import a new sheet workbook A would assign it as sheet 25

I want VBA to change the code name(sheet number) and not the name of the sheet.

I've tried searching for hows, can someone please point me in the right direction on how to do this? thanks!

Upvotes: 2

Views: 8866

Answers (2)

Rich Harding
Rich Harding

Reputation: 635

In your specific example, you could modify the CodeName in Workbook B before the user imports it to Workbook A - because if you copy a sheet to another workbook, it retains its CodeName. Otherwise you have to get into modifying the VBProject itself, e.g.:

<Workbook>.VBProject.VBComponents(<Workbook>.Sheets(<SheetName>).CodeName).Properties("_Codename").value = <CodeName>

I came up with (and tested) the copying solution for a slightly different case, where the workbook into which I'm copying the sheet has its VBAProject password-protected, which means that the above code throws:

50289 : Can't perform operation since the project is protected.

Upvotes: 0

Miguel_Ryu
Miguel_Ryu

Reputation: 1418

In the Excel object model a Worksheet has 2 different name properties:

Worksheet.Name Worksheet.CodeName

the Name property is read/write and contains the name that appears on the sheet tab. It is user and VBA changeable

the CodeName property is read-only

You can reference a particular sheet as Worksheets("Data").Range("A1") where Data is the .Name property or as Sheet1.Range("A1") where Sheet1 is the codename of the worksheet.

edit: You can change the CodeName Property by accessing the VBA Project Model Components Extensibility.

ThisWorkbook.VBProject.VBComponents(Sheets("Sheetname").CodeName).Name = "Sheet" & Workbook.Sheets.Count

Just be sure to have the programmatic access to visual basic project.

File -> Options -> Trust Center -> Trust Center Setttings -> Macro Settings -> Trust Access to the VBA Project object model.

Upvotes: 3

Related Questions