return text not index for property
 

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

return text not index for property
 

sorry Try
MsgBox ActiveWorkbook.Name

Should be the good one!


Nicky Wrote:
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

--
Sibili
-----------------------------------------------------------------------
Sibilia's Profile: http://www.excelforum.com/member.php...fo&userid=2136
View this thread: http://www.excelforum.com/showthread.php?threadid=38080

return text not index for property
 

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"


--
Nicky


------------------------------------------------------------------------
Nicky's Profile: http://www.excelforum.com/member.php...nfo&userid=312
View this thread: http://www.excelforum.com/showthread...hreadid=380800

return text not index for property
 

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"
wrote in message
...

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

return text not index for property
 

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


--
Nicky


------------------------------------------------------------------------
Nicky's Profile: http://www.excelforum.com/member.php...nfo&userid=312
View this thread: http://www.excelforum.com/showthread...hreadid=380800