Macro Fails When Worksheet Is Password Protected

  dogbreath1 17:06 08 Oct 2010
Locked

I have clickable image button which toggles the protection of a range of cells using a macro.

When the worksheet is protected, this feature works fine. However, as soon as the worksheet is password protected, clicking on the image asks for the password and failure to insert it results in a runtime error.

How can I force the button/macro to behave as it was intended....in a similar way to an unlocked cell.

  VoG II 17:11 08 Oct 2010

ActiveSheet.Unprotect Password:="abc"
'
'your code here
'
ActiveSheet.Protect Password:="abc"

  dogbreath1 18:04 08 Oct 2010

Tried that including inserting the actual password and got the message, Compile error: invalid outside procedure.

  VoG II 18:08 08 Oct 2010

I probably didn't explain very well. My code has to go inside your code. Something like

Private Sub CommandButton1_Click()
ActiveSheet.Unprotect Password:="abc"
'
'your code here
'
ActiveSheet.Protect Password:="abc"
End Sub

  dogbreath1 19:07 08 Oct 2010

I've inserted the code as shown and it allows the button to function but doesn't reset the password on completion. After the macro executes, the worksheet is protected but not by password.

  dogbreath1 19:33 08 Oct 2010

Okidoki, solved the above problem by changing the original button macro to:-

Sub ToggleLockedCells()

Sheet1.Unprotect Password:="away1918security"

ActiveSheet.Unprotect

With Range("L7:L14")
If .Locked = True Then
.Locked = False
.Font.ColorIndex = 4
Else
.Locked = True
.Font.ColorIndex = 3
End If

End With

Sheet1.Protect Password:="away1918security"

End Sub


...and changing it from:-

Sub ToggleLockedCells()

ActiveSheet.Unprotect

With Range("L7:L14")
If .Locked = True Then
.Locked = False
.Font.ColorIndex = 4
Else
.Locked = True
.Font.ColorIndex = 3
End If

End With
ActiveSheet.Protect
End Sub

However, after saving and relaunching the workbook, a second macro used to force the user to enable macros ceases to work properly. If 'disable' is chosen, the workbook opens at worksheet1 and with macros disabled (as opposed to displaying a worksheet named 'macros' containing a message 'macros must be enabled.

Alternatively, if the user opts to enable macros, worksheet1 is still displayed but with a runtime error.

Why does the new module macro cause the workbook 'force macros to be enabled' macro to fail?

  VoG II 20:35 08 Oct 2010

What is your force user to enable macros code?

  dogbreath1 20:59 08 Oct 2010

Option Explicit

Const WelcomePage = "Macros"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Turn off events to prevent unwanted loops
Application.EnableEvents = False

'Evaluate if workbook is saved and emulate default propmts
With ThisWorkbook
If Not .Saved Then
Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _
vbYesNoCancel + vbExclamation)
Case Is = vbYes
'Call customized save routine
Call CustomSave
Case Is = vbNo
'Do not save
Case Is = vbCancel
'Set up procedure to cancel close
Cancel = True
End Select
End If

'If Cancel was clicked, turn events back on and cancel close,
'otherwise close the workbook without saving further changes
If Not Cancel = True Then
.Saved = True
Application.EnableEvents = True
.Close savechanges:=False
Else
Application.EnableEvents = True
End If
End With
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Turn off events to prevent unwanted loops
Application.EnableEvents = False

'Call customized save routine and set workbook's saved property to true
'(To cancel regular saving)
Call CustomSave(SaveAsUI)
Cancel = True

'Turn events back on an set saved property to true
Application.EnableEvents = True
ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_Open()
'Unhide all worksheets
Application.ScreenUpdating = False
Call ShowAllSheets
Application.ScreenUpdating = True
End Sub

Private Sub CustomSave(Optional SaveAs As Boolean)
Dim ws As Worksheet, aWs As Worksheet, newFname As String
'Turn off screen flashing
Application.ScreenUpdating = False

'Record active worksheet
Set aWs = ActiveSheet

'Hide all sheets
Call HideAllSheets

'Save workbook directly or prompt for saveas filename
If SaveAs = True Then
newFname = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
Else
ThisWorkbook.Save
End If

'Restore file to where user was
Call ShowAllSheets
aWs.Activate

'Restore screen updates
Application.ScreenUpdating = True
End Sub

Private Sub HideAllSheets()
'Hide all worksheets except the macro welcome page
Dim ws As Worksheet

Worksheets(WelcomePage).Visible = xlSheetVisible

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden
Next ws

Worksheets(WelcomePage).Activate
End Sub

Private Sub ShowAllSheets()
'Show all worksheets except the macro welcome page

Dim ws As Worksheet

ThisWorkbook.Sheets("Sheet1").Visible = xlSheetVisible

Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub

  VoG II 21:08 08 Oct 2010

I can't immediately see why there should be any interaction but perhaps it should be


Sub ToggleLockedCells()

Sheet1.Unprotect Password:="away1918security"

With Range("L7:L14")
If .Locked = True Then
.Locked = False
.Font.ColorIndex = 4
Else
.Locked = True
.Font.ColorIndex = 3
End If

End With

Sheet1.Protect Password:="away1918security", userinterfaceonly:=True

End Sub

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

Illustrator Sylvain Tegroeg created thousands of intricate line drawings for the mobile game…

Best iPad buying guide 2017

Comment télécharger une application indisponible en France ?