Free Microsoft Excel 2013 Quick Reference

MISSING Common Dialog Control 6.0

I have an Excel application created in Excel 2003 that uses a common dialog control.
I used the application on a machine with Excel 2000 and received a compile error, which I traced to "MISSING Common Dialog Control" in
In the tool box - more controls, the common dialog control is not listed.
How do I install the Control and how do I ensure it will work on other machines with Excel 2000 ?


Post your answer or comment

comments powered by Disqus
I am having a problem with using common dialog in a VBA (Excel) application. The macro runs up correctly first time, but if I press Cancel when the common dialog appears, and then run the macro again, an exception code 430 (Class does not support Automation or does not support expected interface) is generated and the program crashes.

I have no idea why the problem is occurring, so would be grateful if anybody can point me in the right direction. As I am reasonably new to VBA, I might be doing something wrong which is obvious to you.

For your reference, please find the example code I have been using for testing below. I believe that you can plug it straight into your VBA project and run it.

Please also note that in my VBA environment, I have included the following references (set by Tools->References). They are not all needed for the example code below, but are required for the application I am writing.

1. Visual Basic for Applications
2. Microsoft Excel 11.0 Object Library
3. OLE Automation
4. Microsoft Office 11.0 Object Library
5. Microsoft Forms 2.0 Object Library
6. Common Dialog Control 6.0 (SP6)
7. Microsoft Scripting Runtime
8. Microsoft Excel 11.0 Object Library

Thank you very much for your help.

Shinsuke

Example code:


	VB:
	
 test() 
    Dim c As New CommonDialog 
    Dim hFile As Integer 
    test_directory = "d:" 
     
    On Error Goto ErrorHandler2 
     
     ' Compute the default filename
    test_file = "test1.html" 
     
    c.InitDir = test_directory ' Program fails here after pressing cancel button
    c.Filename = test_file 
    c.DialogTitle = "Choose the destination file for the upgrade procedures" 
    c.FilterIndex = 2 
    c.Filter = "All files (*.*)|*.*|HTML files|*.html" 
    c.DefaultExt = "html" 
    c.CancelError = True ' If the Cancel button is pressed, generate an exception
    c.Flags = cdlOFNOverwritePrompt Or cdlOFNPathMustExist ' Ask for confirmation to overwrite existing documents, and do not
accept a non-existant directory
    c.ShowSave 
    html_procedure_master_list_filename = c.Filename 
     
    Exit Sub 
     
ErrorHandler2: 
    Select Case Err.Number 
    Case 32755 
        MsgBox "User cancelled operation." 
        End 
    Case Else 
        MsgBox "Exception with error code " & Err.Number 
        End 
    End Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I'm creating an application in Excel. In my Visual Basic sheet, I checked the
box in tools-references for 'Microsoft Common Dialog Control 6.0'. But when I
try to insert a 'Microsoft Common Dialog Control, version 6.0', I get the
message, "Cannot insert object". I'm trying to insert a 'showopen' dialog.
I've tried everything to get the object inserted but without success. Can
someone help?

I am using a book "Excel VBA Macro Programming" to teach myself how to
enhance my finance spreadsheets. There is a project in the book to color
alternating lines on a spreadsheet. Though I already can do this with the
built-in menus, I thought this should be a 'simple' project to teach myself
about forms and ActiveX controls. The book says to use the Common Dialog
Control. I go to Additional Controls, select Microsoft Common Dialog 6.0,
drag it to the form and click. I get a message "The control could not be
created because it is not properly licensed." I tried to 'register' it as
described on this site, but still get the error.

I can do that on a Windows98 with Office 2000 machine. I put a common dialog
control on a worksheet so that it would be popped up when necessary to allow
user to select a workbook to open. However when I load this Excel workbook on
a Windows 2000 Professional with Office 2000 machine, the common dialog
control on the worksheet was missing. Even though I could register the
comdlg32.ocx successfully via regsvr32 program. I still cannot put the
control on a worksheet, get an error something like ..."cannot insert object"

