none
VB 2010 on Windows 10 and Excel 2016 RRS feed

  • Question

  • I have a Microsoft Visual Basic 2010 app that runs on Win 7 box and updates a template Excel 2010 spreadsheet. It works flawlessly. Now transitioning to Windows 10 Excel 2016. During the update of the template spreadsheet Visual Basic 2010 has a problem when running a Worksheet_Activate event that is part of the template spreadsheet that event selects an ActiveX button and makes sure that the button remains in the correct spot.

    Private Sub Worksheet_Activate()
        With ActiveSheet.Shapes("cmdRecon")
            .Top = 45
            .Height = 35
            .Width = 135
            .Left = 1014.75
        End With
        Range("A9").Select
    End Sub

    I assume Visual Basic 2010 hands the process off to VBA in the spreadsheet and the following error occurs.

    Run-time error '-2147024809 (80070057)'
    The item with the specified name wasn't found.

    If I click debug, the "With ActiveSheet.Shapes("cmdRecon")" is highlighted. If I press F5, the process runs and the rest of the Visual Studio app runs and finishes updating the spreadsheet (with the VBIDE visible), once the Visual Basic 2010 app concludes. I click on the dia;og box telling me tha process has completed and the vbide closes and the Visual Basic app ends.

    Does anyone know what might be causing this?
    • Moved by Neda Zhang Friday, October 21, 2016 2:35 AM related to Excel
    Thursday, October 20, 2016 6:38 PM

All replies

  • It sounds to me like the ActiveSheet is not the one you expect when the code executes. It could be a timing issue, but I don't see enough in your code to know for certain. You could either use the Select method on that Worksheet before running the code or reference the Worksheet object directly instead of relying on ActiveSheet.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, October 20, 2016 6:56 PM
  • What has me baffled is that this code runs without a hitch while updating the spreadsheet using win7 and excel 2010. The other thing is that the code runs fine when working only with the spreadsheet in Excel 2016.

    I thought that I had tested selecting first like this

        ActiveSheet.Shapes("cmdRecon").Select
        
        With Selection
            .Top = 45
            .Height = 35
            .Width = 135
            .Left = 1014.75
        End With
    

    but that didn't appear to help. I really need to do more testing, but the sheet is activated more than once and I'm not sure yet which "activate" trips up the process. (perhaps it is the first instance which might explain why it doesn't re-occur as the VBA ide is visble after the problem occurs) I'll do more testing tomorrow and let you know what I discover.

    Thanks for your input, I work in isolation so have no one off of whom I can bounce things.

    Thursday, October 20, 2016 7:17 PM
  • Hi Jwtucher,

    Since your issue seems more related with the product feature of Excel, I will move this thread to the more related forum for better support.

    Reference: https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    Thanks for your understanding and support.

    Best Regards,

    Neda Zhang


    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.

    Friday, October 21, 2016 2:34 AM
  • Hi,

    Sorry for the delay. Do you resolve your issue?

    If the issue has been resolved, we would appreciate if you could share the solution with us, so that this thread would help others who have similar issue.

    If not, does the error occur when testing in VBA?

    What is your VB project and how do you automate Excel?

    Since we failed to reproduce, if possible, I suggest you share your code with us for testing.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 27, 2016 7:37 AM
    Moderator
  • No the issue was not resolved.

    The issue does not occur when using the spreadsheet.

    Whenever that sheet is selected it does what it is supposed to do. Makes sure the button remains in the correct place.

    In the Visual studio VB code I select the spreadsheet to insert information on the sheet. I assume that the Private Sub Worksheet_Activate() event is triggered when I change to that sheet. I introduced a delay as it was suggested by Paul P Clement IV the code is as follows:

    Private Sub Worksheet_Activate()
        Delay 1
        ActiveSheet.Shapes("cmdRecon").Select
        With Selection
            .Top = 45
            .Height = 35
            .Width = 140
            .Left = 1015
        End With
        Range("A9").Select
    End Sub
    
    Function Delay(ByVal T As Integer)
        Dim strT
        Dim strSecsDelay
            strT = Mid((100 + T), 2, 2)
                strSecsDelay = "00:00:" & strT
        Application.Wait (Now + TimeValue(strSecsDelay))
    End Function

     I also tried directly selecting like this

    Private Sub Worksheet_Activate()
        Delay 1
        With ActiveSheet.Shapes("cmdRecon")
            .Top = 45
            .Height = 35
            .Width = 140
            .Left = 1015
        End With
        Range("A9").Select
    End Sub

    The problem still occurs. The Visual Studio vb code that activates the worksheet is:

            'Set starting Variables
            iRowTop = 10
            iRow = iRowTop
            kayRow = 29
            kayCol = 2
            strProd1 = "AB"
            pdsRow = 18
            pdsCol = 2
            iCount = 0
            x = 0
            'Get Number of Products
            oShtRecon = CType(objSheets2("Reconciliation"), _Worksheet)
            oShtRecon.Activate() 'Reconciliation Tab in Docket workbook
    
            With oShtRecon
                .Cells(1, 4).value = intProdTypes 'Get number of product types
            End With
    
            Dim doneIt_FR As Boolean = False
            Dim doneIt_MI As Boolean = False
            Dim doneIt_DL As Boolean = False
    
            'Do Until intProdTypes = iCount
            Do While x < intProdTypes
                'Do While iCount < UBound(P) + 1 ' iCount 0-5 six elements 
                '===============================================================================
                iSkids = Integer.Parse(TS(x))
    
                oShtRecon.Activate() 'Reconciliation Tab in Docket workbook
    
                With oShtRecon
                    strProd1 = .Cells(iRow, 2).Value
                    Dim LotNumTemp As String
                    LotNumTemp = .Cells(iRow, 6).VALUE
                    Dim LotExpiryTemp As String
                    LotExpiryTemp = .Cells(iRow, 7).VALUE

    Don't know what to do now...

    Thanks,

    John




    Tuesday, November 1, 2016 2:25 PM
  • Hi,

    According to your description, my steps to reproduce your issue:

    1)create a .xltm file

    2)add two sheets: sheets("test"), sheets("Reconciliation")

    3)in sheets("Reconciliation"), add a command button named cmdRecon

    4)for sheets("Reconciliation"), add Worksheet_Activate() event to change button location

    5)Use the code below to automate Excel

            Dim oXL As Excel.Application

            Dim oWB As Excel.Workbook

            Dim oSheets As Excel.Sheets

            Dim oSheet As Excel._Worksheet

            oXL = CreateObject("Excel.Application")

            oXL.Visible = True

            oWB = oXL.Workbooks.Open("C:\Users\celeste\Documents\Custom Office Templates\Book1.xltm")

            oSheets = oWB.Sheets

            oSheet = CType(oSheets("Reconciliation"), Excel._Worksheet)

            oSheet.Activate()

    Result: the command button could be moved into the correct location

    Are there any improper steps above?

    Since your application works fine in Office2010, what is your specific version now?

    I am using Office 16.0.6965.2092/ Win10.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 3, 2016 4:00 PM
    Moderator