Protecting sheet is giving me error message with my macros. - Unable to set hidden property of range class. HELP!

Anonymous
2022-10-18T12:49:16+00:00

I am trying to Show / Hide row(s). Unprotected, it works fine. However, when I protect and try to use the macro buttons, I get an "Unable to set hidden property of range class." What am I doing wrong?

Here is my code:

Public Sub AllowMacros()

Me.Protect UserInterfaceOnly:=True

End Sub

Sub Hide_Answer()

'

' Hide_Answer Macro

'

' 

Rows("19:20").Select 

Selection.EntireRow.Hidden = True   (<- this part is highlighted )

Range("B19:Q20").Select

End Sub

Sub Show_Answer()

'

' Show_Answer Macro

'

' 

Rows("19:20").Select 

Selection.EntireRow.Hidden = False 

Range("B19:Q20").Select

End Sub

Microsoft 365 and Office | Excel | For business | 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

6 answers

Sort by: Newest
Most helpful Newest Oldest
  1. Anonymous
    2022-10-18T16:52:42+00:00

    Thank you, Jan. You have been extremely helpful.

    Question. Is there a way to prevent others from sliding up or down when in the worksheet? Is that possible?

    Was this answer helpful?

    0 comments
  2. 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

  3. Anonymous
    2022-10-18T15:34:27+00:00

    Yes. That is exactly what's happening. Your suggestion prior has helped me to work on the macro-enabled sheet.

    Now (as you just explained) When I close out and reopen it, it's protected and unable to use the macros, causing e to get the error message.

    However, when I open the macros box and run "sheet1.AllowMacros," the macros work while protected.

    So is there no other way around this, keeping the sheet protected and having the macros work from when the sheet is first opened?

    Was this answer helpful?

    0 comments
  4. Anonymous
    2022-10-18T15:22:54+00:00

    Dean,

    You came across the fact that you cannot do some (many) actions in a protected sheet.

    Even from within VBA (macros) it is impossibele then.

    But if you use (in VBA) the protection with the argument UserInterfaceOnly:=True then, at a sudden, you can do things from within VBA that cannot be done in the user interface in a protected sheet.

    This protection with UserInterfaceOnly has to be done (once) each time you open the file and before you want VBA to do things that otherwise would be impossible in a protected sheet.

    Hope this declares it a little bit.

    Jan

    Was this answer helpful?

    0 comments
  5. 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