VBA Code to Zoom Data Validation Drop Down Menu Won't Work in Protected Sheet

Hello forum! I have a challenge that I need help with concerning VBA and sheet protection. I have created Data Validation Drop Down Menus in a Worksheet to allow selection from certain lists stored in another sheet. Initially, the font of the options in the drop down menu list was extremely small. I entered the following VBA formula to auto zoom when these cells were selected and return to normal zoom when any other cell was selected:

Please Login or Register  to view this content.

I protected other formula containing cells in the workbook and protected the sheet. Now the VBA isn't able to execute and I once again have extremely small font when I try to select from the drop down menus. Does anyone have a fix for this?

Thanks for the help!

1. Please Edit your post by adding code tags around your code
- click Edit Post
- select code text
- click on # icon above

2. Your could try UnProtecting the sheet in the VBA just before zooming and Protecting it again after zooming

Please Login or Register  to view this content.

Or
3. Reduce the font size in your cells (by format) and then zoom the sheet to make cell text look bigger. That should eliminate the problem without changing zoom every dropdown.

Try
UnProtect immediately below
If Intersect.....
Protect immediately above
Exit Sub

It would be easier to read your code if you indent things - then can work out what belongs together...like this ...

Please Login or Register  to view this content.

I usually include a line at the beginning of most of my selection change event macros

Please Login or Register  to view this content.

This can prevent problems if you select a large number of cells

What does "did not work" mean?

You did change "YourPassword" to the correct value, I hope

First - let's check that the sheet is being unprotected
- leave out the Protect line at the end
- and try selecting a dropdown
- now manually check status of protection
- sheet should be unprotected

Try moving Unprotect to the FIRST line of your code
- I think the protection is preventing the macro from triggering

If that works then you will need to amend a couple of lines in your macro

Let me know and I will test a couple of things on your macro when i get home

It sounds as though the protection is interfering with the event macro, even after the sheet has been unprotected - which I would not expect.
I will get back to you after I test it on my PC.

This works for me
- try out in attached workbook
- sheet is protected (password = "ABCD")
- cells A1 to A20 contain DV
- select any of those cells and zoom = 85%
- select any other cell and zoom = 50%

Please Login or Register  to view this content.