Programmatically determining CODE NAME for sheet based upon Sheet

[Barb Reinhardt]

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

[Jim Thomlinson]

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

--
HTH...

Jim Thomlinson


"Barb Reinhardt" wrote:

> 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
>
>

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote:

> 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
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Barb Reinhardt" wrote:
>
> > 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
> >
> >

[Jim Thomlinson]

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...
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Jim Thomlinson" wrote:
>
> > 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
> >
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Barb Reinhardt" wrote:
> >
> > > 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
> > >
> > >

[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.



Jim Thomlinson wrote:
>
> 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...
> --
> HTH...
>
> Jim Thomlinson
>
> "Tom Ogilvy" wrote:
>
> > 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Jim Thomlinson" wrote:
> >
> > > 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
> > >
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Barb Reinhardt" wrote:
> > >
> > > > 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
> > > >
> > > >

--

Dave Peterson

[Jim Thomlinson]

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...

--
HTH...

Jim Thomlinson


"Dave Peterson" wrote:

> 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.
>
>
>
> Jim Thomlinson wrote:
> >
> > 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...
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> > "Tom Ogilvy" wrote:
> >
> > > 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
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Jim Thomlinson" wrote:
> > >
> > > > 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
> > > >
> > > > --
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "Barb Reinhardt" wrote:
> > > >
> > > > > 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
> > > > >
> > > > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

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

Tom????

Jim Thomlinson wrote:
>
> 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...
>
> --
> HTH...
>
> Jim Thomlinson
>
> "Dave Peterson" wrote:
>
> > 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.
> >
> >
> >
> > Jim Thomlinson wrote:
> > >
> > > 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...
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > 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
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "Jim Thomlinson" wrote:
> > > >
> > > > > 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
> > > > >
> > > > > --
> > > > > HTH...
> > > > >
> > > > > Jim Thomlinson
> > > > >
> > > > >
> > > > > "Barb Reinhardt" wrote:
> > > > >
> > > > > > 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
> > > > > >
> > > > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[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

"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
> 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...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Tom Ogilvy" wrote:
>
>> 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
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "Jim Thomlinson" wrote:
>>
>> > 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
>> >
>> > --
>> > HTH...
>> >
>> > Jim Thomlinson
>> >
>> >
>> > "Barb Reinhardt" wrote:
>> >
>> > > 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
>> > >
>> > >

[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 wrote:
>
> 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
>
> "Jim Thomlinson" <[email protected]> wrote in message
> news:[email protected]...
> > 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...
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Tom Ogilvy" wrote:
> >
> >> 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
> >>
> >> --
> >> Regards,
> >> Tom Ogilvy
> >>
> >>
> >> "Jim Thomlinson" wrote:
> >>
> >> > 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
> >> >
> >> > --
> >> > HTH...
> >> >
> >> > Jim Thomlinson
> >> >
> >> >
> >> > "Barb Reinhardt" wrote:
> >> >
> >> > > 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
> >> > >
> >> > >

--

Dave Peterson

[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

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>>
>> 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
>>
>> "Jim Thomlinson" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> > 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...
>> > --
>> > HTH...
>> >
>> > Jim Thomlinson
>> >
>> >
>> > "Tom Ogilvy" wrote:
>> >
>> >> 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
>> >>
>> >> --
>> >> Regards,
>> >> Tom Ogilvy
>> >>
>> >>
>> >> "Jim Thomlinson" wrote:
>> >>
>> >> > 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
>> >> >
>> >> > --
>> >> > HTH...
>> >> >
>> >> > Jim Thomlinson
>> >> >
>> >> >
>> >> > "Barb Reinhardt" wrote:
>> >> >
>> >> > > 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
>> >> > >
>> >> > >
>
> --
>
> Dave Peterson

[Barb Reinhardt]

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

"Jim Thomlinson" wrote:

> 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...
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Dave Peterson" wrote:
>
> > 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.
> >
> >
> >
> > Jim Thomlinson wrote:
> > >
> > > 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...
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > 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
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "Jim Thomlinson" wrote:
> > > >
> > > > > 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
> > > > >
> > > > > --
> > > > > HTH...
> > > > >
> > > > > Jim Thomlinson
> > > > >
> > > > >
> > > > > "Barb Reinhardt" wrote:
> > > > >
> > > > > > 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
> > > > > >
> > > > > >
> >
> > --
> >
> > Dave Peterson
> >

[Dave Peterson]

Well, you did get colored tabs!



Rob Bovey wrote:
>
> 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
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > 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 wrote:
> >>
> >> 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
> >>
> >> "Jim Thomlinson" <[email protected]> wrote in
> >> message
> >> news:[email protected]...
> >> > 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...
> >> > --
> >> > HTH...
> >> >
> >> > Jim Thomlinson
> >> >
> >> >
> >> > "Tom Ogilvy" wrote:
> >> >
> >> >> 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
> >> >>
> >> >> --
> >> >> Regards,
> >> >> Tom Ogilvy
> >> >>
> >> >>
> >> >> "Jim Thomlinson" wrote:
> >> >>
> >> >> > 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
> >> >> >
> >> >> > --
> >> >> > HTH...
> >> >> >
> >> >> > Jim Thomlinson
> >> >> >
> >> >> >
> >> >> > "Barb Reinhardt" wrote:
> >> >> >
> >> >> > > 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
> >> >> > >
> >> >> > >
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[Rob Bovey]

"Barb Reinhardt" <[email protected]> wrote in message
news:[email protected]...
> 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