Updating value of a Named cell fails using VBA - a UserInterfaceOnly:=True problem

[Ludwig]

I have a Named cell "CreateHolder" which refers to 'Access Rights'!$F$2 as shown in Name Manager. The referenced worksheet is protected with a password using "UserInterfaceOnly:=True" i.e. user can't change it but VBA can.

I can easily test the value of the named cell, but when I try to update it thru VBA it fails.

My Code:
-----------
If Range("CreateHolder").Value = "" Then ' If no-one else has claimed it, current user can
Range("CreateHolder").Value = Application.UserName
.... etc etc ....


The IF test works fine, however setting the cell value line fails with the following error which causes the whole Workbook_Open() code to stop running:

----------------------------------------------------
Run-time error '1004':
Application-defined or object-defined error
----------------------------------------------------

This is reproducable, and can only be avoided by unprotecting then reprotecting the worksheet with "UserInterfaceOnly:=True" prior to the VBA code above, which of course slows down the Workbook_Open() code where it resides. This is in spite of that code having been run by the previous person who had opened, & saved, the spreadsheet ... the moment I comment out the un/re-protect statements the VBA error above comes back on that line for future opens.

Is this re-setting the protection a requirement with VBA each time the workbook is opened?

 

[JoeMo]

Can you post all of your code or, at least, the part that protects the worksheet with a userinterfaceonly argument set to True?

 

[Ludwig]

Here's the relevant code I referred to:
-----------------------------------------------

If Range("PalletCreateHolder").Value = "" Then ' If no-one else has claimed it, current user can
Call ProtectThisSheet("Access Rights") ' Dunno why .. just stops VBA crashing on next line!
Range("PalletCreateHolder").Value = Application.UserName ' means if they crashed out, they still claim the right
End If




And the "call"-ed SUB code used above:
------------------------------------------------
Sub ProtectThisSheet(ByVal MySheetName As String, Optional ByVal ProtectSheet As Boolean = True)

myScrnUpd = Application.ScreenUpdating ' Store current status
Application.ScreenUpdating = False ' Stop screen updates causing it to flash for the person ...

' if sheet is protected, unprotect if so required
If Sheets(MySheetName).ProtectContents = True Then
On Error Resume Next ' multiple just in case of manual locking accidents!
Sheets(MySheetName).Unprotect Password:=c_MyUnl1PSWD ' one it should be
If Sheets(MySheetName).ProtectContents = True Then Sheets(MySheetName).Unprotect Password:=c_MyUnl2PSWD
If Sheets(MySheetName).ProtectContents = True Then Sheets(MySheetName).Unprotect Password:=c_MyUnl3PSWD
If Sheets(MySheetName).ProtectContents = True Then Sheets(MySheetName).Unprotect Password:=c_MyUnl4PSWD
On Error GoTo 0
Else
'If Not ProtectSheet Then Debug.Print " : '" & MySheetName & "' already unprotected ..."
End If

If ProtectSheet Then
If MySheetName = "MyData" Then ' one sheet has one extra option set on
Sheets(MySheetName).Columns.EntireColumn.Hidden = False ' show all columns
Sheets(MySheetName).Range("N:O,Q:Q").EntireColumn.Hidden = True ' then reset ones needing to be hidden ..
gl_AllowChgsInPData = False
Sheets(MySheetName).Protect Password:=c_MyLockPSWD, DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowInsertingHyperlinks:=True, AllowSorting:=True, _
AllowFormattingRows:=True, AllowFiltering:=True, UserInterfaceOnly:=True
Else
Sheets(MySheetName).Protect Password:=c_MyLockPSWD, DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowInsertingHyperlinks:=True, AllowSorting:=True, _
AllowFiltering:=True, UserInterfaceOnly:=True
End If
Sheets(MySheetName).EnableSelection = xlNoRestrictions
Sheets(MySheetName).EnableOutlining = True ' currently only useful for "Change History" but do it to all as general principle
'Debug.Print " : '" & MySheetName & "' suitably protected ..."
End If

