How to get a dump of Fill Color & Font VBA Constants?

[EagleOne]

2003

Would like to create a w/s that has a listing of Font constants like
-4105 and Fill like 8 that are applicable to my workbook as I thing
that the data follows th book?.

Once that I have that list I then will do a Vlookup to populate another
worksheet.

Thanks,

EagleOne

 

[Guest]

See this: http://www.cpearson.com/excel/CFColors.htm

and this: http://www.cpearson.com/excel/colors.htm

 

[Bob Phillips]

First set a reference to the Typelib dll. You will need to browse for it, it
won't be automatically loaded, On my machine it is called TLBINF32.DLL.

Then run this code. It is set for Exzcel XP, you might need to check the OLB
file for your Excel.


VBA:

Public Sub GetWordConstants()
Dim oOLB As Object
Dim sText As String
Dim oOLBc, oOLBm
Dim j As Integer

On Error Resume Next

With Worksheets("Constants")
With .Range("A1")
.Offset(0, 1).Value = "Excel"
.Offset(1, 1).Value = "XL5EN32.OLB"
.Cells(3, 1).Resize(.CurrentRegion.Rows.Count, 2).ClearContents
Set oOLB = TypeLibInfoFromFile(Application.Path &
"\XL5EN32.OLB")
j = 2
For Each oOLBc In oOLB.Constants
For Each oOLBm In oOLBc.Members
.Offset(j, 0).Value = oOLBm.Name
.Offset(j, 1).Value = oOLBm.Value
j = j + 1
Next oOLBm
Next oOLBc
End With
.Visible = True
.Activate
.Range("A1").Select
End With

Set oOLB = Nothing

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[EagleOne]

Thanks Bob!

First set a reference to the Typelib dll. You will need to browse for it, it
won't be automatically loaded, On my machine it is called TLBINF32.DLL.

Then run this code. It is set for Exzcel XP, you might need to check the OLB
file for your Excel.


VBA:

Public Sub GetWordConstants()
Dim oOLB As Object
Dim sText As String
Dim oOLBc, oOLBm
Dim j As Integer

On Error Resume Next

With Worksheets("Constants")
With .Range("A1")
.Offset(0, 1).Value = "Excel"
.Offset(1, 1).Value = "XL5EN32.OLB"
.Cells(3, 1).Resize(.CurrentRegion.Rows.Count, 2).ClearContents
Set oOLB = TypeLibInfoFromFile(Application.Path &
"\XL5EN32.OLB")
j = 2
For Each oOLBc In oOLB.Constants
For Each oOLBm In oOLBc.Members
.Offset(j, 0).Value = oOLBm.Name
.Offset(j, 1).Value = oOLBm.Value
j = j + 1
Next oOLBm
Next oOLBc
End With
.Visible = True
.Activate
.Range("A1").Select
End With

Set oOLB = Nothing

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)