How to allow Data Validation changes using VBA while Worksheet is protected

Anonymous
2012-07-10T17:44:13+00:00

Hi All,

        I've set up a grouping of Option Buttons that have assigned macro's, these assign different PullDown lists to a specific  Merged cell range in the worksheet.  They work great when I have the worksheet UN-PROTECTED.  When I PROTECT the worksheet, it won't allow me to change the "=list", therefore the Option Buttons fail when selected.  Below is one example of an option button macro.

    Range("D3:J6").Select

    With Selection.Validation

        .Delete

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

        xlBetween, Formula1:="=xFILTER"

        .IgnoreBlank = True

        .InCellDropdown = True

        .InputTitle = ""

        .ErrorTitle = ""

        .InputMessage = ""

        .ErrorMessage = ""

        .ShowInput = True

        .ShowError = True

    End With

Is there a way to allow Data Validation changes to the same select cell while the sheet is protected?

Thanks for your time on this - Dean

I have both 2003 & 2010 Excel

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

12 answers

Sort by: Newest
Most helpful Newest Oldest
  1. Anonymous
    2012-07-13T16:40:53+00:00

    No Tom we are all good. I did not take it as a criticism. My answer was oversimplified in that you are correct that there is UserInterfaceOnly which does allow some restricted actions on protected sheets. In general I don't post UserinterfaceOnly as I have never had good luck with it. I was honestly looking for your advice on UserInterfaceOnly. Since I have not used it in a long time I was wondering if it is better now. I figured if anyone knew you would.

    PS. When I make mistakes just keep pointing them out. It is appreciated. You are one of the few posters who will point out a mistake and call it for what it is.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2012-07-13T09:56:58+00:00

    Hi Tom,

         Thanks for putting the code to test, the DrawingObjects did the trick, it worked great, without error.  I understand about the security issue when Unprotecting in the macro and that's a good point.  Thanks for your time on this, it's great to have this alternative too.  Thanks to both you and Jim.  Dean

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  4. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  5. Anonymous
    2012-07-12T16:19:01+00:00

    yes, I'm satisfied with the Protect, UnProtect too.  Thanks Jim - Dean

    Was this answer helpful?

    0 comments