+ Reply to Thread
Results 1 to 16 of 16

UserInterfaceOnly Issues....new issue (I think)

  1. #1
    Registered User
    Join Date
    01-28-2014
    Location
    Thailand
    MS-Off Ver
    Excel 2007
    Posts
    14

    UserInterfaceOnly Issues....new issue (I think)

    I have a sheet which has protection on the workbook and on the sheets. It uses autofilter and also outlining, and the vba needs to read and write some cells.
    Currently I have "nested" protect/unprotect routines. I was trying to utilise UserInterfaceOnly in order to economise the code, but it is not working.
    In fact, when I input the code, the keyword is not even being capitalised!!
    The boolean parameter is being capitalised and the text colour changed to blue, as per normal, but there is no capitalisation on the UserInterfaceOnly statement, and the statement appears to have no effect in the code. Here is the relevant line of code.....

    Please Login or Register  to view this content.

    This is in the Workbook_open() module. What am I doing wrong? Why is the parameter not capitalising as it should and why is it not effective?
    I have searched everywhere to see if his problem is common or if I am using the wrong syntax and can't seem to find anything.

    Greatly appreciate any assistance.
    Thanks.
    Littlenuts......
    Last edited by Littlenuts; 01-28-2014 at 03:54 AM.

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: UserInterfaceOnly Issues....new issue (I think)

    Welcome to the forum.

    We'd like to help you but first..

    Pls take some minutes to read forum rules and specially-in this case- rule#3
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: UserInterfaceOnly Issues....new issue (I think)

    Don't worry about the Text Capitalization just place the cursor in that Optional Argument userinterfaceonly:= part and press Ctrl+I and in the popup information box it will show the userinterfaceonly part highlighted in Bold if it so.. then don't worry the syntax is right


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  4. #4
    Registered User
    Join Date
    01-28-2014
    Location
    Thailand
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: UserInterfaceOnly Issues....new issue (I think)

    Sorry, my bad. Thanks for your assistance. I have edited to correct.

  5. #5
    Registered User
    Join Date
    01-28-2014
    Location
    Thailand
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: UserInterfaceOnly Issues....new issue (I think)

    Quote Originally Posted by :) Sixthsense :) View Post
    Don't worry about the Text Capitalization just place the cursor in that Optional Argument userinterfaceonly:= part and press Ctrl+I and in the popup information box it will show the userinterfaceonly part highlighted in Bold if it so.. then don't worry the syntax is right
    Many thanks for this. I was not aware of it. However it appears to work only with the method and not the arguments. I tried it with the Password:= part and it did not show anything...even though this part is working OK and is also capitalised. Likewise the UserInterfaceOnly:= does not show anything.
    I am still stumped.
    Thanks again.

  6. #6
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: UserInterfaceOnly Issues....new issue (I think)

    Your code is correct. What is your problem with it? (as Sixthsense says, the 'userinterfaceonly' name will not capitalise)
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  7. #7
    Registered User
    Join Date
    01-28-2014
    Location
    Thailand
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: UserInterfaceOnly Issues....new issue (I think)

    Quote Originally Posted by Izandol View Post
    Your code is correct. What is your problem with it? (as Sixthsense says, the 'userinterfaceonly' name will not capitalise)
    The parameter is not effective. As soon as the macro runs and tries to alter a cell in the sheet, it generates an error. I was suspecting that not capitalising was a symptom. Generally I find that the capitalisation occurs when excel encounters a valid command. When I put the "nested protect/unprotect back the sheet runs OK.
    Thanks....

  8. #8
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: UserInterfaceOnly Issues....new issue (I think)

    Perhaps you may post a sample workbook. Userinterfaceonly does not work in all situations but it should allow changing a cell value.

  9. #9
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: UserInterfaceOnly Issues....new issue (I think)

    The protection should be applied via code by enabling the userinterfaceonly and exce will lose the userinterfaceonly option once the workbook is closed and reopened or the protection is applied manually.

    So keep an workbookopen event to reprotect the workbook by enabling the userinterfaceonly and avoid manual protection to get rid of this issue.

  10. #10
    Registered User
    Join Date
    01-28-2014
    Location
    Thailand
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: UserInterfaceOnly Issues....new issue (I think)

    Dear Sixthsense,

    I am sorry I don't quite understand.....
    The module that I posted is in the Workbook_open() module. The protection is not applied manually but is applied on opening using the coding that I posted.....

  11. #11
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: UserInterfaceOnly Issues....new issue (I think)

    Please confirm whether the Workbook_open() resides in THISWORKBOOK code window?

    Don't let the users to remove protection and apply protection manually.

  12. #12
    Registered User
    Join Date
    01-28-2014
    Location
    Thailand
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: UserInterfaceOnly Issues....new issue (I think)

    Still working on this one......
    Here is how I am working. When I close the sheet I am unprotecting, and hiding the tabs and columns which need security (so that if someone with the incorrect security level opens, they do not see confidential information). Before I close I am protecting again with the "UserInterfaceOnly parameter.
    When I open, I am reading the environment userID and then writing this to a cell in the sheet (this is where it falls over), then depending on the value of this I validate against a lookup table, and unhide the sheets that they are authorised to see. In order to do this I am having to unprotect and reprotect each tab.
    Am I correct in assuming that UserInterfaceOnly does not work with setting the hidden or visible property and that I will still have to unprotect and protect in order to set this property?
    I have checked by rem'ing out the protect/unprotect loops on other areas in the code and it still runs OK (using outlining and autofilter) but in the open routine it falls over. It is like UserInterfaceOnly has to be re-applied when the sheet is opened (the property is not retained when the sheet is closed....like "EnableOutlining"....am I right??

    Thanks.

  13. #13
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: UserInterfaceOnly Issues....new issue (I think)

    There is no use in enabling the UserInterfaceOnly at the time of closing the workbook.

    Refer the Post #9 content for getting better understanding

  14. #14
    Registered User
    Join Date
    01-28-2014
    Location
    Thailand
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: UserInterfaceOnly Issues....new issue (I think)

    Thanks Sixth,

    So it appears that like the EnableOutlining, the property is lost on closing and will need to be re-established on opening. That really explain my issue I guess. If I remove the statement from the open event then the autofilter and outlining do not work (regardless of the protect arguments). When I put back the UserInterfaceOnly........the autofilter and outlining work again....I guess it is working as well as it can.

    Many thanks for your assistance and comments.......sorry for my poor understanding.

  15. #15
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: UserInterfaceOnly Issues....new issue (I think)

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  16. #16
    Registered User
    Join Date
    01-28-2014
    Location
    Thailand
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: UserInterfaceOnly Issues....new issue (I think)

    Thanks Sixth....I have marked as "Solved".....

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. how to list only top 10 issue out of 50 issues
    By koi in forum Excel General
    Replies: 4
    Last Post: 01-16-2013, 03:38 PM
  2. [SOLVED] Rounding Issues in Excel formulas or issue with leap year?
    By raw_geek in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-11-2013, 06:53 AM
  3. UserInterfaceOnly
    By justinharris in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-05-2011, 04:55 PM
  4. [SOLVED] help! user form issues; can't find subroutines; appearance issue
    By in-over-his-head-bill in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-06-2006, 05:30 PM
  5. [SOLVED] UserInterfaceOnly
    By D.Parker in forum Excel General
    Replies: 1
    Last Post: 05-17-2005, 04:06 PM

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