reznor9 formulated on Thursday :
I have a checkbox in C15 which is linked to AC1.
underneath it in the range C19:L24 I have colums and rows of info the
user can input data into.
Now I want that range of cells to lock if the checkbox is
unchecked(FALSE) and to unlock if the checkbox is checked(TRUE).
In the same sheet I have the exact same setup where the checkbox is in
O15 and linked to AC2.
and the range I wish to lock/unlock with that checkbox is O19:X24
How can I accomplish this?
Thank you in advance.
You can do this via VBA in the checkbox_Change event. You will also
need to manage sheet protection in the Sheet_Activate event so it
resets protection whenever that sheet is activated. Here you need to
explicitly set the "UserInterfaceOnly" option to True so the code in
your checkbox controls can make changes without having to toggle
protection. This will give the desired results so the target ranges
operate independantly in sync with their respective checkbox.
In the Sheet_Activate event:
With ActiveSheet
.Unprotect Password:=""
.Protect Password:="", UserInterfaceOnly = True
End With 'ActiveSheet
In the Change event for checkbox in C15: (Assumes "Checkbox1")
Range("$C$19:$L$24").Locked = (Not Checkbox1.Value)
In the Change event for checkbox in O15: (Assumes "Checkbox2")
Range("$O$19:$X$24").Locked = (Not Checkbox2.Value)
Right click the sheet tab and choose 'View code' from the popup menu.
Right click each checkbox and choose 'View code' from the popup menu.
Event procedures are located in the dropdown to the right above the
code window. Click on the appropriate one and past the code above into
the empty procedure.
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion