none
Excel 2007 files with VBA (Vista) does not work in Excel 2007 (Windows 7) or Excel 2010 (Windows 7) RRS feed

  • Question

  • I have developed a large number of Excel macros during many years for a customer who used Office 2007 and Windows XP.

    I did most of the development in Office 2007 and Vista (32-bit).

    Now the customer has moved to Windows 7 (64-bit), but is still using Office 2007.

    Everything has worked fine up to December 2014.

    After that no command buttons work anymore. Same with a number of other controls.

    If I open a file in Excel 2010 and Windows 7 (32-bit or 64-bit) and start to make a change in to code, Excel does not recognize classes, like Range("A1").Locked and others. If I check references Excel tells no problem. If I debug Excel tells no problem. If I run the code Excel crashes so deep that I have to kill the Excel program.

    If I try to delete a command button Excel crashes.

    I am now in the situation where I think I have to rebuild all these Excel files with lots of code and lots of user interface with buttons, etc. 

    What has happend?
    I must have missed some important information from Microsoft?
    I know the support for older versions run out, but I guess there must be a way to move from an older version to a newer. At least some information how to do this?

    Anyone who can help me understand this?

     

    Hans Hansson

    Wednesday, February 18, 2015 12:11 PM

All replies

  • Hello Hans,

    Did you install the latest updates and service packs for Office 2007/2010?

    Anyway, try to add logging mechanisms so you can be aware what happens in the code. What line of code causes the issue?

    Wednesday, February 18, 2015 1:19 PM
  • Hello Eugene,

    It is no rocket science. I use the change event to do a couple of things.
    It stop on the row:

        Worksheets("Kalkyl").Range("B17").Locked = False

    with error message: "Runtime error '1004'. Property Locked cannot be used for class Range." (Excuse the translation).

    Here is the code:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Worksheets("Kalkyl").Unprotect
        If Range("E19").Value < 0 Or Range("E21").Value < 0 Then
        With Range("D5:E24").Interior
            .ColorIndex = 45
            .Pattern = xlSolid
            End With
        Else
            With Range("D5:E24").Interior
            .ColorIndex = 35
            .Pattern = xlSolid
            End With
        End If
        If Worksheets("Kalkyl").Range("A17").Value = "Nej" Then
            Worksheets("Kalkyl").Range("B17").Locked = False
            If Worksheets("Kalkyl").Range("B17").Value = Null Then
                'Gör inget.
            Else
                Worksheets("Kalkyl").Range("B17").ClearContents
            End If
            Worksheets("Kalkyl").Range("B17").Locked = True
            Worksheets("Kalkyl").Range("B17").Interior.ColorIndex = 37 'Blå.
        Else
            Worksheets("Kalkyl").Range("B17").Locked = False
            Worksheets("Kalkyl").Range("B17").Interior.ColorIndex = 36 'Gul.
        End If
        Worksheets("Kalkyl").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End Sub

    ***************

    I have references to following class libraries:

    Visual Basic For Applications
    Microsoft Excel 14.0 Object Library
    OLE Automation
    Microsoft Forms 2.0 Object Library
    Microsoft Office 14.0 Object Library

    I had an Windows update from Microsoft today and last Office update was 6 days ago. So I think my installation is up to date?
    But I have the same problem on other computers both at my customer and my customers customer. So it is not isolated to my environment.

    The compiler says everything is alright, but when I run the code it is not.

    After the code has stopped, I click on Break, but Excel is absolutely frozen. I have to kill Excel to get out of the lock.

    The problem came with the Windows update in December. What changes were in that update?


    Hans Hansson

    Wednesday, February 18, 2015 2:28 PM
  • Hi Hans,

    Are you using form controls or active control in the solution? If yes, it may be caused by the update state in the blog below:
    Form Controls stop working after December 2014 Updates

    Please let us know whether it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, February 19, 2015 2:13 AM
    Moderator
  • Hi Fei,

    That seems to be an explanation of my problem.

    The solution described in the blogg is defenitly not a solution in my case. These Excel files are used by a lot of people at my customer site and they offer these files as a service to their customers. It is impossible to handle all these Office installations at different company sites all over the contry. Most of the users are not adminstrators on their computers, so that makes it even more difficult.

    How can it be possible that a change is made in such vital basic class libraries without any warning and an intruction to how to handle this in a way that works? 
    If it was a misstake, then fix that! Do not put the head in the sand! Please!

    Best Wishes,
    Hans


    Hans Hansson

    Thursday, February 19, 2015 9:08 AM
  • Hi Hans,

    I recommend that you firstly test on one of the machines. Try to uninstall that update then check if the macro works fine. Make sure you find the root cause of the problem.

    If it works fine after uninstalling the update, then you can try the fix in this article:

    "Cannot insert object" error in an ActiveX custom Office solution after you install the MS14-082 security update


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, February 24, 2015 10:03 AM
    Moderator
  • Hi,

    >>It is impossible to handle all these Office installations at different company sites all over the contry. Most of the users are not adminstrators on their computers, so that makes it even more difficult.<<

    The KB in this blog provide Fix Me to automate to fix this issue. The users only need to click the figure on the KB below:
    http://support.microsoft.com/kb/3025036/EN-US

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, March 2, 2015 12:17 PM
    Moderator
  • OK. I will try that and come back with result.

    /Hans


    Hans Hansson

    Tuesday, March 3, 2015 9:22 AM