Need Code to rename standard modules

[davidm]

Can someone kindly help me out with a code to *rename* standard modules?
I have googled in vain for leads. Even Chip Pearson seemed to have miss
this one in his comprehensive coverage of codings to manipulate
VBComponents.

[I intend using the first 8 or so characters of ProcedureTitles to
rename meaningfully and can quite comfortably handle this bit of
extracting the characters].

TIA

David

 

[Guest]

You probably know this but ensure that the Microsoft Visual Basic for
Applications Extensibility reference is selected before running the code:

Sub RenameMods()
Dim vbmod As VBComponent
For Each vbmod In ThisWorkbook.VBProject.VBComponents
If vbmod.Type = vbext_ct_StdModule Then
Select Case vbmod.Name
Case "Module1"
vbmod.Name = "HelloWorld_1"
Case "Module2"
vbmod.Name = "HelloWorld_2"
Case "Module3"
vbmod.Name = "HelloWorld_3"
End Select
End If
Next
End Sub

Regards,
Greg

 

[davidm]

Many thanks Greg. For some reason, that code has never worked for me,
logical as it is (and with Microsoft Visual Basic for Applications
Extensibility reference enabled). I would welcome any feedback on
this.

[ps: I am sorry for the multiple postings of the initial thread. It was
due to server problems I was encountering and which made it hard to
authenticate submissions].


David

 

[Guest]

If you meant the code I gave you doesn't work for you, it may be that you
have a later version of Excel that restricts access to the VBProject. I'm
running xl2000 and don't have a problem.

Check Tools>Macro>Security and check to see if there is a "Trust access to
VBProject" checkbox in one of the tabs. I don't have this but have seen it in
later versions. If so, make sure this checkbox is checked. Otherwise, it is
likely a security issue that I'm not up to date with.

Regards,
Greg

 

[Chip Pearson]

David,

Try

ThisWorkbook.VBProject.VBComponents("Sheet1").Name = "NewName"

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"davidm" <[email protected]>
wrote in message
news:[email protected]...

 

[Chip Pearson]

Even Chip Pearson seemed to have miss

this one in his comprehensive coverage of codings to manipulate
VBComponents.

Actually, the info is hidden on the CodeMods page,
http://www.cpearson.com/excel/codemods.htm . I'll add it to the
VBE page.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"davidm" <[email protected]>
wrote in message

Can someone kindly help me out with a code to *rename* standard
modules?
I have googled in vain for leads. Even Chip Pearson seemed to
have miss
this one in his comprehensive coverage of codings to manipulate
VBComponents.

[I intend using the first 8 or so characters of ProcedureTitles
to
rename meaningfully and can quite comfortably handle this bit
of
extracting the characters].

TIA

David


--
davidm
------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=20645
View this thread:
http://www.excelforum.com/showthread.php?threadid=526630