Thanks!
George

Hi,

It is not a function; it is a constant. If you lookup msgbox function and
msgbox constants in Help you will find out more about it. The following is a
small example of its use where the users selection is returned to a variable.

Sub test()
Dim Response As Variant

Response = MsgBox("Do you want to continue?", vbOKCancel)

If Response = vbOK Then
MsgBox "User selected OK"
'Insert required code here
Else
MsgBox "User cancelled." & vbLf & _
"Processing terminated."

End If

End Sub



--
Regards,

OssieMac


"G Lykos" wrote:

Thanks!
George


.

Dim Response As Variant

You'll get Intellisense support and lose the overhead inherent in
Variants if you declare Response as

Dim Response As VbMsgBoxResult


VbMsgBoxResult is the enum that defines vbYes, vbNo, and all the other
MsgBox constants.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Sat, 13 Feb 2010 14:51:01 -0800, OssieMac
wrote:

Hi,

It is not a function; it is a constant. If you lookup msgbox function and
msgbox constants in Help you will find out more about it. The following is a
small example of its use where the users selection is returned to a variable.

Sub test()
Dim Response As Variant

Response = MsgBox("Do you want to continue?", vbOKCancel)

If Response = vbOK Then
MsgBox "User selected OK"
'Insert required code here
Else
MsgBox "User cancelled." & vbLf & _
"Processing terminated."

End If

End Sub

IF you have the TypeLib Info DLL installed on your computer, you can
do this. In VBA, go to the Tools menu, choose References, and scroll
down to "TypeLib Info". If that appears in the list, check it. If it
isn't in the list, you probably don't have the required library (email
me for more info). The file name you need to find is TLBINF32.dll.
With the reference in place, you can use code similar to the
following:

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim EnumName As String
Dim ValueName As String

EnumName = "VBMsgBoxResult"
ValueName = "vbYes"

Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA") .FullPath)
For Each MemInfo In
TLITypeLibInfo.Constants.NamedItem(EnumName).Membe rs
If StrComp(ValueName, MemInfo.Name, vbTextCompare) = 0 Then
Debug.Print "Value Name: " & ValueName & _
" is equal to: " & CStr(MemInfo.Value)
Exit For
End If
Next MemInfo
End Sub

This looks up the string "vbYes" in the "VBMsgBoxResult" enumeration
and returns the numeric value (6) which is assigned to vbYes.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]






On Sat, 13 Feb 2010 14:46:22 -0700, "G Lykos"
wrote:

Thanks!
George

Chip, thanks for the guidance. Will be tomorrow before I can experiment
with this. A concern regarding this solution for my intended use is that it
appears I need to know the vbConstant's associated enumeration. VBA module
code interpretation, and the VBE Immediate window, don't require such to use
the constants directly.

Allow me to briefly explain my interest. I'm working with cell comments,
and wish to set the comment box shape to something special. I set it via
Cell.Comment.Shape.AutoShapeType. There are 139 related vbConstants for
various shapes, e.g. msoShape24pointStar. I'd like to write a small loop to
cycle through them, setting the box as I go, so I can see what they look
like. I can relatively easily find a list of the constant names and paste
it into a spreadsheet. First thought was to read those names into an array
as strings, then process it using some UnknownStandardFunction to evaluate
the constants on the fly.

In the absence of such, I took the names in the spreadsheet and using
leading and trailing text cells to concatenate VB code like 'msoShape(1,1) =
"msoShape24pointStar" : msoShape(1,2) = msoShape24pointStar' (139 lines
worth) and copied/pasted it into a module code block, then fed the array to
a small display loop. This worked, but sure seems crude.

Perhaps an alternative, more elegant approach would be to (in this case)
take the AutoShapeType enumeration (is it one?), feed its name to a routine
like you outlined, and just cycle through all members to see names and
values. That would effectively be dumping out the library directly from the
source.

Incidentally, I do see 'TypeLib Information' in my VBE list of available
references. It appears the suggested code would find all flavors of
VBE-available constants, i.e. vbYes, msoShape24pointStar, and so forth.

Thoughts?

Thanks,
George


"Chip Pearson" wrote in message
...
IF you have the TypeLib Info DLL installed on your computer, you can
do this. In VBA, go to the Tools menu, choose References, and scroll
down to "TypeLib Info". If that appears in the list, check it. If it
isn't in the list, you probably don't have the required library (email
me for more info). The file name you need to find is TLBINF32.dll.
With the reference in place, you can use code similar to the
following:

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim EnumName As String
Dim ValueName As String

