return text not index for property

[Nicky]

Hi all
A simple question, I hope
how do I return the name rather than the index number for a property, so that eg

MsgBox ActiveWorkbook.FileFormat

returns "xlWKS" not "39"

[Sibilia]

Try

MsgBox ActiveSheet.Name

Sibilia

Hi all
A simple question, I hope
how do I return the name rather than the index number for a property, so that eg

MsgBox ActiveWorkbook.FileFormat

returns "xlWKS" not "39"

[Sibilia]

sorry Try
MsgBox ActiveWorkbook.Name

Should be the good one!

Hi all
A simple question, I hope
how do I return the name rather than the index number for a property, so that eg

MsgBox ActiveWorkbook.FileFormat

returns "xlWKS" not "39"

[Nicky]

Thanks for the reply, Sibilia

Sorry, I din't explain myself very well. It's not the name of the sheet or workbook I'm after, but of properties of objects within it.

For many objects' properties, instead of returning text, excel returns an index number. Eg if are in a stacked area chart, and run:

MsgBox ActiveChart.ChartType

it returns "76", not "xlAreaStacked"

I'm hoping to get it to return the text description not the index number, ie "xlAreaStacked" not "76"

[Chip Pearson]

It may be a simple question, but it is a complicated answer. And
by the way, xlWKS is a 4, not a 39. First, in VBA go to the Tools
menu, choose References, and scroll down to "Typelib
Information". If you don't have this, then you can't do what you
want. Assuming you do have this library, check it in the list.
Then use code like the following:


Dim TLIApp As TLI.TLIApplication
Dim TLILibInfo As TLI.TypeLibInfo
Dim ConstInfo As TLI.ConstantInfo
Dim MemInfo As TLI.MemberInfo

Set TLIApp = New TLI.TLIApplication
Set TLILibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("EXCEL").FullPath)
Set ConstInfo = TLILibInfo.Constants.NamedItem("XLFileFormat")
'change
' the XLFileFormat to the constant enum group name you want
to search
For Each MemInfo In ConstInfo.Members
If MemInfo.Value = 39 Then ' change the 39 to the value you
want to look up
MsgBox MemInfo.Name
Exit For
End If
Next MemInfo


For much more information about how to do this and more, go to
http://www.cpearson.com/excel/download.htm and download TLIUtils.


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












"Nicky" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all
> A simple question, I hope
> how do I return the name rather than the index number for a
> property,
> so that eg
>
> MsgBox ActiveWorkbook.FileFormat
>
> returns "xlWKS" not "39"
>
>
> --
> Nicky
>
>
> ------------------------------------------------------------------------
> Nicky's Profile:
> http://www.excelforum.com/member.php...nfo&userid=312
> View this thread:
> http://www.excelforum.com/showthread...hreadid=380800
>

[Nicky]

Thanks Chip, that worked perfectly, though more complicated than I'd expected.

for anyone repeating Chip's method, you'll also need to change macro security to include the VB project as a tructed source

Nicky