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

Ludwig

New Member
Joined
Apr 7, 2003
Messages
41
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?
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,855
Office Version
2010
Platform
Windows
Can you post all of your code or, at least, the part that protects the worksheet with a userinterfaceonly argument set to True?
 

Ludwig

New Member
Joined
Apr 7, 2003
Messages
41
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,855
Office Version
2010
Platform
Windows
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:
Code:
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,855
Office Version
2010
Platform
Windows
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:
Rich (BB code):
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

New Member
Joined
Apr 7, 2003
Messages
41
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,855
Office Version
2010
Platform
Windows
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

New Member
Joined
Apr 7, 2003
Messages
41
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
Code:
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,784
Messages
5,470,744
Members
406,719
Latest member
ensbana

This Week's Hot Topics

Top