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
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
Code:
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