Results 1 to 8 of 8

Thread: Updating value of a Named cell fails using VBA - a UserInterfaceOnly:=True problem
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2003
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,113
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    8 Thread(s)

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

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

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    New Member
    Join Date
    Apr 2003
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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'.

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,113
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    8 Thread(s)

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

    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
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  5. #5
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,113
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    8 Thread(s)

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

    Quote Originally Posted by JoeMo View Post
    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 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.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  6. #6
    New Member
    Join Date
    Apr 2003
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by JoeMo View Post
    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?

  7. #7
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,113
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    8 Thread(s)

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

    Quote Originally Posted by Ludwig View Post
    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.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  8. #8
    New Member
    Join Date
    Apr 2003
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by JoeMo View Post
    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 by Ludwig; May 23rd, 2019 at 05:41 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •