By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,251 Members | 2,727 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,251 IT Pros & Developers. It's quick & easy.

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

gnawoncents
100+
P: 214
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
  3.  
**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
Share this Question
Share on Google+
1 Reply


gnawoncents
100+
P: 214
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.