Google Answers Logo
View Question
Q: Conditional Locking of cells in Excel (referencing previous question) ( Answered 4 out of 5 stars,   0 Comments )
Subject: Conditional Locking of cells in Excel (referencing previous question)
Category: Computers > Programming
Asked by: mactruk-ga
List Price: $20.00
Posted: 02 Oct 2006 07:43 PDT
Expires: 01 Nov 2006 06:43 PST
Question ID: 770141
My question is similar to the following question asked by uniri and
answered by maniac on June 10:

I am trying to program a macro to lock certain cells based on data
entered on other cells in the same sheet.

My conditional statement is layered. First off, IF cell B1=1, THEN
cells C8:F19 need to be locked and blank (if not already blank). IF
cell B2=2, THEN additionally IF cells B8:B19=1 OR 2, THEN cells C8:F19
need to be locked and blank. Cell B1 contains 2 Option Buttons (radio
buttons), and cells B8:B19 contain Combo Boxes (drop-down menus). The
sheet is called "Bid Comparison" and it is the 2nd sheet in the

Ideally, I need this to be answered within 24 hours, as I have a
meeting in which I will have to demonstrate my spreadsheet's
functionality, but I will accept it at any time that it can be

Thank you for your time.

Request for Question Clarification by maniac-ga on 02 Oct 2006 18:18 PDT
Hello Mactruk,

I am not sure you are watching for clarification requests this evening
(I just saw the question), but if so, please respond.

Normally you would group the two option controls (or radio buttons) -
in that case, only one cell would be referenced (B1 or B2). Please
confirm if this is true (and which cell) or if you intended that B1 =
1 or B2 = 2 are truly the conditions to check.

For cells B8 through B19, do you want the rows to be processed
independently or all together? For example, if B8 is one or two, then
clear / lock cells C8 through C19. If all together, can any value in
B8 through B19 trigger the clear / lock or must all values match 1 or
2 to clear / lock the range.

I may post an answer in any case (due to your 24 hour deadline) but if
I make a wrong assumption I will be glad to fix the answer based on
your clarification.

Subject: Re: Conditional Locking of cells in Excel (referencing previous question)
Answered By: maniac-ga on 02 Oct 2006 20:38 PDT
Rated:4 out of 5 stars
Hello Mactruk,

Due to the time limit, here are a pair of macros that should do the
job you requested.

To set up the workbook, please follow the steps below:

[1] Make sure the worksheet "Bid Comparison" is unprotected. If
needed, use the menu
  Tools -> Protection -> Unprotect Sheet

[2] For each of the cells that can change while the worksheet is protected, use
  Format -> Cells... -> Protection (make sure Locked is NOT checked)
On my sample worksheet, I did this to B1, B8:B19, and C8:F19. If you
have additional cells to unprotect, please do so prior to locking the
worksheet (or selecting the first of the two radio buttons).

[3] Copy the macros (at the end of the answer) into the workbook. You
can do this with the following steps:
 3a. Use the menu
  Tools -> Macro -> Visual Basic Editor
 to bring up Visual Basic.
 3b. Make sure your workbook is selected in the "Project Window"
(upper left) & then use the menu
  Insert -> Module
 and a window should appear titled something like "worksheet - Module1(code).
 3c. Copy & paste the code from below into that window
 3d. Close & Return to Microsoft Excel (the menu name may vary
slightly by version of Excel).

[4] Right click on the top radio buttom (the one over B1) & select
from the pop up menu
  Assign Macro...
and then select Lockem (and OK)

[5] Right click on the second radio button (the one over B2) & select
from the pop up menu
  Assign Macro...
and then select CondLock (and OK)

Click somewhere else on the worksheet to unselect the radio button.

At this point, clicking on either of the two radio buttons should run
the macros (and do as requested in your question).  Both macros leave
the worksheet protected after running. I also assume you've read the
explanation from the other question about how to set your macro
security level (or sign the macro) if needed to make sure the macro is
enabled after the workbook is saved & reopened.

Let me take a moment to explain the code in case you need to make a
minor modification (or make a clarification request and I can fix it
as well).

Lockem is pretty straight forward. It does the following steps when
activated by clicking on the first radio button:
 - unprotects the worksheet
 - clears the values set in the range C8:F19
 - sets the cells to be locked
 - protects the worksheet

