Results 1 to 2 of 2

Thread: userInterfaceOnly leaves cells unlocked
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default userInterfaceOnly leaves cells unlocked

    Hi, I'm trying to leave certain cells/functions open for user input, but keep running into a problem I can't figure out. I've been through endless forum threads, but none has been able to help me so far...

    I have a workbook with 4 sheets, two of which are VeryHidden and not relevant. The third one is quite simple with only some cells allowing input through data validation drop-downs. I've locked/unlocked the cells I want and they are working fine.

    The last sheet however is a bit more complicated. It contains some cells that the user should be able unlocked, some that are only hidden and some that are locked and hidden. On top of this, there are grouped columns/rows and some columns that is possible to filter (C11:BW403, with row C containing the headers). To allow for this, I have this code in Workbook_Open

    Code:
    Public Sub Workbook_Open()
    
    
    Dim ws As Worksheet
    
    
    Application.ScreenUpdating = False
    
    
    'Enable Grouping and Filtering
        For Each ws In Sheets
            With ws
                .Unprotect "aPassword"
                .EnableOutlining = True
                .Protect "aPassword", AllowFiltering:=True, userInterfaceOnly:=True
            End With
        Next
    
    
    Application.ScreenUpdating = True
    
    
    End Sub
    Now, while the menu options etc. are grayed out, it's still possible to write in cells that I have said should be locked. The issues are only occuring in cells within my filter area (so B11 and BQ404 are still locked for instance).

    Some cells are merged (I know, I know...), but since I have merged cells outside this area that works and non-merged cells inside the area that don't work, I don't think this is the issue. I also have merged cells on the third sheet and there are no issues there.

    I've also tried .EnableAutoFilter, but it has the same result

    Does anyone have an idea of what I'm missing?

  2. #2
    New Member
    Join Date
    Sep 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: userInterfaceOnly leaves cells unlocked

    I managed to figure out a partial solution. In order to allow the filtering, I had the range C11:BW403 in 'Allow Edit Ranges' that apparently will allow editing the cells as well. If I removed it, grouping and filtering stopped working. However, when I changed it to only C11 all functions worked as I want them to. The issue remains in C11, so it might not be a full solution, but for my purposes it's sufficient.

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
  •