how can I lock/unlock a range of cells depending on state of a checkbox?

[reznor9]

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 th
user can input data into.
Now I want that range of cells to lock if the checkbox i
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 i
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

 

[GS]

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

 

[GS]

GS presented the following revisions...

In the Change event for checkbox in C15: (Assumes "Checkbox1")

Range("$C$19:$L$24").Locked = (Not Me.Checkbox1.Value)

In the Change event for checkbox in O15: (Assumes "Checkbox2")

Range("$O$19:$X$24").Locked = (Not Me.Checkbox2.Value)

...where I forgot to use a fully qualified ref to the parent of the
controls.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[reznor9]

;1608295']reznor9 formulated on Thursday :

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

Im a little lost here. I can right click the sheet tab and view code..
but when I return to the spreadsheet and right click each checkbox i
doesnt show an option for view code. Im right clicking the checkbox fro
the excel sheet... am I supposed to be doing it from elsewhere because
cant find the checkbox or a reference to them on the VB Code page? I
somewhat new to this, so please have patience.
Thank you for your hel

 

[GS]

After serious thinking reznor9 wrote :

;1608295']reznor9 formulated on Thursday :

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

Im a little lost here. I can right click the sheet tab and view code...
but when I return to the spreadsheet and right click each checkbox it
doesnt show an option for view code. Im right clicking the checkbox from
the excel sheet... am I supposed to be doing it from elsewhere because I
cant find the checkbox or a reference to them on the VB Code page? Im
somewhat new to this, so please have patience.
Thank you for your help

My instructions assume you're using checkboxes from the Control
Toolbox, NOT the Forms controls toolbar. If the latter then I recommend
you change to using the former, otherwise it's going to be a bit more
compicated than I suspect you want because you can only 'Assign macros'
to Forms controls and so will require a standard code module and fully
qualified refs to each control so there's no ambiguity which control
gets which macro.

Using controls from the Control Toolbox is much easier to code for, and
clicking 'View code' from the right-click menu automatically puts you
where you need to be in the code window.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion