nt1701
09-29-2018, 06:05 PM
Hello VBA Experts,
I am trying to debug an Excel VBA script that I wrote, and I am stuck. I have a drop-down menu in cell E11 and based on the option selected I wish to change the interior color (to gray) and lock-out cell range G21:H24. The worksheet is password protected and so I account for this in my VBA script. I am getting a variety of run-time errors each time I test the worksheet. Any ideas as to what is wrong with my code? Thanks.
I am using Excel 2010 and the code below uses the Worksheet object and the Change procedure:
Private Sub Worksheet_Change(ByVal Target As Range)
If Left(ActiveSheet.Cells(11, 5).Text, 7) = "Track D" Then
ActiveSheet.Unprotect "opt123"
ActiveSheet.Range(Cells(21, 7), Cells(24, 8)).Locked = False
ActiveSheet.Range(Cells(21, 7), Cells(24, 8)).Interior.ColorIndex = 16
ActiveSheet.Protect "opt123", True, True, False, True, True
Else
ActiveSheet.Unprotect "opt123"
ActiveSheet.Range(Cells(21, 7), Cells(24, 8)).Locked = False
ActiveSheet.Range(Cells(21, 7), Cells(24, 8)).ClearContents
ActiveSheet.Range(Cells(21, 7), Cells(24, 8)).Interior.ColorIndex = 16
ActiveSheet.Range(Cells(21, 7), Cells(24, 8)).Locked = True
ActiveSheet.Protect "opt123", True, True, False, True, True
End If
End Sub
I am trying to debug an Excel VBA script that I wrote, and I am stuck. I have a drop-down menu in cell E11 and based on the option selected I wish to change the interior color (to gray) and lock-out cell range G21:H24. The worksheet is password protected and so I account for this in my VBA script. I am getting a variety of run-time errors each time I test the worksheet. Any ideas as to what is wrong with my code? Thanks.
I am using Excel 2010 and the code below uses the Worksheet object and the Change procedure:
Private Sub Worksheet_Change(ByVal Target As Range)
If Left(ActiveSheet.Cells(11, 5).Text, 7) = "Track D" Then
ActiveSheet.Unprotect "opt123"
ActiveSheet.Range(Cells(21, 7), Cells(24, 8)).Locked = False
ActiveSheet.Range(Cells(21, 7), Cells(24, 8)).Interior.ColorIndex = 16
ActiveSheet.Protect "opt123", True, True, False, True, True
Else
ActiveSheet.Unprotect "opt123"
ActiveSheet.Range(Cells(21, 7), Cells(24, 8)).Locked = False
ActiveSheet.Range(Cells(21, 7), Cells(24, 8)).ClearContents
ActiveSheet.Range(Cells(21, 7), Cells(24, 8)).Interior.ColorIndex = 16
ActiveSheet.Range(Cells(21, 7), Cells(24, 8)).Locked = True
ActiveSheet.Protect "opt123", True, True, False, True, True
End If
End Sub