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 No comments

12 answers

Sort by: Oldest
  1. 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

  2. Anonymous
    2012-07-10T18:22:28+00:00

    Thanks very much Jim, that's perfect.  I appreciate your time on this.  Dean

    Was this answer helpful?

    0 comments No 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. Anonymous
    2012-07-10T19:13:05+00:00

    Thanks very much Tom, that is extremely helpful, I appreciate it.  Dean

    Was this answer helpful?

    0 comments No comments
  5. Anonymous
    2012-07-11T18:16:44+00:00

    So is the UserInterfaceOnly better than it used to be becuase it used to be very hit and miss to the point where it was more of a pain than it was worth. I would set protection to userinterface only and I still ended up having to unprotect and reprotect. I am going back to XL2002 and earlier.

    Was this answer helpful?

    0 comments No comments