Getting Debug Error and don't know why

[dougdrex]

Using Excel 2010

I'm getting debug errors and can't figure out why. There is VBA code in the background, and I've searched through the code but don't see any obvious errors.

It seems like the debug error only comes up when the sheet is protected. Is there a problem with VBA running while a sheet is protected?

Thanks in advance for your insight!
Doug

 

[Joe4]

It seems like the debug error only comes up when the sheet is protected. Is there a problem with <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> running while a sheet is protected?

It depends on exactly what the VBA code is trying to do, but if it is doing something like trying to update a cell in a Protected range, yes, that would cause errors.

Often times, if you hit the "Debug" button in the error window, it will take you right to the line of code that is having issues.

 

[Smitty]

You can unprotect/reprotect the sheet(s) in your code:

Sheets("Sheet1").Protect
  ' Your code here
Sheets("Sheet1").Unprotect

HTH,

 

[dougdrex]

I used a range in the code. Some of the cells are locked, while others are not. I thought it would just be easier that way. Let me know if I need to alter the code to only include cells that are unprotected.

Private Sub Worksheet_Change(ByVal Target As Range)[INDENT]If Not Intersect(Target, Target.Worksheet.Range("C17")) Is Nothing Then[/INDENT]
        If Range("C17") = "YES" Or Range("C17") = "NO" Or Range("C17") = "N/A" Or Range("C17") = vbNullString Then
            Range("C18:D26").Value = vbNullString[INDENT]        End If[/INDENT]
    End If

 

[Joe4]

The VBA is definitely updating values certain values, especially with this line:

Range("C18:D26").Value = vbNullString

You will need to unprotect the range before you can update it.
Smitty's post show show you can protect/unprotect ranges via VBA code.
Here is a link to some code that shows you to protect/unprotect in VBA when passwords are involved: Excel-VBA : Range - Lock/Unlock Specific Cells

 

[Smitty]

If C18:D26 are locked then:

ActiveSheet.Unprotect
  Range("C18:D26").Value = vbNullString
ActiveSheet.Protect

 

[dougdrex]

Thank you both for your help. I'm learning about using VBA, and appreciate your valuable feedback.

Doug