Workbook Protection killing my macros

[Phoenix333]

I'm having this issue with Workbook Protection killing my macros and i'm wondering if anyone else has had the same problem and if there was a simple solution. Here's the gist:

I have a workbook that on one tab has a several lists that shows the offices and below each office is the list of employees that support that office. Each column is given it's own array name, RDM_01, RDM_02, etc. On the main tab in cell C9, there is a drop down list for office.

The user selects the dropdown and selects the appropriate office. The data validation for this list is based on an array called *surprise*, office.

Then in cell C11 I have another drop down, but the data validation on this one is ruled by a macro that displays the list based on the selection in C9. So basically, the macro says, if C9 = Northeast, C11 should display this list RDM_01; if C9 = Lisle, C11 should display list RDM_02. This was working because the cell validation was set to = a list called RDMList, but RDMList changed depending on the data in C9; If Northeast, RDMList = RDM_01, if Lisle, RDMList = RDM_02.

This was working fine, I could switch my selection in C9 and the list that would become available in C11 displayed the appropriate names.

Then I protected the sheet and the workbook, saved it and sent it off for review/approval. However, when my boss looked at it, he noticed that no matter which office he selected in C9, the list of names in C11 never changed. So, I unprotected it thinking this was the problem; but it still doesn't work anymore. It acts almost as if the VB codes doesn't even exist, even though it's still there and i don't get an error when i try to use the workbook, it just doesn't give me the list i want anymore.

I read that other people were having issues as well, but all the posts i found, the error seemed to be related to sharing, which i am not sharing, the workbook is set for only 1 user at a time.

Any one else ever have this issue? If so, how did you fix it?
I can't attach the file, but I have no problems sending the file if you want to take a look, just tell me where to send it.

Any feedback is appreciated.

Thanks
T

 

[jasmith4]

I don't know why you're bothering with a macro to change from office name to offce RDM code -- a simple two-column table and a VLOOKUP should suffice. Also, if you're protecting the worksheet and the workbook, you have to unlock the cells that you want users OR THE CODE to change -- select the cell(s), hit Ctrl-1 and look at the Protection tab. If you want the code to change cells, but not the user, the code has to unprotect the sheet (Worksheet.Unprotect), make changes, and protect it again (Worksheet.Protect).

In any case, it seems to me that on your boss's machine the Excel security is set up to not execute macros. If you're in Excel 2003, check out Tools / Macro / Security, or in 2007/2010 check out File / Options / Truct Center / Trust Center Settings.

 

[Phoenix333]

Thank you jasmith4, the part about keeping the cells that are populated by code unlocked was something i was not aware of. That seems to have solved my problem.

The RDM_01, RDM_02...RDM_09 are not codes with 1 being assigned to each office. They are named arrays; each one with it's own list of employees. So when a user selects ones of 9 offices in the first cell, the 2nd cell doesn't display RDM_xx; the macro attached to it turns it into a drop down box with a list of the employess that belong to the selected office.

So, cell 1 is a dropdown where they can select Northeast, Toronto, Lisle, LA, etc. Cell 2 is a dropdown that if cell 1 = Northeast, cell 2 gives you a choice of selecting Frank, Tom, Mindy, etc... but if you select Toronto in Cell 1, then the choices in the drop down in cell 2 change to be Peter, Paul, Mary, etc...

But because i had a 3rd field that created the email address for the employee selected in cell 2 that was locked when the sheet was protected, the macros weren't working.

You were a huge help in getting this fixed and I really appreciate it. Thank you!