Active X ComboBox not working when Sheet is Protected

[goldman65]

Hi guys,

I'm at a loss as to why my Active X Combobox won't work even after unlocking all the cells the combobox is referencing and inserting unprotect and protect code in VBA editor. Here is the code:

Private Sub Worksheet_BeforeDoubleClick _  (ByVal Target As Range, _
    Cancel As Boolean)
Sheet9.Unprotect Password:="1234"
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet


Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
 ' With cboTemp
  'clear and hide the combo box
   ' .ListFillRange = ""
   ' .LinkedCell = ""
   ' .Visible = False
 ' End With
On Error GoTo errHandler
  If Target.Validation.Type = 3 Then
    'if the cell contains
      'a data validation list
    Cancel = True
    Application.EnableEvents = False
    'get the data validation formula
    str = Target.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
      'show the combobox with the list
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 15
      .Height = Target.Height + 5
      .ListFillRange = "Snacks"
      '.ListFillRange = "Lunch"
      '.ListFillRange = "Dinner"
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
    'open the drop down list automatically
    Me.TempCombo.DropDown
    'Me.TempCombo1.DropDown
    'Me.TempCombo2.DropDown
  End If
  
errHandler:
  Application.EnableEvents = True
  Exit Sub
Sheet9.Protect Password:="1234"
End Sub
'=========================================
Private Sub TempCombo_LostFocus()
Sheet9.Unprotect Password:="1234"
  With Me.TempCombo
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = "Snacks"
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
Sheet9.Protect Password:="1234"
End Sub
 '====================================
'Optional code to move to next cell
'if Tab or Enter are pressed
'from code by Ted Lanham
'***NOTE: if KeyDown causes problems,
'change to KeyUp
'Table with numbers for other keys
'such as Right Arrow (39)
'https://msdn.microsoft.com/en-us/
 ' library/aa243025%28v=vs.60%29.aspx


Private Sub TempCombo_KeyDown(ByVal _
     KeyCode As MSForms.ReturnInteger, _
     ByVal Shift As Integer)
Sheet9.Unprotect Password:="1234"
Select Case KeyCode
    Case 9 'Tab
      ActiveCell.Offset(0, 1).Activate
    Case 13 'Enter
      ActiveCell.Offset(1, 0).Activate
    Case Else
        'do nothing
  End Select
Sheet9.Protect Password:="1234"
End Sub

If anyone can offer some solutions, I would be greatly appreciative.

Kind regards,

Steve

 

[goldman65]

Hi guys,

For those interested, I solved my own problem. All I had to do was tick Edit Scenarios and Edit Objects whilst protecting sheet.

 

[MiggyShi]

Hi guys,

For those interested, I solved my own problem. All I had to do was tick Edit Scenarios and Edit Objects whilst protecting sheet.

how did you solve this? please share.