Workbook.Open mystery

nigelandrewfoster

Well-known Member
Joined
May 27, 2009
Messages
747
Hello there - strange one this. If I run the code below using a button on a userform it executes perfectly. When I attempt to fire it using a keyboard shortcut it stops executing after the Workbook.Open line (highlighted). Any idea why? And how I can solve this?

Many thanks

VBA Code:
Sub Edit_Orders()
  
    Dim wbOrders As Workbook
    Dim rngLastRow As Range
    Dim avarOrders() As Variant
    Dim strNotes As String
    Dim lngGatherRow As Long
    Dim lngDataRows As Long
    Dim i As Long
    Dim j As Long
    
    [B]Set wbOrders = OpenWorkbook(MyDocumentsPath & "+MAIN WORKBOOKS+\", "+Orders+.xlsx")[/B]
    
    If Not wbOrders Is Nothing Then
        
        Application.ScreenUpdating = False
  
        With ThisWorkbook.Worksheets("Clipboard")
            avarOrders = .Range(.[b2], .[b1048576].End(xlUp)).Offset(, -1).Resize(, 25).Value
        End With
        
        i = 1
        lngDataRows = UBound(avarOrders)
        
        Do While i <= lngDataRows
            If IsEmpty(avarOrders(i, 13)) Then
                j = 1
                lngGatherRow = i - 1
                strNotes = avarOrders(lngGatherRow, 16)
                Do While IsEmpty(avarOrders(i, 13))
                    For j = 1 To 24
                        If Not IsEmpty(avarOrders(i, j)) Then
                            If Not IsError(avarOrders(i, j)) Then strNotes = strNotes & " " & avarOrders(i, j) ' Gather split notes together
                            avarOrders(i, j) = Empty
                        End If
                    Next
                    i = i + 1
                Loop
                avarOrders(lngGatherRow, 16) = strNotes
            End If
            If strNotes <> "" Then
                strNotes = ""
            Else
                i = i + 1
            End If
        Loop
        
        With wbOrders.Worksheets("Orders")
            .Cells.ClearContents
            .[a2].Resize((lngDataRows), 25).Value = avarOrders
            ThisWorkbook.Worksheets("Clipboard").[a1:y1].Copy .[a1]
            .[a1].EntireColumn.Delete Shift:=xlLeft
            .[a1:x1].Resize(lngDataRows + 1).Sort Key1:=.[b1], Order1:=xlAscending, Header:=xlYes
            Set rngLastRow = .[a1048576].End(xlUp).EntireRow
            rngLastRow.Resize(1048576 - rngLastRow.Row).Offset(1).Delete
        End With
        
        Application.ScreenUpdating = True

    End If
 
End Sub
 
Does your keyboard shortcut involve the Shift key? If so, it's a known bug.
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Does your keyboard shortcut involve the Shift key? If so, it's a known bug.
?
Never heard about that... would you mind to elaborate on this? What is the known bug and how does it effect vba-execution?
 
Upvote 0
Thanks for that, it all works for me. Do you get any error messages?
None at all. Code execution makes it as far as the OpenWorkbook line, the workbook gets opened but the rest of the procedure does not get run - IF Edit_Orders is run via a keypress rather than executed via a button on a userform or directly via the code editor...
 
Upvote 0
This is the answer: No shift with workbook.open

Weird, but after some thought its quite clear. Opening a file normally whilst pressing "shift" prevents macros from being executed...
 
Upvote 0
Solution
This is the answer: No shift with workbook.open

Weird, but after some thought its quite clear. Opening a file normally whilst pressing "shift" prevents macros from being executed...
Yes, I guess so. Sort of makes sense I suppose. Not a problem, I'll just change the key combination. Wanted to get to the bottom of it first though. Thanks everyone for your contributions!
 
Upvote 0
You're welcome. And besides contributing my (misleading) thoughts I also learned about a bug. Win - Win situation, I guess.
:)
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top