ShowAllData doesn't work with Protection

[Guest]

I have a macro attached to a button that is supposed to remove the filter by
using the command ShowAllData. As long as I don't protect the worksheet it
works fine. As soon as I turn on protection I get a VBA error. Any help here?

 

[Dave Peterson]

How about adding code to unprotect the sheet, showall the data, then reprotect
the worksheet?

 

[Guest]

Thanks, Dave. Tried it... I hangs on the ShowAllData line. I know this line
is a valid line because the button (macro) works correctly when I do not
protect the worksheet.

Any idea what else it might be?

Thanks

 

[Dave Peterson]

It might be best to share the code you tried.

But I'd use something like:

Dim myPWD as String
myPWD = "hi"

With Worksheets("somesheethere") 'or ActiveSheet or Me
.unprotect password:=mypwd
If .FilterMode Then
.ShowAllData
End If
.protect password:=mypwd
End With

 

[Guest]

Thanks, again, Dave! I swear I did this on my work PC and it didn't work
there but it does here. I'll see what is different about my work PC. Thanks
again! BTW, here is my code... Identical to yours except I don't use a PW.


Sub Button6_Click()
'
' Button6_Click Macro
With ActiveSheet
.Unprotect
If .FilterMode Then
.ShowAllData
End If
.Protect
End With
End Sub

 

[Dave Peterson]

This looks like code that's used with a commandbutton from the Control toolbox
toolbar that's placed on that worksheet.

Instead of this:
With ActiveSheet
I'd use:
with Me

Me is the thing that owns the code--in this case, it's the worksheet with the
commandbutton.

Thanks, again, Dave! I swear I did this on my work PC and it didn't work
there but it does here. I'll see what is different about my work PC. Thanks
again! BTW, here is my code... Identical to yours except I don't use a PW.

Sub Button6_Click()
'
' Button6_Click Macro
With ActiveSheet
.Unprotect
If .FilterMode Then
.ShowAllData
End If
.Protect
End With
End Sub