Solved: Macro auto change name in VBE

[fwawergurl16]

Hi. I would like to know if there's such a thing as a macro to change the name in the VBE.

The thing is that when I change the name in the tab of the worksheet, the name in the VBE doesn't change as well. When I run the codes, it could not detect the sheet as its name is different from the sheet & in the VBE (Properties - Alphabetic - Name). I've to change it manaually, which can be qte troublesome for those who know nothing about VBEs.

Can anyone suggest some way or VBA codes to get such a macro done? Thanks!!

[Bob Phillips]

[vba]

ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule.Name = "somevalue"
[/vba]

[lucas]

Johnske has a great article here that will help you understand what is going on in referencing your worksheets.

[fwawergurl16]

Thank u, xld & lucas! Your help is much appreciated

[fwawergurl16]

[vba]

ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule.Name = "somevalue"
[/vba]

xld,

I ran into this:

Run-time error '1004' Programmatic access to Visual Basic Project is not trusted. How can I resolve this error?

[geekgirlau]

Under Tools | Macro | Security | Trusted Publishers you need to tick "Trust access to Visual Basic Project".

Having said that, I don't think you actually need it. Let's think about what you are trying to do. If your sheet tab is called "Tax", you can reference that sheet in your code by using

[vba]Sheets("Tax").Range("A1")[/vba]

However the name shown on the tab can be changed at any time. If your code uses the reference above and the name is changed on the tab, your code will generate an error.

In the VBE window, let's now assume that you have assigned the name "shTax" to your sheet. To reference that sheet in your code, you would use

[vba]shTax.Range("A1")[/vba]

Regardless of the name that you see on the sheet tab, your code will continue to work because the name in the VBE window has not changed.

Whilst XLD's code will change the name of the sheet in the VBE window for you, every place in your code where you reference "shTax" is now wrong, because that sheet name no longer exists. In effect, what you are doing is removing any benefit of giving the sheet a name in the VBE window in the first place.

Try the attached sample. Run both macros, and note the message that displays. Then change the name of the sheet tab, and run both macros again.

[fwawergurl16]

Thanks Geekgirlau. I now see the difference & its effects. Thanks for sharing the knowledge