Macro missing Microsoft Windows Common Controls-2 6.0 (sp4) Excel 365

[lapot]

Hi

I have an excel macro which is running perfectly on Microsoft Office Standard 2016 but when our IT department updated Excel to Office 365 then my macro started giving "Missing: Microsoft Windows Common Controls-2 6.0 (sp4)" error,

This is the line causing error

TempFile = Environ("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

What is the best way to fix the bellow full code code and stop getting this error on Office 365? My IT wouldn't add "Windows Common Controls-2 6.0" on to system.

Here is the full code

Function RangetoHTML(rng As Range)
   ' Changed by Ron de Bruin 28-Oct-2006
   ' Working in Office 2000-2013
   Dim fso          As Object
   Dim ts           As Object
   Dim TempFile     As String
   Dim TempWB       As Workbook

   TempFile = Environ("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
   'Copy the range and create a new workbook to past the data in
   rng.Copy
   Set TempWB = Workbooks.Add(1)
   With TempWB.Sheets(1)
      .Cells(1).PasteSpecial Paste:=8
      .Cells(1).PasteSpecial xlPasteValues, , False, False
      .Cells(1).PasteSpecial xlPasteFormats, , False, False
      .Cells(1).Select
      Application.CutCopyMode = False
      On Error Resume Next
      .DrawingObjects.Visible = True
      .DrawingObjects.Delete

      On Error GoTo 0
   End With

   'Publish the sheet to a htm file
   With TempWB.PublishObjects.Add( _
        SourceType:=xlSourceRange, _
        Filename:=TempFile, _
        Sheet:=TempWB.Sheets(1).Name, _
        Source:=TempWB.Sheets(1).UsedRange.Address, _
        HtmlType:=xlHtmlStatic)
      .Publish (True)
   End With

 

[Dan_W]

So it seems that MSCOMCT2.OCX is the file that provides the DatePicker Control, the MonthView Control, etc. That being the case, I have a sneaking suspicion that when both you @amsaini15 and @lapot upgraded your Excel, you both went from 32bit Office to 64bit Office - can you both please confirm that this is the case by clicking on the About Excel button, as set out on this page: About Office: What version of Office am I using?

It will look like:

and at the end it will say either 32bit or 64bit.

If indeed you are now using 64bit Office, then as RoryA explains above, you will need to remove the specific controls from your Userforms/Worksheets(?) that need this file in order to work. This tends to be the DatePicker Control and the MonthView control, because Microsoft decided not to provide users with a 64bit version of the file.... for [Insert Official Reason Here]. Once you've deleted those specific controls (there is also a certain UpDown control, and animation control and a flat scrollbar control, I think), you should then be able to unselect the MISSING reference.

To be clear, you wouldn't need to delete ALL the controls on your userform - just these select ones. Also, deleting the control from the userform does not automatically delete the related code - it remains in the Userform module... just hanging out... nothing much to do without a control.

Also, you should always make backups of your files before doing any of the above.