none
Shifting focus to created workbook in VBA RRS feed

  • Question

  • Hi

    We have a macro enabled workbook in which a userform is displayed.
    In the form the user selects some filters and then starts execution of macro code with a button.

    The code creates a new workbook and one (or more) worksheets are copied from the macro workbook to the new workbook.

    When exeution is finished the user form is closed and the new workbook is presented above the macro workbook.

    Now, when we try to use the ribbon meny it appears to be locked. We can select different sheets and cells in the workbook but the ribbon meny is inaccessible.

    If we shift to the macro workbook window and back to the new one, the ribbon menu is accessible again.
    This behaviour is new in Excel 2013. In Excel 2010 this is no issue.

    We have tried activate the windows in code with workbook.Activate, but nothing seems to work.

    Does anybody have an idea in how to get this to work?


    Best Regards Peter Karlström Midrange AB, Sweden

    Friday, June 15, 2018 3:10 PM

All replies

  • Hello Peter Karlström,

    I'm wondering if the issue is related to Excel 2013. Could you reproduce the issue on any other PC with Excel 2013?

    I would suggest you share your code so we could try to use it reproduce your issue. 

    Best Regards,

    Terry


    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.

    Monday, June 18, 2018 2:33 AM
  • Hi Terry

    Thanks for your reply.
    I can reproduce the error on any client with Excel 2013, but not on clients with Excel 2010.

    This is a rather large project, so I will extract the parts responsible for creating the new workbook and finishing the procedure.

    Main routine for creating the new workbook is this:

    Private Function CreateExcelBooks() As Boolean
    
        Dim astr
        Dim iIndex As Integer
        Dim xlbook2 As Workbook
        Dim xlsheet As Worksheet
        Dim ws As Worksheet
        Dim xlsheetTimDeb As Worksheet
        Dim bCreateTimDeb As Boolean
        Dim sStatustext As String
        
        On Error GoTo errorHandler
        sStatustext = "Sammanställer resultat .."
        UserForm1.LabelSatus.Caption = sStatustext
    
        Application.ScreenUpdating = False
        Set xlsheet = xlbook.Worksheets("Verkstadsstatistik")
        Set xlsheetTimDeb = xlbook.Worksheets("Timdebitering")
        Set ws = xlbook.Worksheets("Försida")
        Set xlbook2 = Workbooks.Add
        
        With xlbook2
            .Title = Trim$(UserForm1.TxtRubrik.Text)
            .Subject = CabasRUBRIK
        End With
    
        Application.DisplayAlerts = False
        appNrofSheets = Application.SheetsInNewWorkbook
        If appNrofSheets > 1 Then
            For iIndex = 2 To appNrofSheets
                xlbook2.Worksheets(2).Delete
            Next
        End If
    
        UserForm1.LabelSatus.Caption = UserForm1.LabelSatus.Caption + ".."
        ws.Copy after:=xlbook2.Worksheets(1)
        xlbook2.Worksheets(1).Delete
        bCreateTimDeb = True
    
        Call CreateDataSheets(xlbook2, xlsheet, xlsheetTimDeb, 0, bCreateTimDeb)
        Set xlsheet = xlbook2.Worksheets("Verkstadsstatistik")
        For iIndex = 1 To iAntalSeltyp
            If vstStatKalkylArray(iIndex).lAntalkakyler > 0 Then
                UserForm1.LabelSatus.Caption = UserForm1.LabelSatus.Caption + ".."
                Call CreateDataSheets(xlbook2, xlsheet, xlsheetTimDeb, iIndex)
            End If
        Next
         
    
        Call ProtectWorkBook(Workbooks.Item(1), True)
        Application.ScreenUpdating = False
        Set ws = xlbook2.Worksheets("Försida")
        Call CreateIstPage(ws)
        For Each xlsheet In xlbook2.Worksheets
            xlsheet.Protect ("cabas123")
        Next xlsheet
        Call xlbook2.Protect("cabas123")
        xlbook2.Worksheets("Försida").Visible = xlSheetVisible
        
        Application.ActiveWorkbook.Windows(1).Caption = Trim$(UserForm1.TxtRubrik.Text)
        ThisWorkbook.Saved = True
        CreateExcelBooks = True
        Set xlsheet = Nothing    
        UserForm1.LabelSatus.Caption = "Antal Kalkyler:" & Str$(KalklRows)
       
        
        Application.ScreenUpdating = True
        xlbook2.Activate
    
        End
       
        Exit Function
    
    errorHandler:
            Application.ScreenUpdating = True
            CreateExcelBooks = True
            MsgBox Err.Description, vbCritical, astr
            ThisWorkbook.Saved = True
            UserForm1.Enabled = True
            End
    End Function

    The CreateDataSheets subroutine copies one worksheet from the macro workbook to the new workbook and fills it with data.

    The ProtectWorkbook subroutine sets protection to ON in the macro workbook.

    I have tried a number of different techniques to shift focus between the workbooks in the last part of the code, but nothing seems to work.


    Best Regards Peter Karlström Midrange AB, Sweden

    Tuesday, June 19, 2018 7:23 AM
  • Hello Peter,

    I failed to reproduce your issue since there are too much undefined method in your code. 

    I would suggest you try to simplify your code so we could reproduce your issue from scratch. You may even find which part cause the issue during process of simplifying.

    Or you could try to remove sensitive information and unrelated code and then share the workbook so we could use it to reproduce your issue.

    Best Regards,

    Terry


    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.

    Friday, June 22, 2018 6:22 AM
  • Hi Terry

    OK. I will do that.
    I found a workaround by minimizing the new window.
    When the user clicks it, its all available.


    Best Regards Peter Karlström Midrange AB, Sweden

    Tuesday, June 26, 2018 8:58 AM