Getting Debug Error and don't know why

dougdrex

Board Regular
Joined
Sep 27, 2013
Messages
79
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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,067
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
May 15, 2003
Messages
29,536
You can unprotect/reprotect the sheet(s) in your code:

Code:
Sheets("Sheet1").Protect
  ' Your code here
Sheets("Sheet1").Unprotect
HTH,
 

dougdrex

Board Regular
Joined
Sep 27, 2013
Messages
79
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.

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,067
Office Version
365
Platform
Windows
The VBA is definitely updating values certain values, especially with this line:
Code:
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

MrExcel MVP
Joined
May 15, 2003
Messages
29,536
If C18:D26 are locked then:

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

dougdrex

Board Regular
Joined
Sep 27, 2013
Messages
79
Thank you both for your help. I'm learning about using VBA, and appreciate your valuable feedback.

Doug
 

Forum statistics

Threads
1,089,314
Messages
5,407,519
Members
403,151
Latest member
floydschoice

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top