We help IT Professionals succeed at work.

Excel "privacy warning" causes a bug. How to fix that workbook without rebuilding it.

rberke
rberke asked
on
I have had this happen several times before and always ended up rebuilding the entire workbook.

The problem hit me today it is inside a pretty big workbook with vba code that was not working correctly.

I tried several dozen "fixes" but eventually had to bite the bullet and rebuild it. After rebuilding it the vba worked properly.

But in my efforts to fix things I came up two interesting workbooks.

Open NoBug.xlsm an run sub deletenow33. It does not abort.
Open Bug.xlsm and run sub deletenow33.  You will see an abort.

The Workbooks are practically identical except Bug.xlsm gives the privacy warning.

I have virtually everything including reverting from xlsm back to  xls, and the bug still occurs.

I am considering turning the .xlsm into a .zip file and doing further research, but I hope someone on experts exchange can tell me how to fix the problem WITHOUT rebuilding the workbooks.






a very simple example of why this problem is so frustrating.

I have attached to workbooks Bug.xlsm and NoBug.xlsm.  They are nearly identical. The only difference is that when I save the first
bug.xlsm
NoBug.xlsm
Comment
Watch Question

Steve KnightIT Consultancy
CERTIFIED EXPERT

Commented:
I tried out various things with your workbooks earlier and thought had it a few times but didn't.... will try again

Steve
rberkeConsultant

Author

Commented:
Thanks for trying. I have given up myself. Several times I thought I had it but it would slip away.

To fix my real workbook I ended up doing the following
1 I copied all the worksheets to a new workbook,
2 manually moved the forms and vba modules
3 relocated the pivot tables so their source pointed to new workbook.

Of course those same steps would work with  bug.xlsm but that was not the goal of this question.
I'll leave it open for a few more days the close it down and award points.
IT Consultancy
CERTIFIED EXPERT
Commented:
Well I had another look earlier, is very odd.  The one that is failing if you turn the setting in Options / Advanced to show the comment boxes does not show them, only when you click/hover.  The other one DOES show the boxes.

Whether the boxes are shown on the screen or not though through the Advanced Option, and whether their .visible property is true or not the 'good' workbook shows the address where they WOULD be seen as opposed to erroring.

I can;t find anything logical causing the shape object to have no topleft or bottom right co-ordinates.  It is probably something fundamentally obvious like a setting such as the comment hiding one at the workbook level.

Stuck, don't know, sorry!
Steve KnightIT Consultancy
CERTIFIED EXPERT

Commented:
Ting...

activeworkbook.DisplayDrawingObjects=xlDisplayShapes

Open in new window


I saved-as both workbooks to "XML Spreadsheet 2003" thinking there must be some workbook level issue here stopping the text boxes having properties.  The "bug" one had this in there:

DisplayDrawingObjects>HideAll</DisplayDrawingObjects>

Open in new window


and the "nobug" one didn't.

Sub deletenow33()

On Error GoTo err1

Dim tmp As Object, shp As Object, cell As Range
Set cell = [a1]

Debug.Print "Display shapes on"
ActiveWorkbook.DisplayDrawingObjects = xlDisplayShapes

With cell
        .ClearComments
        .AddComment
            Debug.Print "bottomright " & .Comment.Shape.BottomRightCell.Address
        
End With

Debug.Print "Display shapes off"
ActiveWorkbook.DisplayDrawingObjects = xlHide

With cell
        .ClearComments
        .AddComment
            Debug.Print "bottomright " & .Comment.Shape.BottomRightCell.Address
        
End With

Debug.Print "Display shapes on"
ActiveWorkbook.DisplayDrawingObjects = xlDisplayShapes

With cell
        .ClearComments
        .AddComment
            Debug.Print "bottomright " & .Comment.Shape.BottomRightCell.Address
        
End With

Exit Sub

err1:
Debug.Print "   Error: " & Error$
Resume Next

End Sub

Open in new window


gives me:

Display shapes on
bottomright $D$5
Display shapes off
   Error: Application-defined or object-defined error
Display shapes on
bottomright $D$5
Steve KnightIT Consultancy
CERTIFIED EXPERT

Commented:
That in the menus is File / Options / Advanced / Display options for this workbook - "for objects show": All / Nothing
Steve KnightIT Consultancy
CERTIFIED EXPERT

Commented:
Vaguely logical once you know why - comment boxes are shapes, so even if you hide a specific comment or hide all comments using Options their text boxes still retain coordinates, but if you also hide all shape objects then they don't exist to be able to have coordinates...

I suppose if you hide comments you will still want to see them on hover over etc. so it needs to know where they are located still but if they are turned off altogether it doesn't need to have that info ready.

As to how your setting to display shapes got turned off.... maybe you hide and then show them somewhere else in your code and the 'show' bit doesn't get run or someone just fiddling in Options?

Steve
rberkeConsultant

Author

Commented:
I solved it !!!!  I changed both xlsm files to be zips then used Notepad to open the xml.  Bug.zip\xl\workbook.xml looked a little  like this.  NoBug looked the same EXCEPT item 3,4 and 6 were omitted.


  1. <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  2. <workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><fileVersion appName="xl" lastEdited="5" lowestEdited="5" rupBuild="9303" codeName="{8C4F1C90-05EB-6A55-5F09-09C24B55AC0B}"/><workbookPr
  3. showObjects="none"
  4. filterPrivacy="1"
  5. codeName="ThisWorkbook"
  6. hidePivotFieldList="1"
  7. defaultThemeVersion="124226"/><bookViews><workbookView xWindow="240" yWindow="105" windowWidth="14805" windowHeight="8010"/></bookViews><sheets><sheet name="Sheet1" sheetId="7" r:id="rId1"/></sheets><calcPr calcId="145621"/></workbook>

By trial and error I discovered  deleting item 3 fixed the program bug. Deleting item 4 fixed the privacy message.

Notepad allowed me to delete both 3 and 4, but I could not save it directly into the ZIP file. I had to save it to c:\temp\ then copy it into bug.zip\xl\
after items 3 and 4 were gone I renamed bug.zip back to bug.xlsm and it was fixed.
I have no idea if that exact change will work on future problems, but I suspect it will.

By the way, your attempt to save as office 2003 xml was a dead end.  The resulting xml does NOT have the privacy message, and does NOT have the vba code.
Steve KnightIT Consultancy
CERTIFIED EXPERT

Commented:
The old xml file format doesn't contain the rest of that but it did contain the structure of the workbook and your files had that line in such led to the objects bring hidden which you can turn off in vba or gui menu like i said... no need to hand adjust the xml imo.

https://www.experts-exchange.com/questions/29172543/Excel-privacy-warning-causes-a-bug-How-to-fix-that-workbook-without-rebuilding-it.html#a43033111

So one way or another just seems to be drawing objects hidden menu option needs to be right and you can force that on in your vba with the command i have given too.

Steve
rberkeConsultant

Author

Commented:
You are right, I used the VBA immediate window and issued  activeworkbook.DisplayDrawingObjects=xlDisplayShapes

That caused the vba code to work ;properly.

But the privacy message problem still remained.  I know how to fix it by converting to Zip and deleting the filterPrivacy="1"

Do you know of an alternative vba command that will do the same thing?