Please help!

Hello - When I run, I get the error "Compile Error: Method or Data Member not found" at the following line of code:

Me.ctlComDlg.Filename = ""

I have the Microsoft Common Dialog Control 6.0 (SP3) reference selected. Am I missing another reference to make this work? I don't think its a code problem because this code previously worked on another computer...but I am currently using a borrowed laptop for the next few weeks while my other one is being fixed.

Thanks for any help.
Chris

Hi there

I wanted to add a calendar to an Excel spreadsheet but I can't seem to find
the Microsoft Date and Time Picker Control 6.0 in the ACTIVE X control in the
Developer Tab,

Where do I find it?

Thank you

Is there a way to auto hide ]Date and time picker control 6.0, so that the calender only appears when a cell is selected.

I would like to have the option to click on a cell say A1 and many other selected cells and a calender then open up (with todays date) and allow me to select another date.

I can get the calender to link to a cell but the calender is always stays visible in full month view, would like it to auto hide when moving away from cell A1 etc.

Many thanks for any help.

I am using a book 'Excel VBA Macro Programming' to learn how to use the VBA
editor so I can automate some functions in my financial spreadsheets. One of
the book projects says to use this Microsoft Common Dialog Control to set up
the interface between the code I write in my module and the user (me) via a
form. The goal of the project is to color alternate lines with the user's
choice. I already know how to do this from the spreadsheet menus--but
thought I would try to learn the coding and form interface stuff with this
'simple' example. So I went to the 'additional controls' section, selected
the Microsoft Common Dialog Control and added it to the Toolbox. When I drag
it over to the form and left-click to enter it, I get a message "The control
could not be created because it is not properly licensed." I tried to
'register' it following the directions on this site but it did not help. I
though perhaps my project requires certificate, but can't figure that out. I
am running Windows 2000, and Office 2000. Any help would be appreciated.
Don't really understand all this stuff. Can you tell?

I need to access the Common Dialog control in a subroutine. I read that I can do this by dragging the Common Dialog control from the toobox onto a userform. When I tried this, I get an error message that says "The control could not be created because it is not properly licenced." I am using Excel 2002 and Windows 2000. Any suggetions?

Hi All,

Excel 2003 w/Visual Basic 6.3

there is no Common Dialog Box control in VB 6.3, when I try to browse it
under folder system32 the files comdlg32.dll and comdlg32.ocx are there, but
I can not add it in the reference. The error messege is "can't add a
reference to the specified file". Can someone help me?

I have a pop-up calendar in a workbook but when I try to activate it, it errors. In the Tools - References it states the following:
Missing: Microsoft Windows Common Controls-3 6.0 and it refers to
comct332.ocx in Windows-System32.
I checked in the System32 folder and there I have comct232.ocx but not comct332.ocx.
Searching for this on the windows site references to a VB6Cli.exe program as follows: VB6Cli.exe is a utility that fixes design-time licenses used by ActiveX controls installed with Microsoft Visual Basic 6.0.
I have searched the Microsoft site to download either files (VB6Cli.exe and comct332.ocx) but no luck.
I have Microsoft Visual Basic 6.3 and WinXP Professional.
Any help on this matter is greatly appreciated
Thanks and regards.
John

I have windows XP and Excel 2003. In Visual Basic Editor, References, I have missing the Microsoft Windows Common Controls-2 6.0. How can I enable it?
When opening the file, excel prompts Error compiling some modules. Opening the file in another pc with this common controls enabled, it works fine.

Thanks a lot fot the help.

as my title says I have just got a dell xps with windows 64bit and installed office 2010 64bit. Now my VBA macros in excel willnot work.
I have regestered MSCOMCT2.OCX for 32bit and 64bit without any problem but in vba toolbox Microsoft Common Controls-2 6.0 is not available.
I have searched the internet and tried all suggestions without success. Cna anybody please help me,

Cheers

Clive.

