Code to rename worksheet based on combobox selection that works with protected sheet

Hi Nina. If your code works on an unprotected sheet, try this code.

Please Login or Register  to view this content.

Thanks for your quick response. I forgot to mention that I was using code to protect the sheet to allow for grouping/ungrouping. Sorry about that. The code I used to protect the worksheet is below:

Please Login or Register  to view this content.

I temporarily erased the sheet protection code to run the code you sent and it works to update the worksheet name after I manually protected the sheet, but it didn't seem to re-protect the sheet after it was renamed. Does that make sense?

I'll continue working on it, but I would really appreciate some more of your feedback, especially incorporating the protection code I already have above. This seems to be on the right track.

From looking at your code, it appears that when you open the workbook, the sheet is protected with the password "password" but allows grouping. Then when you enter data in F9, you want to re-name the sheet to the value of F9 but the cell may contain illegal characters. Is this correct? What kind of illegal characters are we talking about? If you could post a copy of your file it would also help.

Your understanding is correct.

The illegal characters just refers to a potential for blank names in the drop down list since a worksheet name can't be blank. I put the error there based on what I read in other forums, but it shouldn't make a difference with the way I currently have things set up.

I'm attaching a test spreadsheet since that should hopefully help. To unprotect, type "password". Let me know if you have any more questions. I've tried playing around with the code, but still can't get the renaming to work properly. I'm not tied to any particular method of coding as long as it works.

After looking at your file, I noticed that you want to re-name the sheet according to the value of N9 not F9. Cell N9 has a formula in it wich is based on E10 which itself is based on a drop down with names. I tried choosing different names but it didn't have any effect on N9. I couldn't quite figure out how the formulae worked. In the attached file, you will notice that I simply created a drop down list in cell P9 of the dates in sheet "Startdate", range A2:A19. I also changed the code in the worksheet module to refer to P9. When you choose a date from the drop down in P9, the sheet is re-named accordingly. This seems to work the way you wanted. I hope this helps. Maybe you could incorporate it in your sheet to make it work for you.

Thanks! I meant N9, not F9. Sorry for any confusion. I missed the "F" in your sentence.

I looked at the file you sent. I'm getting a run-time error '1004' when I try to change the date. I'll play around with the code some more to see if I can incorporate it. Thanks again for your help.

I', not sure why you're getting an error. When I try the file I last posted, it works fine if I use the drop down in P9. Good luck with it.