0

I have a protected form that organizes user input from open cells onto another excel sheet and emails it to specific people. This form has worked flawlessly for years, but suddenly yesterday it's throwing a Run-time error '1004': Application-defined or object-defined error. When I enter anything into the form, the error appears immediately, and the debugger highlights the following line of code. 

Range("AL6").Font.Color = vbWhite

On the form where a user adds their information, the cells that are open to users range from AF6-AK6 and auto-populate tomorrows date. AL6 is outside this and protected. There is nothing in cell 'AL6' and nor should there be. This is the code block in which the error dwells.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
       If Time > 15 / 24 And Range("AF6") = Application.WorksheetFunction.WorkDay(Date, 0) Then
           Range("AL6").Font.Color = vbRed
       Else
           Range("AL6").Font.Color = vbWhite
       End If

 End Sub

The form is also saved regularly, so I don't think it's a matter of duplicating itself and becoming corrupted. I also cannot rule that possibility out. Any thoughts?

BetO
  • 47
  • 4
  • worksheet odule or regular module? (dumb question iknow but have to ask) – Doug Coats Sep 18 '18 at 18:19
  • 2
    also if its supposed to go to another sheet maybe its not qualified fully? – Doug Coats Sep 18 '18 at 18:20
  • 1
    I was able to reproduce the error when the sheet is protected if "AL6" is locked. Is there any other related code that may be unlocking the sheet during this edit, which may not be functioning properly? – Valon Miller Sep 18 '18 at 19:12
  • @DougCoats, I use a module (regular?) for the protected sheet to collect all the users data, this error is occurring on a ThisWorkBook sheet set of code if that makes sense? It's been working with zero trouble for a good two years, unless sheets can become unqualified themselves, I don't know if that would be the issue outside an update. – BetO Sep 18 '18 at 19:23
  • @ValonMiller I'm not the only person who has access to his sheet, many people do. Could unlocking the cell and leaving it create the error problem? Nothing in the code references this cell beyond the code section that contains the error. – BetO Sep 18 '18 at 19:30
  • you need to unlock the sheet, edit the range and then relock sheet. Simple solution :D. Also FULLY QUALIFY EVERYTHING i.e. thisworkbook.sheets("blahblahblah").Range("yaddayaddaYadda").value="BLERG" – Doug Coats Sep 18 '18 at 19:53
  • It's hard to speculate on the why, but it seems to be caused by the sheet locking. You might try implementing more explicit locking logic via the Workbook_Open event, so that even if someone changes it, it switches back. Also you can lock only the user interface, which would allow the vba to run unencumbered by the sheet protection: https://stackoverflow.com/questions/125449/protecting-cells-in-excel-but-allow-these-to-be-modified-by-vba-script – Valon Miller Sep 18 '18 at 19:54
  • Echoing what others have said, you should fully qualify the range reference. You can do this by pre-pending `sh`, for example: `sh.Range("AL6")` since sh is the context in which the change occurred. – Valon Miller Sep 18 '18 at 19:55

1 Answers1

1

The issue appears to be related to sheet protection.

You could either set the cell "AL6" to locked = false, or you can use the Workbook_Open event to apply user interface only locking, which will allow VBA to run as if it is unlocked:

For example:

Private Sub Workbook_Open()

    Thisworkbook.sheets("<put your sheet name here>").Protect "Password", UserInterfaceOnly := True

End Sub

More info on UserInterfaceOnly: Protecting cells in Excel but allow these to be modified by VBA script

Valon Miller
  • 1,156
  • 5
  • 9