Results 1 to 5 of 5

Protection with UserInterfaceOnly:= TRUE

This is a discussion on Protection with UserInterfaceOnly:= TRUE within the Excel Questions forums, part of the Question Forums category; ActiveSheet.Protect UserInterfaceOnly:=True allows me to protect sheets whilst still being able to write to cells from code. But I cannot ...

  1. #1
    New Member
    Join Date
    Dec 2002
    Location
    Brisbane
    Posts
    45

    Default

    ActiveSheet.Protect UserInterfaceOnly:=True allows me to protect sheets whilst still being able to write to cells from code.

    But I cannot use code to put AutoFiltering on or off while sheet is protected for UserInterfaceOnly. How do I do this.

    I have written a filter procedure that sets row hights to zero for rows that don't match the criteria but I would like to simply use Excels AutoFilter methods from the code while protection is on. I don't like turning protection on and off in code.

    Thanks

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579

    Default

    Hi georgec

    You could try using this macro in a module. O

    Sub protect1()
    ActiveSheet.EnableAutoFilter = True
    ActiveSheet.Protect Contents:=True, userInterfaceOnly:=True
    End Sub

    Hope this helps
    regards
    Derek

  3. #3
    New Member
    Join Date
    Jul 2006
    Location
    Cardiff
    Posts
    9

    Default

    Hi Derek,

    I'm having a similar problem to georgec (well i think i do!). I have a checkbox (from control menu) and the cell its linked to i need to lock so it can't be accidentaly altered, but when i lock the sheet i get a message saying the cell is protected. I've tried using the code you wrote but it still doesn't seem to work:

    Sub protect1()
    ActiveSheet.Protect Contents:=True, userInterfaceOnly:=True
    End Sub

    Could you help at all please?

    (sorry if i'm highjacking your request georgec)

    Many thanks,
    very confused person

  4. #4
    Board Regular
    Join Date
    May 2009
    Location
    Texas, USA
    Posts
    1,290

    Default Re: Protection with UserInterfaceOnly:= TRUE

    Instead of protect and unprotect routines use the folloing code in teh open workbooks event

    Code:
    Sheet1.Protect userinterfaceOnly:=True
    It protects against users, but aloows procedures to cahnge items on teh sheet
    Bill
    Use Option Explicit to avoid chasing code.

  5. #5
    Board Regular
    Join Date
    May 2009
    Location
    Texas, USA
    Posts
    1,290

    Default Re: Protection with UserInterfaceOnly:= TRUE

    salmagundi,
    I believe the userinterfaceOnly:=True has to be set up when you open the workbook
    Bill
    Use Option Explicit to avoid chasing code.

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
  •  


DMCA.com