Hi,
Is there a way to lockdown certain cells with VBA without protecting the worksheet.
The reason I can't protect the worksheet is because it contains code on double click for a combobox drop down on some of the columns and every time I protect the sheet the combobox stops working. (The subs for this are below for reference)
The cells I need to lock down are
A1 to V1
B4:110
I understand from what I've read in various searches that without protecting the sheet a user could possibly change a 'locked' cell but all I am trying to do is protect a formula in the cells that they shouldn't even need to go into.
I would be very grateful for any advice or solutions offered.
Many thanks
Is there a way to lockdown certain cells with VBA without protecting the worksheet.
The reason I can't protect the worksheet is because it contains code on double click for a combobox drop down on some of the columns and every time I protect the sheet the combobox stops working. (The subs for this are below for reference)
The cells I need to lock down are
A1 to V1
B4:110
I understand from what I've read in various searches that without protecting the sheet a user could possibly change a 'locked' cell but all I am trying to do is protect a formula in the cells that they shouldn't even need to go into.
I would be very grateful for any advice or solutions offered.
Many thanks
Code:
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("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 + 5
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
'open the drop down list automatically
Me.TempCombo.DropDown
End If
errHandler:
Application.EnableEvents = True
Exit Sub
End Sub
Private Sub TempCombo_LostFocus()
With Me.TempCombo
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End Sub