EnumName = "VBMsgBoxResult"
ValueName = "vbYes"

Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA") .FullPath)
For Each MemInfo In
TLITypeLibInfo.Constants.NamedItem(EnumName).Membe rs
If StrComp(ValueName, MemInfo.Name, vbTextCompare) = 0 Then
Debug.Print "Value Name: " & ValueName & _
" is equal to: " & CStr(MemInfo.Value)
Exit For
End If
Next MemInfo
End Sub

This looks up the string "vbYes" in the "VBMsgBoxResult" enumeration
and returns the numeric value (6) which is assigned to vbYes.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]






On Sat, 13 Feb 2010 14:46:22 -0700, "G Lykos"
wrote:

Thanks!
George

If you don't want to include the enumeration name, you can search all
enumerations looking for the desired name. The following code will
search the entire VBA typelib for 'vbYes' and its associated numeric
value. It is not necessary to specify "VbMsgboxResult" because all
enumerations are searched.

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim ValueName As String
Dim ConstInfo As TLI.ConstantInfo
Dim Found As Boolean

ValueName = "vbYes"
Found = False
Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA") .FullPath)

For Each ConstInfo In TLITypeLibInfo.Constants
For Each MemInfo In ConstInfo.Members
If StrComp(ValueName, MemInfo.Name, vbTextCompare) = 0 Then
Debug.Print "Value Name: " & ValueName & _
" is equal to: " & CStr(MemInfo.Value)
Found = True
Exit For
End If
Next MemInfo
If Found = True Then
Exit For
End If
Next ConstInfo

End Sub


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Sat, 13 Feb 2010 18:56:16 -0700, "G Lykos"
wrote:

Chip, thanks for the guidance. Will be tomorrow before I can experiment
with this. A concern regarding this solution for my intended use is that it
appears I need to know the vbConstant's associated enumeration. VBA module
code interpretation, and the VBE Immediate window, don't require such to use
the constants directly.

Allow me to briefly explain my interest. I'm working with cell comments,
and wish to set the comment box shape to something special. I set it via
Cell.Comment.Shape.AutoShapeType. There are 139 related vbConstants for
various shapes, e.g. msoShape24pointStar. I'd like to write a small loop to
cycle through them, setting the box as I go, so I can see what they look
like. I can relatively easily find a list of the constant names and paste
it into a spreadsheet. First thought was to read those names into an array
as strings, then process it using some UnknownStandardFunction to evaluate
the constants on the fly.

In the absence of such, I took the names in the spreadsheet and using
leading and trailing text cells to concatenate VB code like 'msoShape(1,1) =
"msoShape24pointStar" : msoShape(1,2) = msoShape24pointStar' (139 lines
worth) and copied/pasted it into a module code block, then fed the array to
a small display loop. This worked, but sure seems crude.

Perhaps an alternative, more elegant approach would be to (in this case)
take the AutoShapeType enumeration (is it one?), feed its name to a routine
like you outlined, and just cycle through all members to see names and
values. That would effectively be dumping out the library directly from the
source.

Incidentally, I do see 'TypeLib Information' in my VBE list of available
references. It appears the suggested code would find all flavors of
VBE-available constants, i.e. vbYes, msoShape24pointStar, and so forth.

Thoughts?

Thanks,
George


"Chip Pearson" wrote in message
.. .
IF you have the TypeLib Info DLL installed on your computer, you can
do this. In VBA, go to the Tools menu, choose References, and scroll
down to "TypeLib Info". If that appears in the list, check it. If it
isn't in the list, you probably don't have the required library (email
me for more info). The file name you need to find is TLBINF32.dll.
With the reference in place, you can use code similar to the
following:

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim EnumName As String
Dim ValueName As String

EnumName = "VBMsgBoxResult"
ValueName = "vbYes"

Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA") .FullPath)
For Each MemInfo In
TLITypeLibInfo.Constants.NamedItem(EnumName).Membe rs
If StrComp(ValueName, MemInfo.Name, vbTextCompare) = 0 Then
Debug.Print "Value Name: " & ValueName & _
" is equal to: " & CStr(MemInfo.Value)
Exit For
End If
Next MemInfo
End Sub

This looks up the string "vbYes" in the "VBMsgBoxResult" enumeration
and returns the numeric value (6) which is assigned to vbYes.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]






On Sat, 13 Feb 2010 14:46:22 -0700, "G Lykos"
wrote:

Thanks!
George

Excellent - thanks much!

This, with the addition of the two other library files as outlined in your
2005 thread, would appear to lend itself to a function to perform the
in-line evaluation of any VBE constant name (vb, xl, mso) stored in a
variable. Will play around with it, advise all.

Regards,
George

