user interface only question

[michael.beckinsale]

Hi All,

Most of the sheets in my workbook are protected using user interface
only = TRUE.

The problem l am facing is that the application l am building has
numerous macro's which are trigered using a custom menu & custom right
click menu. I assume this is regarded by Excel as part of the user
interface and that is why l get error messages stating that the sheet
is protected.

Is there any way that l can get my custom menu's to work on protected
sheets other than re-coding to unprotect sheets before running the
macro?

All suggestions gratefully accepted.

Regards

Michael B

 

[Jim Rech]

I assume this is regarded by Excel as part of the user interface

I don't think so. UIO does not permit every action but I think it permits
most.

Put this in a new workbook and run the first sub. Then click the new tool
button.

Sub CreateToolBtnAndProtectSheet()
DelToolBtn
With CommandBars("Worksheet Menu Bar").Controls.Add(msoControlButton, ,
, , True)
.FaceId = 59
.Caption = "Test UIO"
.OnAction = "Clicked"
End With
''Protect and set user interface only on:
ActiveSheet.Protect , True, True, True, True
''No UIO:
''ActiveSheet.Protect , True, True, True, False
End Sub

Sub DelToolBtn()
On Error Resume Next
CommandBars("Worksheet Menu Bar").Controls("Test UIO").Delete
ActiveSheet.Unprotect
End Sub

Sub Clicked()
''sheet is protected so this works only with UIO on
Range("A1").Value = Range("A1").Value + 1
End Sub


--
Jim
| Hi All,
|
| Most of the sheets in my workbook are protected using user interface
| only = TRUE.
|
| The problem l am facing is that the application l am building has
| numerous macro's which are trigered using a custom menu & custom right
| click menu. I assume this is regarded by Excel as part of the user
| interface and that is why l get error messages stating that the sheet
| is protected.
|
| Is there any way that l can get my custom menu's to work on protected
| sheets other than re-coding to unprotect sheets before running the
| macro?
|
| All suggestions gratefully accepted.
|
| Regards
|
| Michael B
|

 

[michael.beckinsale]

Hi Jim,

Thanks for the info and code.

I tested your code and all was OK. I then tested my workbook and tried
to get it to fail and guess what, it wouldn't. Very wierd.

Do you know where l can find a list of what will NOT function using
UIO. Its a pain that most of it will work but some wont.

Again thanks for the info, l hope l haven't wasted too much of your
time.

Regards

Michael B

 

[Jim Rech]

Do you know where l can find a list

No I don't, sorry. I just run my code and when something breaks I revise
that bit of code to turn protection on and off. Yes a pain but I don't
think there's any way around it.

--
Jim
| Hi Jim,
|
| Thanks for the info and code.
|
| I tested your code and all was OK. I then tested my workbook and tried
| to get it to fail and guess what, it wouldn't. Very wierd.
|
| Do you know where l can find a list of what will NOT function using
| UIO. Its a pain that most of it will work but some wont.
|
| Again thanks for the info, l hope l haven't wasted too much of your
| time.
|
| Regards
|
| Michael B
|

 

[Peter T]

Note the UserInterfaceOnly property is not saved with the WB, so you need to
reset it sometime between reopening, eg open event, and before using code
that changes protected aspect(s) of the sheet.

Regards,
Peter T