+ Reply to Thread
Results 1 to 3 of 3

VB run time error 1004 on protected sheet

  1. #1
    Registered User
    Join Date
    05-06-2008
    Location
    Chester, England
    MS-Off Ver
    Work: Office 97-03. Home: XP, Office 2010
    Posts
    35

    VB run time error 1004 on protected sheet

    Hi

    I need a little help getting round a run time 1004 error, I've done a basic search of the forum however haven't been able to make the suggested solutions to other problems work. I'm afraid my VB knowledge is Very Basic!

    I have a sheet which is to be used by circa 1000 colleagues, I need certain cells to be available to them to key data and the remainder of the sheet to be view only. I don't want them to have to enter a password.

    I've tried incorporating two potential workarounds from another thread however I can't get either to work, probably due to my lack of understanding so I need your help please

    These are the two codes I've tried using but if I'm honest I'm not sure if they apply to my problem or where to place the code

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

    This is the code I need to run on the protected sheet, would anyone be able to advise how I can protect the sheet and also run the code? And if either of the above codes should work where I'd place them in the below code? If that makes sense, told you my knowledge was basic!

    Please Login or Register  to view this content.
    Many thanks in advance

    Greg
    Last edited by Twaddy; 10-27-2010 at 06:22 PM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VB run time error 1004 on protected sheet

    the UserInterfaceOnly:=True flag needs to be set once for each sheet it needs to apply to each time the workbook is opened. This flag is unique in that it disappears when the workbook closes.

    So, the simplest way to deal with this is a workbook_open macro inside the ThisWorkbook module that properly sets the flag for you as soon as the workbook opens. Then all other macros can run normally on those sheets.

    In ThisWorkbook module:
    Please Login or Register  to view this content.

    That should set you up, just edit the Array() to list the sheets where you want this.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    05-06-2008
    Location
    Chester, England
    MS-Off Ver
    Work: Office 97-03. Home: XP, Office 2010
    Posts
    35

    Re: VB run time error 1004 on protected sheet

    Hi JB, thanks very much for your reply

    It worked a treat and exactly as I wanted

    Thanks very much

    Greg
    Last edited by Twaddy; 10-27-2010 at 06:18 PM.

+ 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