"Chip Pearson" wrote in message
...
If you don't want to include the enumeration name, you can search all
enumerations looking for the desired name. The following code will
search the entire VBA typelib for 'vbYes' and its associated numeric
value. It is not necessary to specify "VbMsgboxResult" because all
enumerations are searched.

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim ValueName As String
Dim ConstInfo As TLI.ConstantInfo
Dim Found As Boolean

ValueName = "vbYes"
Found = False
Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA") .FullPath)

For Each ConstInfo In TLITypeLibInfo.Constants
For Each MemInfo In ConstInfo.Members
If StrComp(ValueName, MemInfo.Name, vbTextCompare) = 0 Then
Debug.Print "Value Name: " & ValueName & _
" is equal to: " & CStr(MemInfo.Value)
Found = True
Exit For
End If
Next MemInfo
If Found = True Then
Exit For
End If
Next ConstInfo

End Sub


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Sat, 13 Feb 2010 18:56:16 -0700, "G Lykos"
wrote:

Chip, thanks for the guidance. Will be tomorrow before I can experiment
with this. A concern regarding this solution for my intended use is that
it
appears I need to know the vbConstant's associated enumeration. VBA
module
code interpretation, and the VBE Immediate window, don't require such to
use
the constants directly.

Allow me to briefly explain my interest. I'm working with cell comments,
and wish to set the comment box shape to something special. I set it via
Cell.Comment.Shape.AutoShapeType. There are 139 related vbConstants for
various shapes, e.g. msoShape24pointStar. I'd like to write a small loop
to
cycle through them, setting the box as I go, so I can see what they look
like. I can relatively easily find a list of the constant names and paste
it into a spreadsheet. First thought was to read those names into an
array
as strings, then process it using some UnknownStandardFunction to evaluate
the constants on the fly.

In the absence of such, I took the names in the spreadsheet and using
leading and trailing text cells to concatenate VB code like 'msoShape(1,1)
=
"msoShape24pointStar" : msoShape(1,2) = msoShape24pointStar' (139 lines
worth) and copied/pasted it into a module code block, then fed the array
to
a small display loop. This worked, but sure seems crude.

Perhaps an alternative, more elegant approach would be to (in this case)
take the AutoShapeType enumeration (is it one?), feed its name to a
routine
like you outlined, and just cycle through all members to see names and
values. That would effectively be dumping out the library directly from
the
source.

Incidentally, I do see 'TypeLib Information' in my VBE list of available
references. It appears the suggested code would find all flavors of
VBE-available constants, i.e. vbYes, msoShape24pointStar, and so forth.

Thoughts?

Thanks,
George


"Chip Pearson" wrote in message
. ..
IF you have the TypeLib Info DLL installed on your computer, you can
do this. In VBA, go to the Tools menu, choose References, and scroll
down to "TypeLib Info". If that appears in the list, check it. If it
isn't in the list, you probably don't have the required library (email
me for more info). The file name you need to find is TLBINF32.dll.
With the reference in place, you can use code similar to the
following:

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim EnumName As String
Dim ValueName As String

EnumName = "VBMsgBoxResult"
ValueName = "vbYes"

Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA") .FullPath)
For Each MemInfo In
TLITypeLibInfo.Constants.NamedItem(EnumName).Membe rs
If StrComp(ValueName, MemInfo.Name, vbTextCompare) = 0 Then
Debug.Print "Value Name: " & ValueName & _
" is equal to: " & CStr(MemInfo.Value)
Exit For
End If
Next MemInfo
End Sub

This looks up the string "vbYes" in the "VBMsgBoxResult" enumeration
and returns the numeric value (6) which is assigned to vbYes.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]






On Sat, 13 Feb 2010 14:46:22 -0700, "G Lykos"
wrote:

Thanks!
George

P.S. Misspoke regarding a resource - guidance on connecting to the three
(vb, xl, mso) libraries will come from your web site download file
XLConsts2a.zip. The thread mentioned had led me there.


"G Lykos" wrote in message
...
Excellent - thanks much!

This, with the addition of the two other library files as outlined in your
2005 thread, would appear to lend itself to a function to perform the
in-line evaluation of any VBE constant name (vb, xl, mso) stored in a
variable. Will play around with it, advise all.

Regards,
George

"Chip Pearson" wrote in message
...
If you don't want to include the enumeration name, you can search all
enumerations looking for the desired name. The following code will
search the entire VBA typelib for 'vbYes' and its associated numeric
value. It is not necessary to specify "VbMsgboxResult" because all
enumerations are searched.

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim ValueName As String
Dim ConstInfo As TLI.ConstantInfo
Dim Found As Boolean

ValueName = "vbYes"
Found = False
Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA") .FullPath)

