+ Reply to Thread
Results 1 to 8 of 8

Problem with UserInterfaceOnly

  1. #1
    Registered User
    Join Date
    04-18-2006
    Posts
    24

    Problem with UserInterfaceOnly

    I have a subroutine that runs for the worksheet_selectionchange event on a protected sheet. Apparently, subs don't work on protected sheets. I found online a method that is supposed to allow subroutines while keeping the worksheet protected. There is an argument of the protect method that should protect the user interface, but allow macros.


    Please Login or Register  to view this content.
    The UserInterfaceOnly argument is supposed to allow VBA changes to the worksheet, even while it's protected, if set to TRUE. Default value is FALSE, from what I've read. However, even when I set this to true immediately before the subroutine runs, I encounter a run-time error on a specific piece of the sub that handles the data validation of particular cells. (Note, the sub works perfectly when the sheet is not protected.)

    Anyone know anything about this? Also, is it significant that it does not auto-capitalize "userinterfaceonly" when I type it in?

    Thanks a lot.
    Last edited by agentsmith83; 03-29-2010 at 10:16 AM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Problem with UserInterfaceOnly

    Protection does not prevent the code running,it will prevent the code fromchanging the sheet. There's an example here

    http://excel-it.com/vba_examples.htm
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    04-18-2006
    Posts
    24

    Re: Problem with UserInterfaceOnly

    But if I use the above-described argument, shouldn't my code be allowed to change the sheet? In addition, the cells I'm changing are not locked.

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Problem with UserInterfaceOnly

    If you want to alter data validation, you have to unlock drawing objects:
    Please Login or Register  to view this content.
    I suspect this is due to the in-cell dropdown - whether you use it or not, the Add method needs to be able to add the dropdown drawing object if required, and the method may well test for protection first as a result. (I am not 100% certain that is the cause, but it seems the most likely to me)
    Last edited by romperstomper; 03-29-2010 at 03:38 AM. Reason: Added "explanation"
    Remember what the dormouse said
    Feed your head

  5. #5
    Registered User
    Join Date
    04-18-2006
    Posts
    24

    Re: Problem with UserInterfaceOnly

    This works, thank you so much.

  6. #6
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Problem with UserInterfaceOnly

    Just a follow-up note:
    I have by chance been experimenting with this method today and it does not appear to be particularly reliable, so I would personally suggest that unprotecting the sheet, making the changes and then reprotecting is probably safer in general. I'll update if I figure out why it sometimes works and sometimes doesn't.

  7. #7
    Registered User
    Join Date
    02-12-2014
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Problem with UserInterfaceOnly

    Just a follow-up note:
    I have by chance been experimenting with this method today and it does not appear to be particularly reliable, so I would personally suggest that unprotecting the sheet, making the changes and then reprotecting is probably safer in general. I'll update if I figure out why it sometimes works and sometimes doesn't.
    Can this be done by cobe so that when a button is pushed the macro unprotects the sheet updates the charts and puts the protection back on.

    Here is the code I use to update the charts.

    Please Login or Register  to view this content.
    Last edited by arlu1201; 02-13-2014 at 01:33 AM.

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Problem with UserInterfaceOnly

    Frank,

    Welcome to the forum -2 pointers.

    1. I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

    2. Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1