VBA Worksheet Protection whilst mainting ActiveX Functionality

[mizogy]

Hi ALl

Hoping someone will be able to help me out there, I've been racking my brains and cannot find a solution.

Please see code below. The intention is to apply protection to the sheet whilst allowing ActiveX functionality (i.e. use of slider bars) whilst the worksheet is protected. It had been working until recent, however now getting a Run-time error '51721' Application-defined or object-defined error

Private Sub Workbook_Open()
Application.EnableEvents = True
Application.Calculation = xlSemiautomatic
Application.ScreenUpdating = False
With Worksheets("RPOA Calculator")
.Protect Password:="commercialrpoa", UserInterfaceOnly:=True
.EnableOutlining = True
End With
End Sub

Your help appreciated!

Mizogy

 

[cerfani]

maybe this will fix it... never saw this error but googled this for you...

https://www.altera.com/support/support-resources/knowledge-base/solutions/rd08172012_653.html

1. Select Excel Options
2. Select the Trust Center category and click Trust Center Settings
3. Select the ActiveX Settings category
4. Change the setting from Disable all controls without notification to one of the other options.

 

[mizogy]

Good shout, however my setting is already applied to 'Enable all controls without restrictions' so not the issue....

maybe this will fix it... never saw this error but googled this for you...

https://www.altera.com/support/support-resources/knowledge-base/solutions/rd08172012_653.html

1. Select Excel Options
2. Select the Trust Center category and click Trust Center Settings
3. Select the ActiveX Settings category
4. Change the setting from Disable all controls without notification to one of the other options.

 

[cerfani]

Ah I see... I kinda didn't like that solution anyway. I hate when people tell me to solve a problem by disabling a notification. LOL

Good luck though.

 

[mizogy]

OK, this is strange, getting the same message if just trying to select the sheet. Tried changing the name of the sheet as well but still same issue. Help!?!

Sheets("RPOA Calculator").select