Weird OneDrive bug?

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
So i have the xlsm file that is beefy with VBA. Some of these VBA codes take a considerable amount of time to finish performing their task to the 10,000+ lines of data i have. I've noticed this very non consequential bug that happens. I just want to note that i DON'T HAVE ANY ONEDRIVE CODES. I don't interact with one drive at all. its a program i don't use, reference, call, or even have named in my codes. But if i have my file explorer open or sometimes when im in an internet tab while my subs are running it opens multiple OneDrive file explorer windows? its so bizzare. It does not do anything to mess up my data or anything, but its just......why? Why are you so weird Excel? I'll try to screenshot when it happens next.

Any ideas as to why this happens?

Windows 10, Excel 2016 - Office 2016
 
Last edited:

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Is the problem workbook linked to any other files?
 

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
Is the problem workbook linked to any other files?

the only outside references are not being called/used during the bug
there are 0 formulas/links in the sheets being accessed by code


here are the codes that are being used:
I start by calling this macro by double click

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
...'
If Target.Address = "$A$3" Then

   MsgBox "Updating eBay listings into the Master Sheet"
   Sheets("ID check").Select
   Call autoID
   Sheets("Program Start").Select
...'
End Sub
End If


it re-organizes the active sheet (which is ID Check) and runs a vlookup to all 6 of my data sheets (which are pasted as value so there are no formulas or links)
The only formulas used are the ones generated by this sub
and then sorts. Then another macro is called.

