ShowAllData doesn't work with Protection

J

JTreks

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?
 
D

Dave Peterson

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

JTreks

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
 
D

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
 
J

JTreks

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top