Date/Time/Calendar Control to work in multi Excel platforms (2003/2007/2010)?

shell_l_d

Board Regular
Joined
Jun 25, 2010
Messages
73
Need a Date/Time/Calendar Control to work in multi Excel platforms (2003/2007/2010) please...

So... I have several spreadsheets created using Excel 2007 with 'Calendar Control 12.0' controls in them. It wont work in Excel 2010 as that control was removed from Office 2010!!!

Now... I also have a different spreadsheet created in Excel 2010 with 'Microsoft Date and Time Picker 6.0 (SP6)' controls in it. It wont work in Excel 2007 as that control doesn't exist in Office 2007!!!

I want to have a multi-compatible spreadsheet (that at least works with Excel 2007 & 2010 & probably also 2003) with a date/time/calendar control in it but dont really want to have to instruct each person who may use it to install some add-in to their Excel.

What options do I have please?

Further to:
http://social.msdn.microsoft.com/Fo...be5-5d7f-4fb2-ae16-1438e0e736a4?prof=required

http://www.mrexcel.com/forum/showthread.php?t=70813&page=2
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
I assume this is related...

The spreadsheet created with Excel 2010 has DTPicker controls in it, when I open it in Excel 2007 it gives this popup msgbox:

Calendar Control 12.0 in Excel 2007
Microsoft Visual Basic
Compile error:
Can't find project or library

Highlights 'Format' formula in this extract of the macro:
Code:
    ' Obtain the start & end dates
    dteStartDate = Format(Worksheets("Update").DTPickerStart, "yyyy-mm-dd")
    dteEndDate = Format(Worksheets("Update").DTPickerEnd, "yyyy-mm-dd")

I just checked VBA > Tools > References for Excel 2007 & it has these selected by default (on my machine):
  • Visual Basic For Applications
  • Microsoft Excel 12.0 Object Library
  • OLE Automation
  • Microsoft Office 12.0 Object Library
  • Microsoft ActiveX Data Objects 6.0 Library
  • MISSING: Microsoft Windows Common Controls-2 6.0 (SP6)
  • Microsoft Forms 2.0 Object Library
When I click on 'MISSING: Microsoft Windows Common Controls-2 6.0 (SP6)' it shows location: C:\Windows\system32\MSCOMCT2.OCX
 
Upvote 0
Not sure if there's a way to auto install missing reference libraries...
Is there VB code that can show the name & location of missing reference libraries? Eg: in this case:

Name: 'MISSING: Microsoft Windows Common Controls-2 6.0 (SP6)'
Location: C:\Windows\system32\MSCOMCT2.OCX

If so, then at least I could use a msgbox to display the name & location of missing libraries on workbook open.

Thnx in advance :)
 
Upvote 0
Further research found this... however these dont have values & can cause error: Method 'Name' of object 'Reference' failed
  • ActiveWorkbook.VBProject.References.Item(n).Name
  • ActiveWorkbook.VBProject.References.Item(n).Description
  • ActiveWorkbook.VBProject.References.Item(n).FullPath
so can't show the full path or name like I can see in VB > Tools > References > for the MISSING file.

Rich (BB code):
Sub GetVbReferences()
    ' code from samanco per http://www.mrexcel.com/forum/showthread.php?t=442346
 
    On Error Resume Next
 
    Dim n As Integer
    Dim s As String
 
    With ThisWorkbook.Worksheets("VbReferences")
        '.Select
        .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"
 
        For n = 1 To ActiveWorkbook.VBProject.References.Count
            Select Case ActiveWorkbook.VBProject.References.Item(n).Type
                Case 0: s = "TypeLib"
                Case 1: s = "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) = s
            .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
        Next n
 
    End With
 
    Worksheets("VbReferences").Cells.EntireColumn.AutoFit
 
    Exit Sub
 
End Sub

Also apparently In Office 2002 or later, the TRUST ACCESS TO VISUAL BASIC PROJECT, in the Macro security settings, MUST be checked,
or the code will not work & may cause:
Error 1004
HelpContext 1001004
Programmatic access to Visual Basic Project is not trusted

