Userinterfaceonly problem

[Paul Wakefield]

Hi,

I have a Workbook_open procedure which applies .Protect userinterfaceonly:= True to every sheet in the Workbook.

However there are a couple of routines - notably Range("xxxx").ClearContents where the macro falls over complaining that the sheet is protected.

Are there any restrictions on UserInterfaceOnly or am I misunderstanding its use?

Paul

 

[schielrn]

Are there any restrictions on UserInterfaceOnly or am I misunderstanding its use?

There are none that I am aware of. I assume you are looping through all the sheets when protecting? You are sure you are re-protecting every sheet with userinterfaceonly? But maybe there was something I didn't realize, but haven't came across this issue before.

 

[Paul Wakefield]

Thank you for such a prompt reply.

The code is:

Private Sub workbook_open()
Dim wSheet As Worksheet

'Allow macros to run on protected worksheets
For Each wSheet In Worksheets

wSheet.Protect Password:="xxx", UserInterFaceOnly:=True

Next wSheet

End Sub


I am using Excel 2007 if that is relevant.

 

[JoeMo]

Thank you for such a prompt reply.

The code is:

Private Sub workbook_open()
Dim wSheet As Worksheet

'Allow macros to run on protected worksheets
For Each wSheet In Worksheets

wSheet.Protect Password:="xxx", UserInterFaceOnly:=True

Next wSheet

End Sub


I am using Excel 2007 if that is relevant.

I see nothing in your code that would prevent clearing cell contents via VBA.

 

[schielrn]

hmmm... Yes that would protect all worksheets. And I just did a test to see if I protected a sheet userinterfaceonly and then re-protected it not using that. It let me still clear contents. So I am unsure why this would be coming up as an error for you?

That is really strange, assuming you have no old compatibility issues such as macro or dialog sheets you would be fine. But I highly doubt this would be the issue.

Does it always fall over at the same point?

 

[Paul Wakefield]

Yes - it always falls over at this line of code:

Range("mastercodelist").Copy Destination:=Range("MasterDestination")

 

[Paul Wakefield]

As you will have seen, this is not in the ClearContents line for which I apologise. When I first tried to analyse the issue, it was after the initial macro failure which, because it left the spreadsheet in a partially deleted state, caused the subsequent ClearContents to fail.

Having spent more time looking at the problem, the initial cause is definitely the Copy Destination line. But should not this work anyway with UserInterfaceOnly = True?

Your thoughts would be gratefully received.

 

[JoeMo]

As you will have seen, this is not in the ClearContents line for which I apologise. When I first tried to analyse the issue, it was after the initial macro failure which, because it left the spreadsheet in a partially deleted state, caused the subsequent ClearContents to fail.

Having spent more time looking at the problem, the initial cause is definitely the Copy Destination line. But should not this work anyway with UserInterfaceOnly = True?

Your thoughts would be gratefully received.

What error message do you get when the macro fails? Is the range "mastercodelist" the same size as the range "MasterDestination"? If not, this will cause an error.

 

[Paul Wakefield]

The error message is:

"Run-time error '1004':

The cell or chart that you are trying to change is protected and therefore read-only.

To modify a protected cell or chart........................................"

Mastercodelist is a dynamic range, MasterDestination is a single cell.


The code runs fine if I use specific sheet protection and unprotection throughout the macros. However as the design grows, it becomes harder to ensure this method gives complete control of protection as well as introducing extra lines of code. This is why I hoped the UserInterfaceOnly route would provide a solution.

Many thanks for your time looking at this.

 

[VoG]

This may be a silly suggestion but what happens if you unprotect them first:

Private Sub workbook_open()
Dim wSheet As Worksheet

'Allow macros to run on protected worksheets
For Each wSheet In Worksheets
    wSheet.Unprotect Password:="xxx"

    wSheet.Protect Password:="xxx", UserInterFaceOnly:=True

Next wSheet

End Sub