Problem with excel security settings causing UserInterFaceOnly to not work

[gazza365]

Hi,

I have a workbook that has the following code in the "This Workbook" page

[Private Sub Workbook_Open()
Dim wSht As Worksheet

 'set protection using UserInterface to allow macros to work
  For Each wSht In ActiveWorkbook.Sheets
        wSht.Protect _
            DrawingObjects:=True, _
            Contents:=True, _
            Scenarios:=True, _
            UserInterfaceOnly:=True
    Next wSht
    Sheets("Home").Activate
End Sub

This code protects every sheet but allows macros to run. It seems to work fine when I open the workbook from my documents as this is a trusted source in my excel settings.

The problem is that when I send it to other users, the excel security warning that disables 'Active content' etc seems to stop this from working properly. Even if the user clicks 'enable this content', they get 'run-time error 1004 - unable to set the Hidden property of the Range class'

As I understand it, the UserInterfaceOnly: = True code allows macros to run on protected sheets. It appears that the excel security settings that initially prevent the macros from running has also prevented my workbook open event from running.

Any idea how to stop this? The workbook has a lot of macros so adding in an 'Unprotect - protect' to each one would be very impractical.

Thanks in advance for any help.

Gareth