I am using XP 2003 I notice that in the VBA toolbox when you right click in
it to select additional controls there are some missing that my office
computer has. Specifically -Microsoft Date & Time Picker Controls 6.0 (SP4).

Is there some placing I'm not looking to add it, maybe a download,etc.?
Thank you for your help.
--
Though daily learning, I LOVE EXCEL!
Jennifer

Hi

I have problem. I want to use microsoft windows common controls 6.0 (sp6) activex controls in excel 2002. it is not available in the toolbox list. i tried using register custom control option in toolbox to register this ocx file. i did not
get any error msg. but when i go again and see the list
it is not listed there.

can anybody help ??? :

thanx:

Venkat

Hi all,

Creating reports in Excel is a common task and also to retrieve data from databases.

For several reason we may decide to create a standalone reporttool in VB instead of doing it from inside Excel.

When setting up reports with the Pivottable and particular if we want to have the option to update the table ( i e retrieve updated data from the source) we must be aware of that there exist certain limitations to use a classic ADO-approach and therefore are (still) forced to use the ODBC-approach.

Please see Populate pivottables using ADO

In the example early binding is used and therefore it require that we set references to the following library:

* Microsoft Excel 9.0 or later

What we need:
* Microsoft Visual Basic 6.0
* Microsoft Excel 2000 and later
* The example database Northwind.mdb


	VB:
	
 
 
Dim xlApp As Excel.Application 
Dim xlWbook As Excel.Workbook 
Dim xlWSheet As Excel.Worksheet 
Dim xlptCache As Excel.PivotCache 
Dim xlptTable As Excel.PivotTable 
 
Const stCon As String = "ODBC;DSN=MS Access Database;" & _ 
"DBQ=C:Northwind.mdb;DefaultDir=C:;" & _ 
"DriverId=25;FIL=MS Access;" & _ 
"MaxBufferSize=2048;PageTimeout=5;" 
 
 
Const stSQL As String = "SELECT ShipCountry, " & _ 
"COUNT(Freight) AS [# Of Shipments], " & _ 
"SUM(Freight) AS [Total Freight] " & _ 
"FROM Orders " & _ 
"GROUP BY ShipCountry;" 
 
 
Public Function Create_PivotTable_Report(ByVal stFilename As String) 
    Dim bStarted As Boolean 
     
     'If a session of Excel is already running then grab it.
    On Error Resume Next 
    Set xlApp = GetObject(, "Excel.Application") 
    On Error Goto 0 
     
     'Otherwise instantiate a new instance.
     'Keep in mind that no active Add-ins will be available unless they are
     'of the type COM.
    If xlApp Is Nothing Then 
         'Set the flag to remember who have started the session.
        bStarted = True 
        Set xlApp = New Excel.Application 
    End If 
     
     'Create a new workbook with one worksheet only.
    Set xlWbook = xlApp.Workbooks.Add(xlWBATWorksheet) 
     
    With xlWbook 
        Set xlWSheet = .Worksheets(1) 
        Set xlptCache = .PivotCaches.Add(SourceType:=xlExternal) 
    End With 
     
     'Populate the Pivotcache.
    With xlptCache 
        .Connection = stCon 
        .CommandText = stSQL 
        .CommandType = xlCmdSql 
    End With 
     
     'Create the Pivottable.
    Set xlptTable = xlWSheet.PivotTables.Add( _ 
    PivotCache:=xlptCache, _ 
    TableDestination:=xlWSheet.Range("D4"), _ 
    TableName:="PT_Report") 
     
     'Setup the pivottable.
    With xlptTable 
        .ManualUpdate = True 
        .PivotFields("ShipCountry").Orientation = xlRowField 
        .PivotFields("# Of Shipments").Orientation = xlDataField 
        .PivotFields("Total Freight").Orientation = xlDataField 
        .Format xlTable2 
        .ManualUpdate = False 
        .ManualUpdate = True 
    End With 
     
     'Save the created workbook.
    xlWbook.SaveAs stFilename 
     
     'If this procedure have started Excel then the UserControl of Excel must be
     'set to True and we need to make Excel visible.
    If bStarted Then 
        With xlApp 
            .Visible = True 
            .UserControl = True 
        End With 
    End If 
     
     'Switch to Excel.
    AppActivate (xlApp) 
     
     'Release the objects from memory.
    Set xlptTable = Nothing 
    Set xlptCache = Nothing 
    Set xlWSheet = Nothing 
    Set xlWbook = Nothing 
    Set xlApp = Nothing 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