<TABLE style="WIDTH: 788pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1051><COLGROUP><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1024" width=29><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1820" width=51><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1564" width=44><COL style="WIDTH: 161pt; mso-width-source: userset; mso-width-alt: 7623" width=214><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1820" width=51><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1223" width=34><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1251" width=35><COL style="WIDTH: 170pt; mso-width-source: userset; mso-width-alt: 8078" width=227><COL style="WIDTH: 274pt; mso-width-source: userset; mso-width-alt: 12999" width=366><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 22pt; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=16 width=29>Item</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=51>Name</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 33pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=44>Type</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 161pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=214>Description</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=51>Is Broken</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=34>Major</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=35>Minor</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 170pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=227>GUID</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 274pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=366>Full Path</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=16 align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>VBA</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>TypeLib</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>Visual Basic For Applications</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=middle>FALSE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=right>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=right>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>{000204EF-0000-0000-C000-000000000046}</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=16 align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>Excel</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>TypeLib</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>Microsoft Excel 12.0 Object Library</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=middle>FALSE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=right>6</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>{00020813-0000-0000-C000-000000000046}</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>C:\Program Files\Microsoft Office\Office12\EXCEL.EXE</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=16 align=right>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>stdole</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>TypeLib</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>OLE Automation</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=middle>FALSE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=right>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>{00020430-0000-0000-C000-000000000046}</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>C:\Windows\system32\stdole2.tlb</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=16 align=right>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>Office</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>TypeLib</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>Microsoft Office 12.0 Object Library</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=middle>FALSE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=right>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>C:\Program Files\Common Files\Microsoft Shared\OFFICE12\MSO.DLL</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=16 align=right>5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>ADODB</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>TypeLib</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>Microsoft ActiveX Data Objects 6.0 Library</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=middle>FALSE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=right>6</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=right>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>{B691E011-1797-432E-907A-4D8C69339129}</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>C:\Program Files\Common Files\System\ado\msado15.dll</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=16 align=right>6</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>TypeLib</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=middle>TRUE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=right>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63></TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=16 align=right>7</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>MSForms</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>TypeLib</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>Microsoft Forms 2.0 Object Library</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=middle>FALSE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=right>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>{0D452EE1-E08F-101A-852E-02608C4D0BB4}</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>C:\Windows\system32\FM20.DLL</TD></TR></TBODY></TABLE>
 
Last edited:
Upvote 0
I've come up with this code instead... it will advise by msgbox if mscomct2.ocx is not installed or not registered or if trust centre option is turned off so unable to check. Hopefully someone else can use it. :)

VBAProject - Module 2:
Code:
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
 
Upvote 0
Bugger... just when I thought I had it working on both Excel 2010 & Excel 2007 (Enterprise)... it now works on Excel 2007 but not Excel 2010.

I did some changes in Excel 2007 & ran my macro which worked fine (accessed the DTPicker object).

Now when I run the spreadsheet in Excel 2010, whenever it tries to access the DTPicker object it shows an error:

SOURCE: VBAProject
ERROR# 32809, LINE# 0, HelpContext: 1000095
Application-defined or object-defined error
It fails for either of these lines:
Code:
'dteStartDate = Worksheets("Update").DTPickerStart
 dteStartDate = Format(Worksheets("Update").DTPickerStart, "yyyy-mm-dd")
Any ideas please?

When I click on a DTPicker in design view it shows DTPicker.2 in the fomula bar:
Code:
=EMBED("MSComCtl2.DTPicker.2","")

