In the Workbook_open I've got the following:
Dim ws As Worksheet
For Each ws In Worksheets
ws.Protect Password:="test", UserInterFaceOnly:=True
Next ws
As I understand it this should allow macros to work. What is happening is that on firing the first macro (which involves a sheet change), a small message box comes up asking for a password. However, if you hit 'cancel' in this box it carries out the macro anyway. What is going on here?
I have used for some time the technique of having the worksheets protected and the (through buttons) call macros that Applicatition.Unprotect the sheet do something not allowed while sheet protected and then Application.Protect. They work. Then I read about UserInterfaceOnly and I wanted to use it. I tried unsuccessfully a sort of testing
Private Sub Workbook_Open()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect "aPass", UserInterfaceOnly:=True
Next wSheet
End Sub
Code for three buttons on the same sheet. Very simple code to try whether previous code behaves as expected.
Sub FillValueActive()
Dim activeNum As Integer
activeNum = ActiveCell.Row
Range("A1").Offset(activeNum - 1, 0).Value = "This text shows filled"
End Sub .........................................
I have a workbook that i am trying to make a shared workbook.
Shared workbooks have all kinds of functionality limitations. The one specifically giving me problems is the ability to protect workbooks.
Once a workbook is shared it's protection cannot be changed.
I protect a workbook prior to sharing and set the UserInterfaceOnly property = true (this allows my code to do whatever it wants but still maintains protection in the user interface).
Once i share and excel makes me save, everything works super and all the code in my auto_open macro works perfectly. But as soon as i close and open the file it seems that the UserInterfaceOnly property i set to true turns off and my code stops working.
Because it's a shared workbook i cannot turn it back on in the auto_open because that functionality is disabled.
I tried to use the code that allows macro to run even when the sheet is protected.
View 3 Replies View Related