Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combo boxes & protecting worksheets
How can I protect a worksheet and still make my combo boxes work?
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combo boxes & protecting worksheets
If you mean a cell with a dropdown data validation list:
- select that cell - right-click and select Format Cells - on the Protection tab, make sure Locked is unchecked If you mean a combobox from the Control Toolbox toolbar: - set the Locked property of the combobox to False - make sure the linked cell is not locked (same steps as for the data validation dropdown above) If you mean a combobox from the Forms toolbar: - right-click on the combobox and select Format Control - on the Protection tab, make sure Locked is unchecked - make sure the linked cell is not locked (same steps as for the data validation dropdown above) Hope this helps, Hutch "Renee" wrote: How can I protect a worksheet and still make my combo boxes work? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combo boxes & protecting worksheets
Not really. So here's my next challenge question. Is it possible, and if
so, how, to link the combo box in its properties to a cell in another worksheet in the same workbook? Here's what I've got: I've got two combo boxes that link to their own respective cells, but when I go to protect the worksheet and then try using the drop-down list combo boxes, I get, "The cell or chart that you are trying to change is protected and therefore read-only...." I'm thinking if I can put them on another worksheet, I can hide the worksheet, but I just don't know how to do the link in the properties. By the way, I have MS Excel 2007. Any help would be grateful. Thanks. "Tom Hutchins" wrote: If you mean a cell with a dropdown data validation list: - select that cell - right-click and select Format Cells - on the Protection tab, make sure Locked is unchecked If you mean a combobox from the Control Toolbox toolbar: - set the Locked property of the combobox to False - make sure the linked cell is not locked (same steps as for the data validation dropdown above) If you mean a combobox from the Forms toolbar: - right-click on the combobox and select Format Control - on the Protection tab, make sure Locked is unchecked - make sure the linked cell is not locked (same steps as for the data validation dropdown above) Hope this helps, Hutch "Renee" wrote: How can I protect a worksheet and still make my combo boxes work? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combo boxes & protecting worksheets
You can only link one cell to a combo box placed directly on a
worksheet, even if you specify a range as a linked cell. It's not efficient to use a combo box just to display one item. But you can link to any cell in the workbook, even if it's on another sheet. 1. Go to ViewToolbarsControl Toolbox 2. Press "Design mode" icon and double click the combo box 3. Press F4 to show the Properties window (if not already displayed) 4. Scroll down to "Linked Cell" and type in your cell reference, ex: Sheet12!$A$1 5. Press Alt-Q to return to Excel, click "Design Mode" again to exit design mode and test the combo box ps- Did you see the bit about "make sure the linked cell is not locked"? You need to un-protect the worksheet (if needed), select the cells that are linked to the combo box, press Ctrl-1 and uncheck "Locked" and "Hidden" on those cells, then re-protect the worksheet. HTH, JP On Mar 18, 6:53*pm, Renee wrote: Not really. *So here's my next challenge question. *Is it possible, and if so, how, to link the combo box in its properties to a cell in another worksheet in the same workbook? Here's what I've got: *I've got two combo boxes that link to their own respective cells, but when I go to protect the worksheet and then try using the drop-down list combo boxes, I get, "The cell or chart that you are trying to change is protected and therefore read-only...." I'm thinking if I can put them on another worksheet, I can hide the worksheet, but I just don't know how to do the link in the properties. By the way, I have MS Excel 2007. Any help would be grateful. Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combo boxes & protecting worksheets
If you are getting that error message, then you didn't unlock the linked cell
before protecting the sheet. For each combobox: - select its linked cell - right-click and select Format Cells - on the Protection tab, make sure Locked is unchecked - click OK Hutch "Renee" wrote: Not really. So here's my next challenge question. Is it possible, and if so, how, to link the combo box in its properties to a cell in another worksheet in the same workbook? Here's what I've got: I've got two combo boxes that link to their own respective cells, but when I go to protect the worksheet and then try using the drop-down list combo boxes, I get, "The cell or chart that you are trying to change is protected and therefore read-only...." I'm thinking if I can put them on another worksheet, I can hide the worksheet, but I just don't know how to do the link in the properties. By the way, I have MS Excel 2007. Any help would be grateful. Thanks. "Tom Hutchins" wrote: If you mean a cell with a dropdown data validation list: - select that cell - right-click and select Format Cells - on the Protection tab, make sure Locked is unchecked If you mean a combobox from the Control Toolbox toolbar: - set the Locked property of the combobox to False - make sure the linked cell is not locked (same steps as for the data validation dropdown above) If you mean a combobox from the Forms toolbar: - right-click on the combobox and select Format Control - on the Protection tab, make sure Locked is unchecked - make sure the linked cell is not locked (same steps as for the data validation dropdown above) Hope this helps, Hutch "Renee" wrote: How can I protect a worksheet and still make my combo boxes work? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combo boxes & protecting worksheets
On Mar 19, 8:20 am, Tom Hutchins
wrote: If you are getting that error message, then you didn't unlock the linked cell before protecting the sheet. For each combobox: - select its linked cell - right-click and select Format Cells - on the Protection tab, make sure Locked is unchecked - click OK Hutch "Renee" wrote: Not really. So here's my next challenge question. Is it possible, and if so, how, to link the combo box in its properties to a cell in another worksheet in the same workbook? Here's what I've got: I've got two combo boxes that link to their own respective cells, but when I go to protect the worksheet and then try using the drop-down list combo boxes, I get, "The cell or chart that you are trying to change is protected and therefore read-only...." I'm thinking if I can put them on another worksheet, I can hide the worksheet, but I just don't know how to do the link in the properties. By the way, I have MS Excel 2007. Any help would be grateful. Thanks. "Tom Hutchins" wrote: If you mean a cell with a dropdown data validation list: - select that cell - right-click and select Format Cells - on the Protection tab, make sure Locked is unchecked If you mean a combobox from the Control Toolbox toolbar: - set the Locked property of the combobox to False - make sure the linked cell is not locked (same steps as for the data validation dropdown above) If you mean a combobox from the Forms toolbar: - right-click on the combobox and select Format Control - on the Protection tab, make sure Locked is unchecked - make sure the linked cell is not locked (same steps as for the data validation dropdown above) Hope this helps, Hutch "Renee" wrote: How can I protect a worksheet and still make my combo boxes work? So, there is no way to truly stop a user from screwing up the entry? I've been trying to get Validation or Combobox to do this for me, but it seems that either way there has to be an unprotected cell. Is there an option to force someone to choose from a specific list? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combo boxes & protecting worksheets
If you want the user to enter a value into a cell - whether by direct input,
choosing a value from a dropdown list, or as the result of running a macro -that cell will have to be unlocked (although the macro can unprotect the sheet, put the data into the cell, and re-protect the sheet in a split second). With data validation, the cell is unlocked but the user is limited to what can be entered, and if the sheet is protected, the user can't remove the validation. With a combobox or listbox control, only the linked cell has to be unprotected. It can be in a hidden row or column (which the user can't unhide) or on another (hidden) sheet. You could even add data validation to the linked cell to make sure the user doesn't enter an incorrect value into it. Hutch " wrote: On Mar 19, 8:20 am, Tom Hutchins wrote: If you are getting that error message, then you didn't unlock the linked cell before protecting the sheet. For each combobox: - select its linked cell - right-click and select Format Cells - on the Protection tab, make sure Locked is unchecked - click OK Hutch "Renee" wrote: Not really. So here's my next challenge question. Is it possible, and if so, how, to link the combo box in its properties to a cell in another worksheet in the same workbook? Here's what I've got: I've got two combo boxes that link to their own respective cells, but when I go to protect the worksheet and then try using the drop-down list combo boxes, I get, "The cell or chart that you are trying to change is protected and therefore read-only...." I'm thinking if I can put them on another worksheet, I can hide the worksheet, but I just don't know how to do the link in the properties. By the way, I have MS Excel 2007. Any help would be grateful. Thanks. "Tom Hutchins" wrote: If you mean a cell with a dropdown data validation list: - select that cell - right-click and select Format Cells - on the Protection tab, make sure Locked is unchecked If you mean a combobox from the Control Toolbox toolbar: - set the Locked property of the combobox to False - make sure the linked cell is not locked (same steps as for the data validation dropdown above) If you mean a combobox from the Forms toolbar: - right-click on the combobox and select Format Control - on the Protection tab, make sure Locked is unchecked - make sure the linked cell is not locked (same steps as for the data validation dropdown above) Hope this helps, Hutch "Renee" wrote: How can I protect a worksheet and still make my combo boxes work? So, there is no way to truly stop a user from screwing up the entry? I've been trying to get Validation or Combobox to do this for me, but it seems that either way there has to be an unprotected cell. Is there an option to force someone to choose from a specific list? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combo boxes & protecting worksheets
On Mar 20, 10:26 pm, Tom Hutchins
wrote: If you want the user to enter a value into a cell - whether by direct input, choosing a value from a dropdown list, or as the result of running a macro -that cell will have to be unlocked (although the macro can unprotect the sheet, put the data into the cell, and re-protect the sheet in a split second). With data validation, the cell is unlocked but the user is limited to what can be entered, and if the sheet is protected, the user can't remove the validation. With a combobox or listbox control, only the linked cell has to be unprotected. It can be in a hidden row or column (which the user can't unhide) or on another (hidden) sheet. You could even add data validation to the linked cell to make sure the user doesn't enter an incorrect value into it. Hutch Thanks. The problem with data validation is that its error protection only extends to direct keyboard input. A user can hit DEL to blank it out or copy another unprotected cell into the validated cell and thereby get around the validation. I guess I'll have to use combobox and hide the unprotected cell as the best solution. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combo boxes & protecting worksheets
If you want to keep the user from pasting to B10 on Sheet1 (for example), you
could use: Private Sub Workbook_Activate() Application.CutCopyMode = None End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name < "Sheet1" Then Exit Sub If Intersect(Target, Range("B10")) Is Nothing Then Exit Sub Application.CutCopyMode = None End Sub This code would be pasted into the ThisWorkbook module of the workbook. This could be expanded for multiple cells. Hutch " wrote: On Mar 20, 10:26 pm, Tom Hutchins wrote: If you want the user to enter a value into a cell - whether by direct input, choosing a value from a dropdown list, or as the result of running a macro -that cell will have to be unlocked (although the macro can unprotect the sheet, put the data into the cell, and re-protect the sheet in a split second). With data validation, the cell is unlocked but the user is limited to what can be entered, and if the sheet is protected, the user can't remove the validation. With a combobox or listbox control, only the linked cell has to be unprotected. It can be in a hidden row or column (which the user can't unhide) or on another (hidden) sheet. You could even add data validation to the linked cell to make sure the user doesn't enter an incorrect value into it. Hutch Thanks. The problem with data validation is that its error protection only extends to direct keyboard input. A user can hit DEL to blank it out or copy another unprotected cell into the validated cell and thereby get around the validation. I guess I'll have to use combobox and hide the unprotected cell as the best solution. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combo boxes & protecting worksheets
On Mar 24, 10:07 am, Tom Hutchins
wrote: If you want to keep the user from pasting to B10 on Sheet1 (for example), you could use: Private Sub Workbook_Activate() Application.CutCopyMode = None End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name < "Sheet1" Then Exit Sub If Intersect(Target, Range("B10")) Is Nothing Then Exit Sub Application.CutCopyMode = None End Sub This code would be pasted into the ThisWorkbook module of the workbook. This could be expanded for multiple cells. Hutch " wrote: On Mar 20, 10:26 pm, Tom Hutchins wrote: If you want the user to enter a value into a cell - whether by direct input, choosing a value from a dropdown list, or as the result of running a macro -that cell will have to be unlocked (although the macro can unprotect the sheet, put the data into the cell, and re-protect the sheet in a split second). With data validation, the cell is unlocked but the user is limited to what can be entered, and if the sheet is protected, the user can't remove the validation. With a combobox or listbox control, only the linked cell has to be unprotected. It can be in a hidden row or column (which the user can't unhide) or on another (hidden) sheet. You could even add data validation to the linked cell to make sure the user doesn't enter an incorrect value into it. Hutch Thanks. The problem with data validation is that its error protection only extends to direct keyboard input. A user can hit DEL to blank it out or copy another unprotected cell into the validated cell and thereby get around the validation. I guess I'll have to use combobox and hide the unprotected cell as the best solution. Thanks, I'll check that out. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protecting Check Boxes to prevent editing | Excel Discussion (Misc queries) | |||
Combo Boxes | Excel Discussion (Misc queries) | |||
Selecting subsets using combo boxes or list boxes | Excel Discussion (Misc queries) | |||
Questions on combo boxes and list boxes. | New Users to Excel | |||
2 combo boxes | Excel Worksheet Functions |