Private Sub Worksheet_SelectionChange

[boxsterman]

Hi all,

I have some code on a particular worksheet. All is fine when the sheet is not protected, however when protected I receive a Run-time error '1004': Unable to set the Hidden porperty of the Range class.

Here is some of the code, it errors out on the hidden "True" line.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not IsNumeric(Range("C68")) Then Exit Sub
If Range("C68").Value > 0.03 Then
Range("73:85").EntireRow.Hidden = False
Else
Range("73:85").EntireRow.Hidden = True
End If

Thank you very much.

 

[Candyman8019]

Try something like this.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Unprotect Password:="password here"
If Not IsNumeric(Range("C68")) Then Exit Sub
If Range("C68").Value > 0.03 Then
Range("73:85").EntireRow.Hidden = False
Else
Range("73:85").EntireRow.Hidden = True
ActiveSheet.protect Password:="password here"
End If

 

[HaHoBe]

Hi boxterman,

either unprotect the sheet in question right at the start of the event and protect it again at the end but using Worksheet_SelectionChange makes it more useful to use the volatile setting of UserInterfaceOnly as additional parameter when using Protect. This could be done in Workbook_Open or Worksheet_Activate and would allow macros to run without problems. Volatile means that this status will not be saved and needs to be refreshed every time the workbook is opened again.

Sample code may look like

Private Sub Worksheet_Activate()
  With ActiveSheet
    .EnableSelection = xlNoSelection
    .Protect Contents:=True, UserInterfaceOnly:=True
  End With
End Sub

This would allow code to run on worksheet with code behind it.

Holger

 

[boxsterman]

Thanks everyone. I believe I got it to work,

 

[HaHoBe]

Hi boxtserman,

marking your own post without a hint on how you have done it is not the solution to me...

Holger

 

[smozgur]

Thanks everyone. I believe I got it to work,

Good to hear you got the solution.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.