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.
--Maniac
Sub Lockem()
'
' Macro created by Maniac on 2 October 2006
'
ActiveSheet.Unprotect
Range("C8:F19").Value = ""
Range("C8:F19").Locked = True
ActiveSheet.Protect
End Sub
Sub CondLock()
'
' Macro created by Maniac on 2 October 2006
'
ActiveSheet.Unprotect
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
Else
For J = 3 To 6
Cells(I, J).Locked = False
Next J
End If
Next I
ActiveSheet.Protect
End Sub |
Request for Answer Clarification by
mactruk-ga
on
03 Oct 2006 05:38 PDT
Maniac,
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!
|
,
0 Comments
)