Combobox becomes inactive when sheet is protected

[odartey]

Hello all,
the script below as been of great help to me. the flexibility of having to double click and type your first two letters and the required text popping up for you.
Thanks to CONTEXTURES.
Now my challenge is when the sheet is protected , the double clicking becomes inactive, ie you don't have access to type your first letters.
I need help in tweeking the script below to allow me
Thanks in advance
Thank you
Odartey

,
Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub TempCombo_KeyDown(ByVal _
  KeyCode As MSForms.ReturnInteger, _
  ByVal Shift As Integer)
'move to next cell on Enter and Tab
Dim varVal As Variant
On Error Resume Next
'change text value to number, if possible
varVal = --ActiveCell.Value
If IsEmpty(varVal) Then
  varVal = ActiveCell.Value
End If
Select Case KeyCode
  Case 9  'tab
  ActiveCell.Value = varVal
  ActiveCell.Offset(0, 1).Activate
  Case 13 'enter
  ActiveCell.Value = varVal
  ActiveCell.Offset(1, 0).Activate
  Case Else
  'do nothing
End Select


End Sub
Private Sub TempCombo_LostFocus()
  With Me.TempCombo
  .Top = 10
  .Left = 10
  .Width = 0
  .ListFillRange = ""
  .LinkedCell = ""
  .Visible = False
  .Value = ""
  End With
End Sub


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set wsList = Sheets("COA")
Set ws = ActiveSheet
ws.Unprotect
Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
  With cboTemp
  .ListFillRange = ""
  .LinkedCell = ""
  .Visible = False
  End With
On Error GoTo errHandler
  If Target.Validation.Type = 3 Then
  Cancel = True
  Application.EnableEvents = False
  str = Target.Validation.Formula1
  With cboTemp
  .Visible = True
  .Left = Target.Left
  .Top = Target.Top
  .Width = Target.Width + 15
  .Height = Target.Height + 5
  .ListFillRange = str
  .LinkedCell = Target.Address
  End With
  cboTemp.Activate
  ws.Protect
  End If

errHandler:
  Application.EnableEvents = True
  Exit Sub
End Sub

 

[PCosta87]

Hi,

Question: Do you need to be able to double click anywhere in the sheet or in a specific cell/range?
If it is the latter you could just leave those cells unprotected which would allow you to double click them when the rest of the sheet is protected.

Hope this helps