Link to home
Start Free TrialLog in
Avatar of Robert Berke
Robert BerkeFlag for United States of America

asked on

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

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
Avatar of Steve Knight
Steve Knight
Flag of United Kingdom of Great Britain and Northern Ireland image

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

Steve
Avatar of Robert Berke

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Steve Knight
Steve Knight
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
That in the menus is File / Options / Advanced / Display options for this workbook - "for objects show": All / Nothing
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
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.
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?anchorAnswerId=43033111#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
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?