Application.ScreenUpdating = myScrnUpd ' restore to previous screen updates status

End Sub

--------------------------------------------------------------------------
Apologies for layout, when I paste the indented code in, all leading spaces on lines disappear(?!?!).



As you can see, I've built some robustness into the code, in case for example I manually lock the spreadsheet with CAPS LOCK on. I can also call "Sub ProtectThisSheet" from another one which loops through *all* worksheet names ensuring they are locked 'correctly'.

 

[JoeMo]

Your code seems unnecessarily complex, hard to read w/o code tags, and a bit confusing. Why do you have multiple passwords for the same sheet? What about trying something simpler like this:

Sub KISS()
If Range("PalletCreateHolder").Value = "" Then
    Sheets("Access Rights").Protect pswd:="c_MyLockPSWD", userinterfaceonly:=True
    Range("PalletCreateHolder").Value = Application.UserName
End If
End Sub

 

[JoeMo]

Your code seems unnecessarily complex, hard to read w/o code tags, and a bit confusing. Why do you have multiple passwords for the same sheet? What about trying something simpler like this:

Sub KISS()
If Range("PalletCreateHolder").Value = "" Then
    Sheets("Access Rights").Protect password:="c_MyLockPSWD", userinterfaceonly:=True
    Range("PalletCreateHolder").Value = Application.UserName
End If
End Sub

Just noticed I used an abbreviation for the password argument which will cause a run time error. Change "pswd" to "password" - as with the bold red font above.

 

[Ludwig]

Just noticed I used an abbreviation for the password argument which will cause a run time error. Change "pswd" to "password" - as with the bold red font above.

As mentioned in my earlier post, it appears "unnecessarily complex" as it serves a more general situation, including handling for when a sheet might get locked manually with CAPS LOCK On and hence the wrong password is unknowingly used. Another key use is for unlocking (without locking again), and locking (correctly) any/all sheets in the workbook. Just one piece of code covering all uses within VBA & do no need to go "find" all places ever again to make an update.

However, the suggested change is not a solution for the problem, in that I think I shouldn't be needing to do it anyway ... the worksheet is only locked for userinterface, not VBA. Why therefore is VBA complaining unless I issue that protect again every time I open the workbook?

 

[JoeMo]

As mentioned in my earlier post, it appears "unnecessarily complex" as it serves a more general situation, including handling for when a sheet might get locked manually with CAPS LOCK On and hence the wrong password is unknowingly used. Another key use is for unlocking (without locking again), and locking (correctly) any/all sheets in the workbook. Just one piece of code covering all uses within VBA & do no need to go "find" all places ever again to make an update.

However, the suggested change is not a solution for the problem, in that I think I shouldn't be needing to do it anyway ... the worksheet is only locked for userinterface, not VBA. Why therefore is VBA complaining unless I issue that protect again every time I open the workbook?

One way to avoid this is to put the sheet.protect password:="xyz", userinterfaceonly:= True portion in a workbook_open event macro so that it happens automatically every time the workbook is opened.

 

[Ludwig]

One way to avoid this is to put the sheet.protect password:="xyz", userinterfaceonly:= True portion in a workbook_open event macro so that it happens automatically every time the workbook is opened.

Thanks for that suggestion.. that's what I'm already doing. I'm just mystified why VBA has forced me to do that. Once it's done (in _open) it lasts for the whole time the workbook is open i.e. until I save & close it. By that I mean I can change the value in that cell as many times as I like without having to do it again. e.g. in _BeforeClose I issue

Range("PalletCreateHolder").Value = ""

and VBA does not complain ..... unless, as I found when testing code changes, the VBA environment has reset itself with all global variables returning to default values ... then this statement in "close" fails unless I first issue that "userinterfaceonly" command. However, in normal operation it doesn't happen at that point because VBA doesn't reset itself like it can in testing.