470,575 Members | 1,877 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,575 developers. It's quick & easy.

Error 1004 on second line trying to set value or formatting of cell or range

214 100+
I seem to be missing an unstated rule here and am looking for some insight.

I am running Excel 2016 on Windows 10 Enterprise and am getting an error when I try to set the value or formatting of a cell (or cells). The error itself (Run-time error '1004': Application-defined or object-defined error) doesn't tell me much.

Key notes:
- I get the error always on the SECOND line of code. The first works fine.
- The line of code works fine when the preceding line is "commented out" or deleted.
- I get the error whether I am calling the VBA from a command button or using a Worksheet_Change event.
- I get the error when using Cells or Range and whether setting the value or formatting.
- The code is all stored in and targeted to the same worksheet from which it is triggered.

Sample Code
Expand|Select|Wrap|Line Numbers
  1. Range(Cells(17, 2), Cells(22, 2)).ClearContents
  2. Range(Cells(14, 2), Cells(21, 2)).Font.ColorIndex = 0
**Again, the error occurs on the second line, but I get no error if I don't run the first line. If I switch the order of the two lines, I still get the error on the second of the two lines. What am I missing?
Jul 17 '19 #1
1 2678
214 100+
Thank you to everyone who took the time to look at this. I tried running the code on a new, blank worksheet and didn't get an error, so I did some extra troubleshooting and figured out what I was missing.

I apologize--my original question didn't have all the information anyone would have needed to figure out the problem.

My worksheet is protected and VBA unprotects it to make changes then re-protects it before closing. What I didn't realize was that assigning a value from VBA would trigger the Worksheet_Change event. Consequently, after assigning the first value, I was inadvertently calling the procedure and re-protecting the sheet. This drove the error the next time (2nd line) I tried to assign a value or make a change.

I fixed the issue by disabling events at the start of my procedure and re-enabling at the end (see below). Rookie mistake! Thanks again all. Hopefully my oversight helps someone else out.

Expand|Select|Wrap|Line Numbers
  1. Application.EnableEvents = False
  2. 'Assign values and formats to cells and ranges here
  3. '
  4. '
  5. Application.EnableEvents = True
Jul 18 '19 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by dailem | last post: by
4 posts views Thread by kscdavefl | last post: by
1 post views Thread by livre | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.