Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing VB Component Names to match Worksheet names using VBE
Hi,
In VBE the worksheets can be identified using a name that never changes (default Sheet1 etc) To retrieve that name I can do this: VBProject.VBComponents(index).name and I can change it in a similar way. My problem is this... I am building new by copying entire sheets (which have lots of data) into a new template - that has a new VB Project. I want to rename the VB Code Module of each copied worksheet from it's current default (like Sheet1) to the name it has in the workbook in Excel... so how, using the VBIDE/VBE Object module can I reference the code module of a worksheet to change it's name to the name it has in the workbook? thanks Philip |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing VB Component Names to match Worksheet names using VBE
I got it... simplicity itself:
For Each objSheet In Worksheets objSheet.CodeName = VBA.Replace(objSheet.Name, " ", "_") Next thx anyway "Philip" wrote: Hi, In VBE the worksheets can be identified using a name that never changes (default Sheet1 etc) To retrieve that name I can do this: VBProject.VBComponents(index).name and I can change it in a similar way. My problem is this... I am building new by copying entire sheets (which have lots of data) into a new template - that has a new VB Project. I want to rename the VB Code Module of each copied worksheet from it's current default (like Sheet1) to the name it has in the workbook in Excel... so how, using the VBIDE/VBE Object module can I reference the code module of a worksheet to change it's name to the name it has in the workbook? thanks Philip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using the Excel generic worksheet names instead of user-given names in code | Excel Discussion (Misc queries) | |||
can worksheet names be changed by changing certain cells? | Excel Worksheet Functions | |||
Changing worksheet names | Excel Worksheet Functions | |||
return all worksheet tab names and chart sheet tab names in report - an example | Excel Programming | |||
Change names of files in a folder to match names in Excel Column | Excel Programming |