MS-Excel: How to show the value of a combo box inside a locked cell.

Question

Background Details I have an excel spreadsheet with Activex dropdown (combobox) objects which help the user to know what options are available. I did this because the data validation list dropdowns are way too small in font size, and were gathering a lot of complaints.

So my solution was to add combobox objects which allow the user to select from a range of options. However, I have to link the comboboxes to a cell with the linkedcell property, so that both the user and various formulas can see what has been chosen. I also set up the combobox to disappear when it's not in use (much in the same way as the data validation dropdown button only appears when you select the relevant cell).

Here is the problem: I don't want the users to edit the value in the linked cell, so I make sure the linked cell is locked whenever the combobox is not selected:

Private Sub comboBox1_GotFocus()
    Call unlockComboBoxTargetCell(comboBox1)
End Sub

the procedure above does this:

 If (targetComboBox.LinkedCell <> "") Then
        Dim targetCell As Variant
        Set targetCell = Range(targetComboBox.LinkedCell)
        If Not targetCell Is Nothing And targetCell.Locked <> False Then
            unlockSheet (activesheet.Name)
            targetCell.MergeArea.Locked = False
            lockSheet (activesheet.Name)
        End If
    End If

Equivalent procedures exist to lock the target cell. However, whenever you do a "Save As" action on the workbook, it seems that the linked and locked cells create a problem: Excel gives this error out of the blue: "The cell or chart you are trying to change is protected and therefore read-only..." This error comes up about twice or three times for each cell that is locked and is the linkedcell for a combobox.

Is there a good way to overcome this problem? Right now my best solution is to leave the cells unlocked and place data validation on the cell, so that if the user edits the cell they will at least be refused when they type something invalid. I could make sure that the combobox covers up the linked cell whenever it is selected, but sometimes that means having a very large, annoying combo box with a very tiny dropdown button on its right side.

Perhaps I am being a bit too particular about the user interface?

Thanks in advance for reading this long and involved post.

Answer 1

In the "lockSheet" procedure you have created, the code to 'protect' the worksheet needs an additional parameter, UserInterfaceOnly, set to true.

I imagine the LockSheet sub is something like this;

` sub lockSheet(strSheetName as string)

thisworkbook.sheets(strSheetName).Protect

end sub `

Try this:

` sub lockSheet(strSheetName as string)

thisworkbook.sheets(strSheetName).Protect, UserInterfaceOnly=True

end sub `

UserInterfaceOnly allows programmatic changes to the protected sheet.

Bill