A quite common question on different forums is how we can automate Excel from Visual Basic.

The question is more specific how to get it right.

In general we should consider the following rules:

Always use variables whenever You can and also use Excel's object-structure to a maximum.Use the built-in constants for objects methods/properties where it's suitable.Implement a complete error-handling.
For those of You who would like to have constants in a table please see Ivan F Moala's nice site:
Constants

One major drawback when automating Excel is that all add-ins that are installed and activated are not available (which explain the fast load of Excel.exe).

If we need access to a specific add-in we need therefore to activate it before we can use it.

The example code below shows how to do it for add-ins with the file-extension 'xla'.

If You intend to use XLLs (DLLs like MoreFunc et al) then the following KB-article give You the required solution:
Add-Ins Don't Load When Using CreateObject

Developing plattform:
Windows 2000 SP-4
MS Visual Basic Enterprise 6.0 SP-6
MS Excel 2003


	VB:
	
 
 
Sub Add_Data_Workbook() 
    Dim xlApp As Excel.Application 
    Dim xlwbBook As Excel.Workbook, xlwbAddin As Excel.Workbook 
    Dim xlwsSheet As Excel.Worksheet 
    Dim rnData As Excel.Range 
    Dim stFile As String, stAddin As String 
     
    stFile = "c:Test.xls" 
    stAddin = "E:ArbetsmaterialTimeConverter.xla" 
     
     'Instantiate a new session with the COM-Object Excel.exe
    Set xlApp = New Excel.Application 
     
    Set xlwbBook = xlApp.Workbooks.Open(stFile) 
    Set xlwsSheet = xlwbBook.Worksheets("Sheet1") 
     
     'We can either open the add-in as a standardworkbook
     'like the following. The drawback is that any Workbook_Open
     'or Auto_Open will not be executed or
     'Set xlwbAddin = xlApp.Workbooks.Open(stAddin)
     
     'we can activate the add-in like the following.
    xlApp.AddIns.Add(stAddin, True).Installed = True 
     
    With xlwsSheet 
         'Instead of using the name of method for the range-object etc
         'the prefered approach is to use constants.
        Set rnData = .Range(.Range("A1"), .Range("A65536").End(xlUp)) 
    End With 
     
     'Select the range we will manipulate with the add-in.
    rnData.Select 
     
     'Execute the procedure inside the add-in.
    xlApp.Run "TimeConverter.xla!Time_Converting" 
     
     'Save & Close the workbook.
    With xlwbBook 
        .Save 
        .Close 
    End With 
     
     'If we have opened the add-in as a standardworkbook we need to close it.
     'xlwbAddin.Close
     
     'Close Excel.
    xlApp.Quit 
     
     'Release the COM-objects from memory.
    Set rnData = Nothing 
    Set xlwsSheet = Nothing 
    Set xlwbBook = Nothing 
    Set xlApp = Nothing 
     
     'Feedback to the user.
    MsgBox "Done!", vbInformation 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Please feel free to comment this post in the Excel/VBA-section.

Kind regards,
Dennis

Hi,

I am desperate with DTPicker: it used to work, I have several worksheets that use it, but all of a sudden it has stopped working for me.

When I insert the control on a blank worksheet (Developer, Insert, More Controls, Microsoft Date and Time Picker Controls 6.0 SP6), I get a message with "Cannot Insert Object".

Similarly, when I try to place it on a Userform, I receive an "Invalid argument".

