VBA Macro not working when workbook is protected

**JustinMAS** · 04-23-2008, 03:41 PM

I wish I could have been more specific with my Title. This is what I am encountering. I used the McGimpsey code for generating a unique sequential number every time a workbook opens. I tailored the code to fit my needs and stuck it into my invoice sheet. For the longest time I was unable to make it work until I moved the code from a template to a standard workbook. Then I was able to make it work as long as the sheet was unprotected. I realized that to get the code to work I had to then unprotect the cells that the code was writing to. Now that I've done that I am getting errors in the code that weren't there before, rendering the macro useless. The code I am using is:

Please Login or Register  to view this content.

I am getting errors on the ".NumberFormat = "dd-mmm-yy"" line and ".NumberFormat = "@""

I am out of ideas at this point. Can someone give me a hand please? BTW, I'm running Excel 2007.

Justin

**shg** · 04-23-2008, 04:17 PM

Please edit your post to add code tags and to have a title descriptive of your problem.

**shg** · 04-23-2008, 04:57 PM

You need to either unlock the cells to be manipulated, or unprotect the sheet in VBA before before doing so.

If you protect the sheet in VBA with the UserInterfaceOnly option set to True, then you can manipulate the cells without unprotecting the sheet. If you elect to go this way, see the remarks in Help for the Protect method regarding UserInterfaceOnly.

**JustinMAS** · 04-23-2008, 04:59 PM

I have unlocked the cells. I am still getting the error whenever I open the worksheet.

**shg** · 04-23-2008, 05:00 PM

Post the workbook?

**JustinMAS** · 04-23-2008, 05:05 PM

Here you go!

**shg** · 04-23-2008, 05:16 PM

Cells Z10 and Z11 are merged, and Z10 was unlocked but not Z11. Unlock both and it works fine.

Merging cells is a bad idea.

**JustinMAS** · 04-23-2008, 05:20 PM

I am really new to Excel "programming". Up until now the deepest I have gotten would be to use formulas in a sheet. Is there a better way to achieve what I was attempting to do?

**JustinMAS** · 04-23-2008, 05:29 PM

I just went into the worksheet, I un-merged the dispatch number block, set the protection on all of those cells to unprotected, re-merged the cells, saved and reopened the document and I still have the same error. I also tried unmerging and using on cell and I am getting the same error "Run-Time Error '1004': Unable to set the number format property of the range class"

**shg** · 04-23-2008, 06:05 PM

Post it again?

**JustinMAS** · 04-23-2008, 08:35 PM

As soon as I get back to work tomorrow I'll post it again. Thanks for all of your help. I truly appreciate it.

**JustinMAS** · 04-24-2008, 08:07 AM

Here it is again with the changes I made. BTW if you want to unprotect the sheet, it isn't password protected.

**shg** · 04-24-2008, 11:37 AM

When you protect the sheet, under Allow all users of this worksheet to:, check Format cells.

**JustinMAS** · 04-24-2008, 02:18 PM

I did what you suggested and lo and behold, IT WORKED!!! Thanks so much for all of your help. Again, it is truly appreciated.

**shg** · 04-24-2008, 04:58 PM

You're welcome, Justin.

VBA Macro not working when workbook is protected

LinkBack

Thread Tools

Rate This Thread

Display

VBA Macro not working when workbook is protected

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions