Programmatically determining CODE NAME for sheet based upon Sheet

[Guest]

I have about 20 workbooks that I want to extract the code name for a workbook
I'll call oWB. Let's say the the sheet names are "SUMMARY_1" and
"SUMMARY_2". How do I determine what the code names are for each sheet in
the workbook?

Thanks,
Barb Reinhardt

 

[Guest]

This code should be close... ( I tested it locally but you should be able to
adapt it for your 20 workbooks)

Sub test()
Dim oWB As Workbook
Dim oSH As Worksheet

Set oWB = ThisWorkbook
On Error Resume Next
Set oSH = oWB.Sheets("SUMMARY_1")
On Error GoTo 0
If oSH Is Nothing Then
MsgBox "SUMMARY_1 does not exist"
Else
MsgBox "SUMMARY_1's code name is " & oSH.CodeName
End If
End Sub

 

[Guest]

In some versions of Excel, this might be problemmatic if the VBE isn't open.
I believe Chip Pearson shows a more robust way at
http://www.cpearson.com/excel/vbe.htm

 

[Guest]

Ok, I'm lost. What part of my code requires the VBE to be open? I don't doubt
you (since that has almost never worked out in the past) but I fail to see
what part of my code requires the VBE as I do not work with VBE components in
any way...

 

[Dave Peterson]

IIRC, there can be a problem getting the codename of a worksheet that is added
in code. Maybe Tom is remembering that--or I'm remembering incorrectly.

 

[Guest]

I've seen that (and I agree) but it seems more like Barb is trying to get the
code name of an existing sheet not a sheet she just added. Chip's site
alludes to something about xl95 and code names but nothing that indicates
that a sheet code name will be an issue...

 

[Dave Peterson]

I'm hoping that it's Tom's memory (and not mine!) <vbg>.

Tom????

 

[Tom Ogilvy]

It was more nefarious than after adding a worksheet:

http://tinyurl.com/eeewp

Unfortunately the thread is incomplete, but the topic was about what I said.
By the way, I never got their solutions to work for me.

The solution I used and which at one time was on Chip's Site was to use the
properties in the VBE sequence of objects.

Probably fixed by now, but can't say.

 

[Rob Bovey]

Hi Jim,

If the VBProject for a workbook has never been altered (e.g. the
workbook has only been operated on from the Excel UI), then it really
doesn't have a VBProject and you can't even depend on the CodeNames. For
example, if you insert a new worksheet in the middle of existing sheets the
CodeNames of all worksheets after the one you inserted will change.

Try this: Create a new workbook with two worksheets and save it. Open
the VBE and you'll see that the CodeNames correspond to the sheet tab names.
Insert a new worksheet between Sheet1 and Sheet2. In the VBE it still shows
the CodeNames as you'd expect. Now save the workbook, close it and reopen
it. The CodeNames are now out of order. Sheet3 has a CodeName of Sheet2 and
Sheet2 has a CodeName of Sheet3.

The reason this happens is because until you do something to manually
alter the VBProject of a workbook, VBA considers it not to have one. VBA
regenerates the VBProject each time you open the workbook, sometimes with
conflicting results.

This also will occasionally cause problems getting access to anything
below the VBProject object of the workbook when the VBE is not open because
VBA may not yet have generated a default VBProject for workbooks with
unedited VBProjects. In my experience you can force VBA to "wake up" by
using the VBProject object of the target workbook in some trivial way:

If wkbBook.VBProject.Protection = 0 Then
''' Calls on VBComponents should succeed now
End If

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Dave Peterson]

Nicely explained.

But...

I have sheet.xlt in my XLStart folder. When I used that to insert the sheet, I
couldn't duplicate what you wrote (I did try a few times).

But when I started excel in Safe mode, everything worked exactly the way you
described.

I remember the workaround that I saw was a simple assignment:
set myProject = someworkbook.vbproject

(just as an aside)

 

[Rob Bovey]

Hi Dave,

Interesting observation about Sheet.xlt. I hadn't noticed that before.
This definitely isn't a well documented or absolutely repeatable phenomenon,
although someone from MS did once confirm they were aware of the problem but
weren't likely to fix it.

I remember the workaround that I saw was a simple assignment:
set myProject = someworkbook.vbproject

Yeah, that should work as well. I think just about anything that
exercises the VBProject object of a workbook will bootstrap the process of
creating the whole VBProject for that workbook if it doesn't already exist.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Guest]

Correct, I'm trying to get the code name of an existing sheet in the
workbook, not one that I've added previously.

Help me understand something. If I have Sheet1, Sheet2 and Sheet3 in the
workbook when I start and I change the Sheet names to say Name1, Name2 and
Name3. I then add a sheet between Name2 and Name3. Do the code names of
the sheets change??? I just checked it and the code name for Sheet3 changes
to Sheet4. I presume that if I changed the code names in the Properties
window for each sheet, they would be static.

Thanks everyone for your assistance.
Barb

 

[Dave Peterson]

Well, you did get colored tabs!

Hi Dave,

Interesting observation about Sheet.xlt. I hadn't noticed that before.
This definitely isn't a well documented or absolutely repeatable phenomenon,
although someone from MS did once confirm they were aware of the problem but
weren't likely to fix it.


Yeah, that should work as well. I think just about anything that
exercises the VBProject object of a workbook will bootstrap the process of
creating the whole VBProject for that workbook if it doesn't already exist.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Rob Bovey]

I presume that if I changed the code names in the Properties
window for each sheet, they would be static.

Hi Barb,

That's correct. As soon as you modify anything in the Visual Basic
Project of a workbook, all aspects of that project will become persistent,
including those you haven't modified.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Dave Peterson]

I remember seeing the problem when new sheets were added (a blank codename was
returned). But I guess I didn't see (or dodn't remember seeing <vbg>) the other
ways that it can manifest itself.

Glad to see that your memory is up to snuff!

I replied to this last night (twice), but it still doesn't appear to have
showed up.

http://tinyurl.com/zrkld

http://tinyurl.com/paeoq

http://tinyurl.com/e95qd

are some links to what I was describing. Think most of it was before you
were a regular here Dave, so it might have gone away by xl2000.