How do code a Command Button in Excel to unprotect a worksheet?

[tuccokeith]

Hello,

I want to code one Command Button to protect an Excel worksheet and another one to unprotect a worksheet.

I need to do this because the autofilter does not work when a worksheet is protected.

Can this be done using a typical command button VB macro?

Private Sub CommandButton1_Click()
If CommandButton1.Caption = "Allow Autofilter" Then

???????????
End If
End Sub

 

[rmikesmith]

tuccokieth,

You can do this using a single CommandButton. The Click event handler should be made to look like:

Const PW = "DUH"


Private Sub CommandButton1_Click()

  With CommandButton1
  If .Caption = "AutoFilter On" Then
    .Caption = "AutoFilter Off"
    Me.Unprotect PW
    Me.Range("A1").AutoFilter
  ElseIf .Caption = "AutoFilter Off" Then
    .Caption = "AutoFilter On"
    Me.Range("A1").AutoFilter
    Me.Protect PW, True, True, True
  End If
  End With
  
End Sub

This will toggle AutoFilter On/Off along with worksheet protection. Change the Range address to what's appropriate for your list.


HTH
Mike

 

[asjeff]

Hi TuccoKeith - use coding like:

ActiveSheet.Name = "Sheet1"
Worksheets("Sheet1&quot.Protect Password:="YourPassword"
Worksheets("Sheet2&quot.Activate
Worksheets("Sheet2&quot.UnProtect Password:="YourPassword"


Hope this helps

Asjeff

 

[tuccokeith]

Hello,

Regarding this field definiton code:

Const PW = "DUH"

If my worksheet name is amendment would this code read:

Const PW ="Amendment"?

 

[asjeff]

I think rmikesmith intends pw to stand for 'password', so:

const pw = "Your Password" - which will be the password that you originally protected the sheet with


asjeff

 

[rmikesmith]

asjeff,

Thanks for filling in the blanks.


Mike

 

[tuccokeith]

Hello,

I am getting an error with the following code:

"AutoFilter Method Range Class failed"

What did I do wrong in setting up the Range?

Const PW = "hipaa"


Private Sub CommandButton4_Click()
With CommandButton4
If .Caption = "Auto Filter On" Then
.Caption = "Auto Filter Off"
Me.Unprotect PW
Me.Range("a1:ah1&quot.AutoFilter
ElseIf .Caption = "Auto Filter Off" Then
.Caption = "Auto Filter On"
Me.Range("a1:ah1&quot.AutoFilter
Me.Protect PW, True, True, True
End If
End With
End Sub

 

[rmikesmith]

I can see nothing in your posted code that would cause this problem. I played around with some variations and could not generate this error except when I supplied an invalid range designation (e.g. "AH" with no number). Not sure what to advise.

Mike

 

[weetnie]

Why not try to Record it as a macro,

Compare that code to yours,

 

[tuccokeith]

Hello,

The version of Excel I have is Excel 97

Would that have something to do with this problem?

 

[tuccokeith]

I just tried the code with Excel 2000 and it worked. It is not workingn with Excel 97. Does the code need to be changed to work with 97?

 

[rmikesmith]

tuccokeith,

I don't believe so, but not 100%. I tested this on XL 2000 but have 97 at home. I'm heading that way soon. I'll try it out then post back as to outcome.

Regards,
Mike

 

[rmikesmith]

tuccokeith,

Just copied the workbook to my home system and ran it on Excel 97 without problems.

Mike