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

 

[Paul Wakefield]

Thanks for the idea but no joy I'm afraid. It still falls over at the same point.

 

[Paul Wakefield]

I have found a workaround.

The issue only arises when using Copy and Destination and only when the source and destination are on different worksheets. By splitting the code into two lines (i.e. using copy and PasteSpecial), the code runs correctly.

But, if anybody has a better solution, it would be good to know.

 

[rmeyts]

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

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

Paul,

I have had the same problem, driving me nuts last week. Reading your post has encouraged me to look deeper, I now think I have the answer. You are copying from a protected sheet where the source range has the property HIDDEN, therefore the VBA code cannot evaluate the source formula.

It may be that, confusingly, Userinterfaceonly only applies to the destination sheet, not to any source data, and you therefore need to make the appropriate assumptions when coding.

This is the stripped down code to demonstrate:

Sub test_CopyFormula()

''' copy a formula from cell D1 in Sheet1 to cell D1 in Sheet2
''' ===========================================

' set protection in Source sheet
Sheet1.Unprotect
Sheet1.Protect UserInterFaceOnly:=True

' Source has any formula in D1, need to copy formula into Sheet2

' this next line always works
Sheet2.Range("D1").Value = Sheet1.Range("D1").Value

' this next line works ONLY WHEN the cell format in the Source sheet is not 'HIDDEN'
Sheet2.Range("D1").FormulaR1C1 = Sheet1.Range("D1").FormulaR1C1
End Sub

Regards,

Roger

 

[Paul Wakefield]

Roger,

Thanks for the thought. Unfortunately none of my cells are hidden.

If Sheet2 is protected (userinterfaceonly:= True) and the last line of your code is altered as follows:

Sheet1.Range("D1").Copy Destination:=Sheet2.Range("D1")

The code will complain that the sheet is protected whether or not the cells have the hidden format.

Regards.

Paul