Application-defined or object-defined error with Format function

Question

My cell is locked and I am filling the cell with data on Worksheet_Change event of a drop-down: Format(Date, "mm-dd-yyyy").

I am getting following error :: Run-time error '1004': Application-defined or object-defined error

I do not want to unlock the field as it is to be non-editable.

I also do understand that it is giving the error just because the cell is locked and it needs to perform the operation: Format(Date, "mm-dd-yyyy")..

What options do I have to make it working without unlocking the cell?

Scott Holtzman · Accepted Answer · 2017-06-21 18:29:38Z

1

Unlock the sheet, then make changes as needed, then lock again.

Me.Unprotect "pwd"
Me.Range("A1").Value = Format(Date,"mm-dd-yyyy")
Me.Protect "pwd"

answered Jun 21, 2017 at 18:29

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sid Over a year ago

I am not using a password to protect .... I am just using the following:ActiveSheet.Unprotect ActiveSheet.Range(ActiveSheet.Cells(S, 1), ActiveSheet.Cells(S + 9, lColumn)).Locked = True ActiveSheet.Protect .. Please do let me know how to do using this approach..

Scott Holtzman Over a year ago

@sid - Just take the password out and pass "". Otherwise, I am confused. You can just set the format (or cell value) in between protection and unprotection. Because locking a cell is meaningless when worksheet is unprotected state.

sid Over a year ago

This is not working ... I am setting the ActiveSheet.Range().locked = False and then setting the value and then setting ActiveSheet.Range().Locked = True ... This is how the code looks like ActiveSheet.Range(ActiveSheet.Cells(rps + 3, cps)).Locked = False Cells(rps + 3, cps).Value = Format(Date, "mm-dd-yyyy") ActiveSheet.Range(ActiveSheet.Cells(rps + 3, cps)).Locked = True

sid Over a year ago

What I also found out is that when I click on the drop-down and select and option 2 things are happening: 1) A String in a cell is populated and 2) A date in a cell is populated ... Both the cells are locked .. .while it is able to write String to a locked cell BUT its giving"Application-defined OR Object-defined" Error while entering date. ??? What could be the reason...... Why excel vba is able to enter string in a locked cell but unable to enter date in a locked cell ?? Please help

Collectives™ on Stack Overflow

Application-defined or object-defined error with Format function

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related