CondLock is a little more complicated. It assumes the 1/2 value in
B8:B19 only affects that row. It does the following steps when
activated by clicking on the second radio button:
 - unprotects the worksheet
 - for each row (8 through 19) it does the following steps
  o if the value in the second column (B) and this row is 1 or 2,
clears the values and locks the cells in that row (C:F)
  o if the value is not 1 or 2, unlocks the cells in that row (columns C:F)
 - protects the worksheet

If you have any problem with the code as provided, if any part of the
solution is unclear, or if you need additional information on this
solution, please make a clarification request. I would be glad to help
you further.

Good luck with your work.


Sub Lockem()
'   Macro created by Maniac on 2 October 2006
    Range("C8:F19").Value = ""
    Range("C8:F19").Locked = True

End Sub
Sub CondLock()
'   Macro created by Maniac on 2 October 2006
    For I = 8 To 19
        If (Cells(I, 2).Value = 1) Or (Cells(I, 2).Value = 2) Then
            For J = 3 To 6
                Cells(I, J).Value = ""
                Cells(I, J).Locked = True
            Next J
            For J = 3 To 6
                Cells(I, J).Locked = False
            Next J
        End If
    Next I
End Sub

Request for Answer Clarification by mactruk-ga on 03 Oct 2006 05:38 PDT

Thank you for your prompt and expert attention to my question. You
were absolutely correct in realizing that I linked both option boxes
to cell B1. I mistakenly typed B2 when referring to it having the
value of 2. So no problems there.

Unfortunately, I'm having a minor problem with C8:F19 being locked
even if cells B8:B19 do no contain the values 1 or 2. (i.e. when they
are 3 or 4, they should be unlocked). I'm trying to look at your macro
and determine where the snag is, but I'm not having much success.
Sometimes I can enter data into cells C8:F19 when B8:B19 are values 3
or 4, and sometimes I can't.

Other than that hangup, everything is working correctly. Please see if
you can figure out why those cells are locking up. Thanks a million!

Request for Answer Clarification by mactruk-ga on 03 Oct 2006 05:43 PDT
Also, I think it is important to note that when Condlock is running
(or has run), the Combo Boxes in B8:B19 are able to be modified as
many times as the user wants. So I don't know whether or not once
Condlock has been activated if that means any locked cells in C8:F19
cannot be unlocked. Hope that isn't too confusing...

Clarification of Answer by maniac-ga on 03 Oct 2006 06:18 PDT
Hello Mactruk,

It is certainly feasible to "Assign Macro" to the other controls
(mapped to B8:B19) call CondLock. With the code "as-is", it would
clear / change protection of the cells based solely on the values in
B8:B19 - wrapping an IF statement around the FOR loop (for I) would
probably be needed. Something like
  IF (Cells(1,2).Value = 2) THEN
    [existing code in CondLock]
would add the check that the second radio button had been checked
prior to clearing /locking (or unlocking) the cells in that region. If
the value in B1 was 1, this code would be skipped - leaving the
protections unchanged from the last time Lockem had run.


Request for Answer Clarification by mactruk-ga on 03 Oct 2006 09:36 PDT
Great news! I figured out that if I assign the CondLock macro to each
Combo Box in cells B9:B19, it functions exactly how I need it to. So
basically, my project is done for now (until the bosses need me to add
something more to it!), but the education you've provided will prove
very valuable down the road now that I understand macros and the
VisualBasic aspect of Excel programming.

I'm sure I will return with future questions if I get stuck on a task
that's beyond my comprehension. Thanks for everything!

Request for Answer Clarification by mactruk-ga on 03 Oct 2006 09:42 PDT
your final clarification was precisely what I was able to discover
with a little thought. thanks again!

Clarification of Answer by maniac-ga on 03 Oct 2006 17:09 PDT
Hello Mactruk,

I was glad that everything worked out OK. Thanks for the kind words.

mactruk-ga rated this answer:4 out of 5 stars and gave an additional tip of: $5.00
The explanation of the code really helped me understand the
functionality of the macros in Excel. I am very satisfied with the
answer I received and will definitely return to use Google Answers
whenever I have a complex problem that needs to be solved. The effort
to answer my question within my suggested time frame is very

There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy