PDA

View Full Version : Combobox vs protected sheets



Dabo
05-28-2009, 05:51 AM
Hi all,

I'm currently struggling with that :

I have sheets with a lot of comboboxes, their links cells are protected so that the user cannot change their name range etc.
As the link cells are protected, I cannot change the value of the combobox...

Who has a idea ?

mikerickson
05-28-2009, 06:26 AM
Select a linked cell, Format the cell and on the Protection tab there is a checkbox marked "Locked"; Un-check that box.

Dabo
05-28-2009, 06:28 AM
In fact I want the link cell to be protected against manual input, not against combobox input

Oorang
05-28-2009, 06:37 AM
Well, you can either unlock the linked cell (Format Cells>Protection). So the rest of the sheet stays protected but the combobox is editable, or you can go to an ActiveX combobox (View>Toolbars>Control Toolbox) and use a programmatic solution:

'Place this in the "ThisWorkbook" module:
Private Sub Workbook_Open()
Sheet1.ComboBox1.AddItem "Foo"
Sheet1.ComboBox1.AddItem "Bar"
Sheet1.ComboBox1.AddItem "Baz"
End Sub

'Place this in the Worksheet module that has the combobox:
Private Sub ComboBox1_Change()
Dim blnPrtMd As Boolean
blnPrtMd = Me.ProtectionMode
If blnPrtMd Then Me.Unprotect
Me.Cells(1, 1).VALUE = Me.ComboBox1.ListIndex + 1&
If blnPrtMd Then Me.Protect
End Sub

mikerickson
05-28-2009, 06:45 AM
In fact I want the link cell to be protected against manual input, not against combobox input
Hide the row that the linked cell is on. When the sheet is protected the user can't unhide the row.