CODEWITHSUNDEEP
vbaexcel
mathgenius
mathgenius

Reputation: 513

Sharing an excel workbook renders VBA unviewable and conditional formatting uneditable

After creating a workbook and writing a macro in the "Module" window, the macro is assigned to a button. Protecting the workbook by clicking Review > "Protect Shared Workbook" renders the entire VBA project of that workbook "unviewable", the exact error message is "Project is Unviewable". In addition the Conditional Formatting button is grayed-out.

Unprotecting the workbook and sharing it with the option "allow changes by one or more..." ticked has no effect.

The macro still functions but I would like to be able to remedy the "Project is Unviewable" issue.

Upvotes: 0

Views: 1257

Answers (2)

Joshua Dannemann
Joshua Dannemann

Reputation: 2080

Well, here is the answer to your question. enter image description here

So, there it is, the VBA project becomes read-only when you share the workbook. I sincerely doubt there is a work-around for this one.

Upvotes: 1

Joshua Dannemann
Joshua Dannemann

Reputation: 2080

Since all you need to do is copy data from another workbook, I'd suggest you take a different approach. You can ditch the macro and add the information from the other workbook into your spreadsheet as an external data source. Then you can simply right-click > refresh. This should also ensure that your conditional formatting remains intact.

Remember, after you insert the external data, you need to do a couple more things to make sure that you do not get prompted for the file name on refresh, that the import does not cause disruptive shifts, and formulas adjacent to the external data are copied down.

Click on properties

enter image description here

Then set the options as so. If you would like, you can also preserve column width.

enter image description here

Last, if you need to change the path to the file problematically, find out the name of the data range and then you can use code similar to the following in the "ThisWorkbook" module to change it and then refresh the data.

Sub Workbook_Open()
    Sheet1.Range("test").QueryTable.Connection = "TEXT;" & PATHTOFILE
    Sheet1.Range("test").QueryTable.Refresh
End Sub

Upvotes: 0

Related Questions