Sub GetVbReferences()
' ASSUMPTION: a worksheet called "VbReferences" exists.
' Write to worksheet code per samanco in http://www.mrexcel.com/forum/showthread.php?t=442346
' Added my own code to check if mscomct2.ocx is installed &/or registered or if trust centre option enabled.
' If not, a msgbox will display with full instructions on what to do/check.
On Error Resume Next
Dim n As Integer
Dim s1 As String, s2 As String, s3 As String
'GUID & Name of "Microsoft Windows Common Controls-2"
s2 = "{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}"
s3 = "mscomct2.ocx"
' Fill VbReferences worksheet with details on selected VBProject References
With ThisWorkbook.Worksheets("VbReferences")
.Cells.Clear
.Cells(1, 1) = "Item"
.Cells(1, 2) = "Name"
.Cells(1, 3) = "Type"
.Cells(1, 4) = "Description"
.Cells(1, 5) = "Is Broken"
.Cells(1, 6) = "Major"
.Cells(1, 7) = "Minor"
.Cells(1, 8) = "GUID"
.Cells(1, 9) = "Full Path"
.Cells(1, 10) = "Built In"
n = ActiveWorkbook.VBProject.References.Count 'Compile error: Invalid use of property
If n = 0 Then
MsgBox "Unable to check if " & s3 & " (Microsoft Windows Common Controls-2) is installed &/or registered." _
& "This is required for the date selection objects to be usable." _
& vbCrLf & vbCrLf & "Check if this is enabled:" _
& vbCrLf & vbCrLf & "Excel 2003 & XP:" _
& vbCrLf & " * Tools > Macro > Security > Trusted Sources >" _
& vbCrLf & " tick 'Trust access to Visual Basic Project' check box > OK." _
& vbCrLf & vbCrLf & "Excel 2007 & 2010:" _
& vbCrLf & " * Click Microsoft Office button > Excel Options >" _
& vbCrLf & " Trust Center > Trust Center Settings > Macro Settings >" _
& vbCrLf & " tick 'Trust access to the VBA project object model' check box > OK." _
, vbInformation, s3 & " installed or registered?"
Exit Sub
End If
' loop through all VBProject References
For n = 1 To ActiveWorkbook.VBProject.References.Count
Select Case ActiveWorkbook.VBProject.References.Item(n).Type
Case 0: s1 = "TypeLib"
Case 1: s1 = "Project"
End Select
'ERROR NOTE: Name, Description & FullPath can cause: Method 'Name' of object 'Reference' failed
.Cells(n + 1, 1) = n
.Cells(n + 1, 2) = ActiveWorkbook.VBProject.References.Item(n).Name
.Cells(n + 1, 3) = s1
.Cells(n + 1, 4) = ActiveWorkbook.VBProject.References.Item(n).Description
.Cells(n + 1, 5) = ActiveWorkbook.VBProject.References.Item(n).IsBroken
.Cells(n + 1, 6) = ActiveWorkbook.VBProject.References.Item(n).Major
.Cells(n + 1, 7) = ActiveWorkbook.VBProject.References.Item(n).Minor
.Cells(n + 1, 8) = ActiveWorkbook.VBProject.References.Item(n).GUID
.Cells(n + 1, 9) = ActiveWorkbook.VBProject.References.Item(n).FullPath
.Cells(n + 1, 10) = ActiveWorkbook.VBProject.References.Item(n).BuiltIn
' Check if mscomct2.ocx is installed &/or registered so DTPicker recognised
' (Microsoft Windows Common Controls-2)
If ActiveWorkbook.VBProject.References.Item(n).GUID = s2 Then
' NOT installed
If ActiveWorkbook.VBProject.References.Item(n).IsBroken = True Then
MsgBox s3 & " (Microsoft Windows Common Controls-2)" _
& vbCrLf & "is not installed (or registered). " _
& "This is required for the date selection objects to be usable." _
& vbCrLf & vbCrLf & "1) Download mscomct2.cab from http://activex.microsoft.com/controls/vb6/MSCOMCT2.CAB" _
& vbCrLf & "2) Unzip/extract mscomct2.cab (.ocx & .inf)" _
& vbCrLf & "3) Copy extracted files to relevant directory as administrator (click continue if prompted for admin permission)" _
& vbCrLf & " c:\windows\system\ = Windows 95, 98, or ME" _
& vbCrLf & " c:\WINNT\system32\ = Windows NT or 2000" _
& vbCrLf & " c:\windows\system32\ = Windows XP or 7" _
& vbCrLf & " c:\windows\sysWOW64\ = Windows 7 64bit" _
& vbCrLf & "4) Close & re-open spreadsheet." _
, vbCritical, s3 & " missing!"
' registered
ElseIf InStr(0, ActiveWorkbook.VBProject.References.Item(n).FullPath, s3) > 0 Then
'do nothing
' NOT registered
Else
MsgBox s3 & "(Microsoft Windows Common Controls-2)" _
& vbCrLf & "is not registered. " _
& "This is required for the date selection objects to be usable." _
& vbCrLf & vbCrLf & "Register it by doing ONE of the following &" _
& vbCrLf & "close & re-open spreadsheet." _
& vbCrLf & " * Start > Run > regsvr32 [fullpath]/mscomct2.ocx" _
& vbCrLf & " * Start > Programs > Accessories >" _
& vbCrLf & " right click 'Command Prompt' >" _
& vbCrLf & " Run as administrator > regsvr32 [fullpath]/mscomct2.ocx" _
& vbCrLf & vbCrLf & "Full paths:" _
& vbCrLf & " regsvr32 c:\windows\system\ = Windows 95, 98, or ME" _
& vbCrLf & " regsvr32 c:\WINNT\system32\ = Windows NT or 2000" _
& vbCrLf & " regsvr32 c:\windows\system32\ = Windows XP or 7" _
& vbCrLf & " regsvr32 c:\windows\sysWOW64\ = Windows 7 64bit" _
, vbCritical, s3 & " not registered!"
End If
End If
Next n
.Cells.EntireColumn.AutoFit
End With
Exit Sub
End Sub
.Name
