Solved: I need somebody to fill in the missing part in this code

[xltrader100]

Function fetchThisPropertyValue(byVal controlName as String, byVal propertyName as String) as Variant

< your code goes here >

fetchThisPropertyValue = ??
End Function

Sub whatColor()
var1 = "Label1"
var2 = "BackColor"
msgBox "Current color of Label1 is " & fetchThisPropertyValue(var1, var2)
End sub
if Label1 is red then msgBox reads like &H000000FF& or 255

Each Property will alway be valid for the Control it's paired with, and the function will run on the same form as the control being queried.

[Bob Phillips]

You will need to set a reference to the Typelib library, and pass this function the actual object and the property name



'-----------------------------------------------------------------
Public Function GetProperty(obj As Object, prop As String)
'-----------------------------------------------------------------
Dim mpTLI As New TLIApplication
Dim mpInterface As InterfaceInfo
Dim mpMember As MemberInfo
Dim mpResult As Variant
Dim i As Long

Set mpInterface = mpTLI.InterfaceInfoFromObject(obj)
For i = 1 To mpInterface.Members.Count

Set mpMember = mpInterface.Members.Item(i)
If mpMember.Name = prop Then

mpResult = ""
mpResult = TLI.InvokeHook(obj, mpMember.MemberId, INVOKE_PROPERTYGET)
'adjust long values; convert to hex (else shows neg value)
If TypeName(mpResult) = "Long" Then

mpResult = "&H" & Hex(mpResult) & "&"
End If

GetProperty = mpResult
End If
Next i
Set mpTLI = Nothing
Set mpInterface = Nothing
Set mpMember = Nothing
End Function

[xltrader100]

That sure looks like what I want, but I can't find a reference to Typelib library in my Excel 2003. Should it be there (as standard issue) or do I have to import it?

[Bob Phillips]

It is called 'Typelib Information', and the dll is tlbinf32.dll

[xltrader100]

Tlbinf32.dll wasn't on my machine. I found a 2005 MS doc saying this dll was only distributed in Visual Studio 6.0 and Visual Basic 6.0 CD's.

Anyway, I downloaded and registered it and your function works brilliantly. I also downloaded the Help file for Tlbinf32.dll http://support.microsoft.com/kb/224331 which is very useful.

But now what? How do I save this wb in a form that can be used by any random Excel user? This must be a recurring problem for developers, so is there a good solution? I'd rather not make any add-ins if I can help it.

[Bob Phillips]

I have VS and VB6, so that must be why I had it.

You are wrong, it is not a recurring problem. I only knew how to do it because I have an addin that looks at userforms and gets all the controls, and all the properties of same (I built it to help me easily edit userforms, rather than using the Userform design mode). I have told others about it, and very few have been interested, and I have not seen a request like yours before.

If you want to make it available, a GUI and and an addin is the way to go IMO.

[xltrader100]

I've run into a wee problem with getProperty. The form loads normally, and the function works normally, but when I unload it and try to make any changes to the form code, I can't recompile. I get a compiler error of "User defined type not defined" when it comes to dim mpTLI As New TLIApplication. I have to comment out the function to recompile the other code, or if I just ignore the error, the form and function still load and work properly. Any ideas?