Code:
Sub autoID()
    
    
    Dim lastRow As Long
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    
    On Error Resume Next
    With ws
        .Columns("B").SpecialCells(xlBlanks).EntireRow.Delete
        .Columns("B:B").Cut
        .Columns("A:A").Insert Shift:=xlToRight
        .Columns("B:B").NumberFormat = "00000"
        .Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        .Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="^", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
        .Range("D2").FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC1,TTW!R1:R1048576,18,FALSE),IFERROR(VLOOKUP(RC1,MMW!R1:R1048576,18,FALSE),IFERROR(VLOOKUP(RC1,TTH!R1:R1048576,18,FALSE),IFERROR(VLOOKUP(RC1,WP!R1:R1048576,18,FALSE),IFERROR(VLOOKUP(RC1,w1!R1:R1048576,18,FALSE),IFERROR(VLOOKUP(RC1,RLP!R1:R1048576,18,FALSE),""err""))))))"
        lastRow = Range("A" & Rows.Count).End(xlUp).Row
        .Range("D2").AutoFill Destination:=Range("D2:D" & lastRow)
        .Range("E2").FormulaR1C1 = _
        "=IF(AND(RC[-3]<>"""",RC[-1]>=8,RC[-1]<>""err""),1,IF(AND(RC[-3]="""",RC[-1]>=8,RC[-1]<>""err""),4,""err""))"
        lastRow = Range("A" & Rows.Count).End(xlUp).Row
        .Range("E2").AutoFill Destination:=Range("E2:E" & lastRow)
        .Range("D:E").Copy
        .Range("D:E").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        .Range("A1").Value = "P#"
        .Range("B1").Value = "Excess"
        .Range("C1").Value = "Item ID"
        .Range("D1").Value = "QTY"
        .Range("E1").Value = "New QTY"
    End With
    
    
    
    
    Cells.Select
    ActiveWorkbook.Worksheets("ID check").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("ID check").Sort.SortFields.Add Key:=Range( _
    "C2:C" & lastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortNormal
    With ActiveWorkbook.Worksheets("ID check").Sort
        .SetRange Range("A1:XF" & lastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Call IDtoStock
    
    Sheets("Program Start").Select
    
End Sub

IDtoStock
this determines if a product is discontinued, in stock, or out of stock & writes the item ID to either the out of stock sheet or the quantity update sheet
there are no references to outside workbooks, formulas, etc in this either

Code:
Sub IDtoStock()
    Dim qtyROWmax    As Long
    Dim oosROWmax    As Long
    Dim idROWmax   As Long
    Dim i              As Long
    Dim qtyID        As String
    Dim oosID        As String
    Dim idID        As String
    Dim oosSHEET       As Worksheet
    Dim idSHEET       As Worksheet
    Dim qtySHEET       As Worksheet
    
    Set idSHEET = ActiveSheet
    
    idROWmax = idSHEET.Cells(Rows.Count, 1).End(xlUp).Row
    Set oosSHEET = Sheets("OOS")
    Set qtySHEET = Sheets("QTY")
    oosROWmax = oosSHEET.Cells(Rows.Count, 1).End(xlUp).Row
    qtyROWmax = qtySHEET.Cells(Rows.Count, 1).End(xlUp).Row
    
    
    For i = 2 To idROWmax
        
'Perform if/then
        If (idSHEET.Range("E" & i) = "err" _
        Or idSHEET.Range("E" & i) < 1) Then
        
'Write a row to TTH Result Table
'Increment Result sheet row
        oosROWmax = oosROWmax + 1
        idSHEET.Range("C" & i).Copy oosSHEET.Range("B" & oosROWmax)
        oosSHEET.Range("A" & oosROWmax).Value = "End"
        oosSHEET.Range("C" & oosROWmax).Value = "Incorrect"
    End If
    
'Perform if/then
    If (idSHEET.Range("E" & i) > 0) _
    And idSHEET.Range("E" & i) <> "err" Then
    
'Write a row to TTH Result Table
'Increment Result sheet row
    qtyROWmax = qtyROWmax + 1
    idSHEET.Range("C" & i).Copy qtySHEET.Range("B" & qtyROWmax)
    idSHEET.Range("E" & i).Copy qtySHEET.Range("C" & qtyROWmax)
    qtySHEET.Range("A" & qtyROWmax).Value = "Revise"
    
End If
Next i
End Sub

and then the code ends. do you see anything that might be causing this? perhaps calling a sub that calls a sub?
the only outside references in any of the subs i have in my workbook were recently added and this has been happening for a while or code that i never bothered removing once it became obsolete (i'll get around to it i swear)

example of obsolete code:
this was used to check image links for errors but i no longer have a use for it in this workbook.
its not linked to anything necessarily but it does have xml and http objects?

Code:
Sub checkLINKS()

Dim alink As Hyperlink
Dim strURL As String
Dim objhttp As Object

If MsgBox("Checking Image Links for 404 Error", vbOKCancel) = vbCancel Then

    Exit Sub

End If

On Error Resume Next
For Each alink In Cells.Hyperlinks
    strURL = alink.Address

    If Left(strURL, 4) <> "http" Then
        strURL = ThisWorkbook.BuiltinDocumentProperties("Hyperlink Base") & strURL
    End If

    Application.StatusBar = "Testing Link: " & strURL
    Set objhttp = CreateObject("MSXML2.XMLHTTP")
    objhttp.Open "HEAD", strURL, False
    objhttp.Send

    If objhttp.statustext <> "OK" Then

        alink.Parent.Select
        ActiveCell.Offset(0, 1).Value = 1
        
    End If

Next alink
Application.StatusBar = False
On Error GoTo 0
MsgBox ("Checking Complete!" & vbCrLf & vbCrLf & "Cells With Broken or Suspect Links are Highlighted in RED.")

End Sub
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If there are links to workbooks on OneDrive perhaps Excel is trying to access them when formulas are being calculated?
 

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
If there are links to workbooks on OneDrive perhaps Excel is trying to access them when formulas are being calculated?

i don't even have onedrive set up. its just installed because its a work computer with office.
if i go to the app its "not signed in" and says "sign in to get started"
 
Last edited:

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
im the most puzzled user in the world as to why onedrive pops up. never used it. never will use it. never referenced it. never told it to do anything. never mentioned it in notes. never even signed into it. its just.....there.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,737
Messages
5,597,826
Members
414,180
Latest member
Sir Khaya

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
Top