how can I lock/unlock a range of cells depending on state of a checkbox?
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. |
how can I lock/unlock a range of cells depending on state of a checkbox?
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 |
how can I lock/unlock a range of cells depending on state of a checkbox?
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 |
how can I lock/unlock a range of cells depending on state of a checkbox?
After serious thinking reznor9 wrote :[color=blue][i]
'GS[_2_ 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 |
All times are GMT +1. The time now is 01:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com