VBA not working while sheet is protected. ComboBox can't popup.

[leftikos]

Hi Guys,

I simplified one of my templates to show you where I’m stucked.
My Private Sub is not working while sheet is protected. I’ve tried several options but with no success.

ThisWorkbook.Sheets("MCNrequest").Protect userinterfaceonly:=True

Private Sub Workbook_Open()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect Password:="Secret", UserInterFaceOnly:=True
Next wSheet
End Sub

and others…

How my Private sub is working:
Each cell which contains Data Validation List turns to ComboBox after double click. As you might now ComboBox is much better for huge list and you’re able to use search as u type.
I want to keep my sheet protected because rest of cells contains formulas such vlookup, and I don’t want let user to delete them or play with it.
When is sheet protected, after double click spinning wheel occurred and ComboBox won’t popup.

Any chance that you can advise where in code should I add “unprotect formula” or how to make it happen?
Thanks in advance.

Please find and example below. Password to unprotect is "mcn"

https://drive.google.com/file/d/0B0s-0_lS3QrnSHpOc2tydVZuWGs/edit?usp=sharing

Private Sub ComboBox1_Change()
ComboBox1.ListFillRange = "customer_name"
ComboBox1.DropDown
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
Set ws = ActiveSheet

Set cboTemp = ws.OLEObjects("ComboBox1")
  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 + 5
      .Height = Target.Height + 5
      .ListFillRange = str
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
    'open the drop down list automatically
    Me.ComboBox1.DropDown

  End If
  
errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub
'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = True

If Application.CutCopyMode Then
  'allow copying and pasting on the worksheet
  GoTo errHandler
End If

Set cboTemp = ws.OLEObjects("ComboBox1")
  On Error Resume Next
  With cboTemp
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With

errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub

 

[FirstTimer]

Hi, I joined the other day in hopes of finding a solution to the exact same problem you are having (even the code is the same) but after searching found nothing that would help. I was also sad to see that no one had responded to help you. Since I could not find anything I decided to tackle it on my own and actually found the solution right in front of me (by chance) and it did not take special code to do it.
Create your spreadsheet, lock and unlock all the cells you require. create your combobox, you do not need to lock this column. Now select Protect Sheet, before putting in your confirmation password make sure you go through the password menu, select/check "Select unlocked cells", and the big one is select "Edit Objects" near the bottom, now put in your confirmation password. the combobox is now protected and when double clicking on it it will open like it should. When protected your mouse will only float over the locked cells giving you piece of mind that no formulas or data can be changed.
I hope this helps you or anyone else having the same problem.

 

[JohnBB]

Still helping ppl. Thanks!