Modificación de CodeName con código VB excel

Question

Modificación de CodeName con código VB excel

Arturo Solano 26

CodeName modification with excel VB code
Good evening. I have the following code:

'I create a sheet with the following code
Worksheets.Add(After:=ActiveSheet).Name = "Nombre de la pestaña de la hoja"

'Change the CodeName
ThisWorkbook.VBProject.VBComponents.Item(Sheets(ActiveSheet.Index).CodeName).Properties("_CodeName").Value = "Nombre del CodeName"

Perform the action but immediately after modifying the Internal Name of the sheet, send me the following:

"You cannot enter the interruption time at this time" I know that I can continue or finish the compilation, but when I continue, it shows me the following.

"Error '9' has occurred at runtime:" "Subscript out of range"

Do you know why this happens and how to correct it?

Thanks in advance for your attention and support

Accepted answer

0 additional answers

Your answer

Answer 1

Philippe Levesque 5,836

Hi

Please edit to add the revelant text into english. The main language on Q&A is just English as we speak.

For forum in other language(s) you can still use Technet/MSDN forums for that matter.

Thanks you for your understanding

Arturo Solano 26 Reputation points

2019-11-02T00:46:02.947+00:00

I don't know if I translated it well, I need a little practice, but if you can support me I would appreciate it very much
jun sun 16 Reputation points

2019-11-05T08:15:50.54+00:00
Arturo,
Are you trying to rename the newly created worksheet?

Worksheets.Add(After:=ActiveSheet).Name = "Nombre de la pestaña de la hoja" Sheets(ActiveSheet.Index).Name = "Nombre del CodeName"
Arturo Solano 26 Reputation points

2019-11-06T00:56:40.18+00:00

Yes, I need to create the table and rename it, in case I am going to add more formulas inside the table, if the user changes the NAME property, it does not affect because the code points to the CodeName.
jun sun 16 Reputation points

2019-11-07T07:54:51.513+00:00

@Arturo Solano check this link, hope helps... https://exceloffthegrid.com/vba-excel-tables-code/

'to create new table
ActiveSheet.ListObjects.Add(SourceType:=xlSrcRange, Source:=Selection.CurrentRegion, xlListObjectHasHeaders:=xlYes).Name = "myTable"

'to resize existing table
ActiveSheet.ListObjects("myTable").Resize Range("$A$1:$J$100")

'to add extra line to existing table
Dim ws As Worksheet
Set ws = ActiveSheet
Dim mytbl As ListObject
Set mytbl = ws.ListObjects("myTable")

mytbl.ListRows.Add 1

'to rename table
ActiveSheet.ListObjects("myTable").Name = "theTable"
Arturo Solano 26 Reputation points

2019-11-07T17:26:59.07+00:00

Thanks for your help is what I needed so that the system pause was not made.

Share via

Modificación de CodeName con código VB excel

0 additional answers

Your answer