Protect Workbook (Lock Certain Cells) and still have VBA run on unlocked cells??

[Canadian911Guy]

So, next problem:
On the linked Workbook, I want to LOCK the following:

• Rows 1, 2 & 3
• Columns A, J, K, M, O, P & R
• The remaining Columns must be UNLOCKED.
  • Now, I know how to do this, and it works perfectly, except... None of the VBA works when I lock it.

So, what am I missing in my VBA code to make the VBA code work ONLY on the unlocked cells?

Thank you in advance!

FILE IS HERE

This is the code:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim strList As String
On Error Resume Next
Application.EnableEvents = False

    Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo exitHandler

    If rngDV Is Nothing Then GoTo exitHandler

    If Not Intersect(Target, rngDV) Is Nothing Then
        If Target.Validation.Type = 3 Then
            strList = Target.Validation.Formula1
            strList = Right(strList, Len(strList) - 1)
            strDVList = strList
            frmDVList.Show
        If Target.Value = "HIGH" Then
            Call PostMitChoice_Initialize
        End If
        If Target.Value = "CATASTROPHIC" Then
            Call PostMitChoice_Initialize
        End If
      End If
   End If
exitHandler:
  Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change1(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim strSep As String
strSep = ", "
  Application.EnableEvents = False
On Error Resume Next
If Target.Count > 1 Then GoTo exitHandler

Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
   'do nothing
Else
  newVal = Target.Value
  Application.Undo
  oldVal = Target.Value
  Target.Value = newVal
   If newVal = "" Then
      'do nothing
   Else
         If oldVal = "" Then
            Target.Value = newVal
         Else
            Target.Value = oldVal & strSep & newVal
         End If
    End If
End If
exitHandler:
  Application.EnableEvents = True
End Sub

Private Sub PostMitChoice_Initialize()
    Load PostMitChoice
    PostMitChoice.Show
End Sub

 

[Michael M]

Keep in mind you can't have 2 change events in the one sheet module, but it would
Probably easier to Unprotect the sheet at the beginning of the code and Protect again at the end!

 

[Canadian911Guy]

Keep in mind you can't have 2 change events in the one sheet module

Well, the code as written works perfectly, so two change events or not, it works.

I tried to unprotect the sheet at the beginning and then protect it again at the end, but the problem is it unlocks ALL cells when I do that, and I can't have that. I need to have the VBA code work only on the unlocked cells while the locked cells remain uneditable.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Protect Workbook (Lock Certain Cells) and still have VBA run on unlocked cells??
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Canadian911Guy]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Protect Workbook (Lock Certain Cells) and still have VBA run on unlocked cells??
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Apologies. Thank you.

 

[Fluff]

Try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim strList As String
On Error Resume Next
Application.EnableEvents = False

    On Error GoTo exitHandler
        If Target.Validation.Type = 3 Then
            strList = Target.Validation.Formula1
            strList = Right(strList, Len(strList) - 1)
            strDVList = strList
            frmDVList.Show
        If Target.Value = "HIGH" Then
            Call PostMitChoice_Initialize
        End If
        If Target.Value = "CATASTROPHIC" Then
            Call PostMitChoice_Initialize
        End If
      End If
exitHandler:
  Application.EnableEvents = True
End Sub

You can also delete the

Private Sub Worksheet_Change1(ByVal Target As Range)

routine as it's not doing anything.

 

[Canadian911Guy]

That worked perfectly!! Thank you!!

 

[Fluff]

Glad we could help & thanks for the feedback.