combo boxes & protecting worksheets

  • Thread starter Thread starter Renee
  • Start date Start date
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
 
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.
 
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 View>Toolbars>Control 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
 
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
 
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
Click to expand...

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?
 
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
 
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
Click to expand...

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.
 
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
 
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
Click to expand...

Thanks, I'll check that out.
 
Back
Top