The Activex control seems to be well registered: there is a "Microsoft Windows Common Controls-2 6.0 (SP6)" on the list of References and it is checked, location is c:windowssystem32MSCOMCT2.OCX

I have re-registered the control (regsvr32 c:windowssystem32MSCOMCT2.OCX) but nothing changes. I even got a different version of the control (SP4 instead of SP6) and tried registering, nothing changes.

I'm using Excel 2007 and Vista.

What else could I try?

thanks!

Hi there

I've added a user control in Excel2007. Within this control is a MS TreeView Control 6.0.
During the user control intialization, nodes are added to the treeview:

Me.TreeView_Audituniverse.Nodes.Clear

Me.TreeView_Audituniverse.Nodes.Add Key:="Key1", Text:="Key1"
Me.TreeView_Audituniverse.Nodes.Add relative:="Key1", relationship:=tvwChild, Key:="Key2",
Text:="Key2"
Me.TreeView_Audituniverse.Nodes.Add relative:="Key2", relationship:=tvwChild, Key:="Key3",
Text:="Key3"
Me.TreeView_Audituniverse.Nodes.Add relative:="Key2", relationship:=tvwChild, Key:="Key4",
Text:="Key4"
I expected to display to be like this:
Key1-Key2-Key3
-Key4But when I try it, no hierarchy is displayed and the control looks like:
Key4
Key3
Key2
Key1

Does anybody has an idea what I'm missing???

Thanks, Pi

Does anybody know if it is possible to change the background colour of
the "date box" of a Microsoft Date and Time Picker 6.0 COLOUR - by
"date box" I mean the combobox type object that you see when you this
control does not have the focus

(I know how to change the background of the dropdown calender, it's
just the box that returns the date selected, that I'm having fun with)

Any help greatly appreciated.
Jason

Greetings!

I am trying to create a form in excel that will allow users to input data accurately and quickly. In column labeled "Date" (Column A) I would like to use the Calendar Control 11.0 control to allow users to pick dates quickly.

For a brief second, i got it to work correctly, and now I cannot get it to work at all.

All I did originally, was to go into VBA and insert a user form. I picked the Calender Control 11.0 and exited VBA. Then, when I went to my spreadsheet in any cell with a date in or above it or with the word "Date" above it, it would have a small icon to the right that I could click and a calendar would pop out and allow me to pick a date for that cell.

However, this went away after I saved it and now I cannot get it to work in any spreadsheet that I create. What am I missing. How does this control work?

I have developed an Excel application with embedded VB code. This has run OK
in all environments from Office 2000 on W98 up to Office 2003 on WXP SP2. I
do not have VB as a separate development environment so all the code has been
developed using the embedded VB editor.

I have a German user with Office 2003 (SP2), but the version of VB appears
to be 6.0, not 6.3 as on other systems so am wondering if this is where the
following problem lies.

Up to now I have found that variables declared as Public in a module are
available to private sub code within worksheets and if set in a private sub
are available to a public sub called from the private sub. But in the VB6.0
environment above, a variable set in a private sub is not passed to a public
sub in a module.

If I include a Public declaration of the variable in both the worksheet code
and the module containing the public sub, VB 6.3 assumes there are two
independent variables instead of one common one, so that won't work. I
realise that I can pass variables when calling modules, but that will mean
changing a lot of code.

Q1: Is there a way to share variables between private and public subs within
a project which will work with VB 6.0 as well as 6.3?
Q2: Is there an upgrade to VB 6.3 which can be applied to the user's
environment?

Hello,

I have created a marco-enabled workbook in 2007 but we moved to 2010. I used the Calendar Control 12.0 in 2007, however we do not have this control installed in 2010. How do I install this control?

Any help is appreciated.

Thanks.

Gents

I'm using calendar control 11.0 and have noticed a small issue which I guess is a perrenial.

If I select a date first then change the month the calendar value does not reflect the change immediately and maintains the original date and month.

How can I force an immediate update to the calendar value on change of month?

Regards

Simpo


No luck finding an answer? You could always try Google.