My VbReferences show this:
<table style="border-collapse: collapse; width: 1069pt;" border="0" cellpadding="0" cellspacing="0" width="1424"><col style="width: 27pt;" width="36"> <col style="width: 59pt;" width="78"> <col style="width: 41pt;" width="55"> <col style="width: 238pt;" width="317"> <col style="width: 48pt;" width="64"> <col style="width: 32pt;" width="43"> <col style="width: 33pt;" width="44"> <col style="width: 215pt;" width="286"> <col style="width: 338pt;" width="450"> <col style="width: 38pt;" width="51"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 27pt;" height="20" width="36">Item</td> <td style="width: 59pt;" width="78">Name</td> <td style="width: 41pt;" width="55">Type</td> <td style="width: 238pt;" width="317">Description</td> <td style="width: 48pt;" width="64">Is Broken</td> <td style="width: 32pt;" width="43">Major</td> <td style="width: 33pt;" width="44">Minor</td> <td style="width: 215pt;" width="286">GUID</td> <td style="width: 338pt;" width="450">Full Path</td> <td style="width: 38pt;" width="51">Built In</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">1</td> <td>VBA</td> <td>TypeLib</td> <td>Visual Basic For Applications</td> <td align="center">FALSE</td> <td align="right">4</td> <td align="right">1</td> <td>{000204EF-0000-0000-C000-000000000046}</td> <td>C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA7\VBE7.DLL</td> <td align="center">TRUE</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">2</td> <td>Excel</td> <td>TypeLib</td> <td>Microsoft Excel 14.0 Object Library</td> <td align="center">FALSE</td> <td align="right">1</td> <td align="right">7</td> <td>{00020813-0000-0000-C000-000000000046}</td> <td>C:\Program Files\Microsoft Office\Office14\EXCEL.EXE</td> <td align="center">TRUE</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">3</td> <td>stdole</td> <td>TypeLib</td> <td>OLE Automation</td> <td align="center">FALSE</td> <td align="right">2</td> <td align="right">0</td> <td>{00020430-0000-0000-C000-000000000046}</td> <td>C:\Windows\system32\stdole2.tlb</td> <td align="center">FALSE</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">4</td> <td>Office</td> <td>TypeLib</td> <td>Microsoft Office 14.0 Object Library</td> <td align="center">FALSE</td> <td align="right">2</td> <td align="right">5</td> <td>{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}</td> <td>C:\Program Files\Common Files\Microsoft Shared\OFFICE14\MSO.DLL</td> <td align="center">FALSE</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">5</td> <td>ADODB</td> <td>TypeLib</td> <td>Microsoft ActiveX Data Objects 6.0 Library</td> <td align="center">FALSE</td> <td align="right">6</td> <td align="right">0</td> <td>{B691E011-1797-432E-907A-4D8C69339129}</td> <td>C:\Program Files\Common Files\System\ado\msado15.dll</td> <td align="center">FALSE</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">6</td> <td>MSComCtl2</td> <td>TypeLib</td> <td>Microsoft Windows Common Controls-2 6.0 (SP6)</td> <td align="center">FALSE</td> <td align="right">2</td> <td align="right">0</td> <td>{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}</td> <td>C:\Windows\system32\MSCOMCT2.OCX</td> <td align="center">FALSE</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">7</td> <td>MSForms</td> <td>TypeLib</td> <td>Microsoft Forms 2.0 Object Library</td> <td align="center">FALSE</td> <td align="right">2</td> <td align="right">0</td> <td>{0D452EE1-E08F-101A-852E-02608C4D0BB4}</td> <td>C:\Windows\system32\FM20.DLL</td> <td align="center">FALSE</td> </tr> </tbody></table>
 
Last edited:
Upvote 0
Need a Date/Time/Calendar Control to work in multi Excel platforms (2003/2007/2010) please...

So... I have several spreadsheets created using Excel 2007 with 'Calendar Control 12.0' controls in them. It wont work in Excel 2010 as that control was removed from Office 2010!!!

Now... I also have a different spreadsheet created in Excel 2010 with 'Microsoft Date and Time Picker 6.0 (SP6)' controls in it. It wont work in Excel 2007 as that control doesn't exist in Office 2007!!!

I want to have a multi-compatible spreadsheet (that at least works with Excel 2007 & 2010 & probably also 2003) with a date/time/calendar control in it but dont really want to have to instruct each person who may use it to install some add-in to their Excel.

What options do I have please?

Further to:
http://social.msdn.microsoft.com/Fo...be5-5d7f-4fb2-ae16-1438e0e736a4?prof=required

http://www.mrexcel.com/forum/showthread.php?t=70813&page=2


I recently upgraded to Excel 2010 and Microsoft Date and Time Picker isn't listed in references. I did some research and am thinking I need to install sp6 for vb6 but that isn't working either (get an error message that says "Not installed successfully" moments after agreeing to the license). Any ideas?
 
Upvote 0
Help please,
I don't have Excel 2010 "Date and Time Picker 6.0" in "more controls" window.
where I can find or download it? :confused:
 
Upvote 0
Code extracts from my post #5 above shows :)

"1) Download mscomct2.cab from http://activex.microsoft.com/controls/vb6/MSCOMCT2.CAB" _
"2) Unzip/extract mscomct2.cab (.ocx & .inf)" _
"3) Copy extracted files to relevant directory as administrator (click continue if prompted for admin permission)" _
" c:\windows\system\ = Windows 95, 98, or ME" _
" c:\WINNT\system32\ = Windows NT or 2000" _
" c:\windows\system32\ = Windows XP or 7" _
" c:\windows\sysWOW64\ = Windows 7 64bit" _
"4) Close & re-open spreadsheet." _
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top