+ Reply to Thread
Results 1 to 2 of 2

using VB code to unlock and lock different areas on multiple sheets

  1. #1
    Registered User
    Join Date
    02-28-2007
    Posts
    55

    Question using VB code to unlock and lock different areas on multiple sheets

    Hello

    Could someone please have a look at the attached workbook.

    What should happen is when the correct username and password are entered the areas shaded yellow (in example only) are unlocked on every sheet in the workbook.

    Unfortunetly, what currently happens is that when the user clicks on the sheet they want to edit they are asked for the username and password (as it is supposed too).

    If this information is correctly supplied then the code goes onto unlock all the yellow areas on that sheet but (and this is the problem) it then goes onto totally unlock all the other sheets, not just the yellow areas as i would like.

    This has driven me mad for ages, please help

    Many thanks
    Attached Files Attached Files
    Last edited by Healthwatch; 02-11-2010 at 05:16 AM.

  2. #2
    Forum Contributor sauerj's Avatar
    Join Date
    11-11-2009
    Location
    Lafayette, IN
    MS-Off Ver
    Excel 2019
    Posts
    163

    Re: using VB code to unlock and lock different areas on multiple sheets

    Got it! ... Two possible fixes:
    1) Option 1 Fix: Comment out "Set Wks = ActiveSheet" (see 1st code below). This line is not necessary, and causes problems as explained below.
    2) Option 2 Fix: Change the ByRef to ByVal in the called procedure "UnLockCells" (see 2nd code below).
    3) Or do BOTH ...

    Explanation as to why the above messes up the desired action:
    1) When "UnLockCells" procedure is called by the "validatePW()" procedure, it sends WS (the worksheet that it wants to UNLOCK the desired cells). But, since this worksheet was read ByRef, this UnLockCells procedure was CHANGING the definition of this WS worksheet to the ActiveSheet, the sheet you are currently sitting on (see the "Set Wks = ActiveSheet" line in this UnLockCells" procedure, OPTION #1 FIX shown below).
    2) Then, when control went back to "validatePW()", WS wasn't the next sheet in the line-up, but instead the ActiveSheet. So, in the next step of "validatePW()", when it tried to Protect the sheet that it just UNLOCKED, well, since WS got changed to the ActiveSheet, it never Protected the next sheets in the line-up, but only the ActiveSheet.
    Take care and best regards!

    Please Login or Register  to view this content.
    Last edited by sauerj; 02-04-2010 at 11:23 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