For Each ConstInfo In TLITypeLibInfo.Constants
For Each MemInfo In ConstInfo.Members
If StrComp(ValueName, MemInfo.Name, vbTextCompare) = 0 Then
Debug.Print "Value Name: " & ValueName & _
" is equal to: " & CStr(MemInfo.Value)
Found = True
Exit For
End If
Next MemInfo
If Found = True Then
Exit For
End If
Next ConstInfo

End Sub


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Sat, 13 Feb 2010 18:56:16 -0700, "G Lykos"
wrote:

Chip, thanks for the guidance. Will be tomorrow before I can experiment
with this. A concern regarding this solution for my intended use is that
it
appears I need to know the vbConstant's associated enumeration. VBA
module
code interpretation, and the VBE Immediate window, don't require such to
use
the constants directly.

Allow me to briefly explain my interest. I'm working with cell comments,
and wish to set the comment box shape to something special. I set it via
Cell.Comment.Shape.AutoShapeType. There are 139 related vbConstants for
various shapes, e.g. msoShape24pointStar. I'd like to write a small loop
to
cycle through them, setting the box as I go, so I can see what they look
like. I can relatively easily find a list of the constant names and
paste
it into a spreadsheet. First thought was to read those names into an
array
as strings, then process it using some UnknownStandardFunction to
evaluate
the constants on the fly.

In the absence of such, I took the names in the spreadsheet and using
leading and trailing text cells to concatenate VB code like
'msoShape(1,1) =
"msoShape24pointStar" : msoShape(1,2) = msoShape24pointStar' (139 lines
worth) and copied/pasted it into a module code block, then fed the array
to
a small display loop. This worked, but sure seems crude.

Perhaps an alternative, more elegant approach would be to (in this case)
take the AutoShapeType enumeration (is it one?), feed its name to a
routine
like you outlined, and just cycle through all members to see names and
values. That would effectively be dumping out the library directly from
the
source.

Incidentally, I do see 'TypeLib Information' in my VBE list of available
references. It appears the suggested code would find all flavors of
VBE-available constants, i.e. vbYes, msoShape24pointStar, and so forth.

Thoughts?

Thanks,
George


"Chip Pearson" wrote in message
...
IF you have the TypeLib Info DLL installed on your computer, you can
do this. In VBA, go to the Tools menu, choose References, and scroll
down to "TypeLib Info". If that appears in the list, check it. If it
isn't in the list, you probably don't have the required library (email
me for more info). The file name you need to find is TLBINF32.dll.
With the reference in place, you can use code similar to the
following:

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim EnumName As String
Dim ValueName As String

EnumName = "VBMsgBoxResult"
ValueName = "vbYes"

Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA") .FullPath)
For Each MemInfo In
TLITypeLibInfo.Constants.NamedItem(EnumName).Membe rs
If StrComp(ValueName, MemInfo.Name, vbTextCompare) = 0 Then
Debug.Print "Value Name: " & ValueName & _
" is equal to: " & CStr(MemInfo.Value)
Exit For
End If
Next MemInfo
End Sub

This looks up the string "vbYes" in the "VBMsgBoxResult" enumeration
and returns the numeric value (6) which is assigned to vbYes.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]






On Sat, 13 Feb 2010 14:46:22 -0700, "G Lykos"
wrote:

Thanks!
George

Chip, ran and executed the suggested code. Searching for a solution to
"programmatic access not trusted" led me to your related thread in 2005,
same subject. Downloaded xlConsts2.zip from your site, looks like it
already does what I was interested in doing. Thanks for making it
available!

Best regards,
George


"Chip Pearson" wrote in message
...
IF you have the TypeLib Info DLL installed on your computer, you can
do this. In VBA, go to the Tools menu, choose References, and scroll
down to "TypeLib Info". If that appears in the list, check it. If it
isn't in the list, you probably don't have the required library (email
me for more info). The file name you need to find is TLBINF32.dll.
With the reference in place, you can use code similar to the
following:

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim EnumName As String
Dim ValueName As String

EnumName = "VBMsgBoxResult"
ValueName = "vbYes"

Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA") .FullPath)
For Each MemInfo In
TLITypeLibInfo.Constants.NamedItem(EnumName).Membe rs
If StrComp(ValueName, MemInfo.Name, vbTextCompare) = 0 Then
Debug.Print "Value Name: " & ValueName & _
" is equal to: " & CStr(MemInfo.Value)
Exit For
End If
Next MemInfo
End Sub

This looks up the string "vbYes" in the "VBMsgBoxResult" enumeration
and returns the numeric value (6) which is assigned to vbYes.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]






On Sat, 13 Feb 2010 14:46:22 -0700, "G Lykos"
wrote:

Thanks!
George