Solved: Sheetprotection and Save dialog

[Gert Jan]

Hi :hi:
I'm using this KBase entry http://vbaexpress.com/kb/getarticle.php?kb_id=578 (thanks John) in a workbook.
I would like to have the sheet ("Prompt") protected, but as you see there has to be an entry in A100,
Private Sub HideSheets()
'
Dim Sheet As Object '< Includes worksheets and chartsheets
'
With Sheets("Prompt")
'
'the hiding of the sheets constitutes a change that generates
'an automatic "Save?" prompt, so IF the book has already
'been saved prior to this point, the next line and the lines
'relating to .[A100] below bypass the "Save?" dialog...
If ThisWorkbook.Saved = True Then .[A100] = "Saved"
'
.Visible = xlSheetVisible
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next
'
If .[A100] = "Saved" Then
.[A100].ClearContents
ThisWorkbook.Save
End If
'
Set Sheet = Nothing
End With
'
End Sub
When i protect the sheet and put Unprotect and Protect in the code, the "Save?" dialog keeps popping up.
How and where do I have to put in "Unprotect" and at which point the "Protect" statement to prevent the Save-dialog?

Gert Jan

[Bob Phillips]

Option Explicit

Private Const WS_MASTER As String = "Prompt"

Private Sub Workbook_Open()

With Application
'disable the ESC key
.EnableCancelKey = xlDisabled
.ScreenUpdating = False

Call UnhideSheets

.ScreenUpdating = True
're-enable ESC key
.EnableCancelKey = xlInterrupt
End With

End Sub
'
Private Sub UnhideSheets()
Dim sh As Object

For Each sh In Sheets
Sheet.Visible = xlSheetVisible
Next

Sheets("Prompt").Visible = xlSheetVeryHidden

Application.Goto Worksheets(1).Range("A1"), True '< Optional

ActiveWorkbook.Saved = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.EnableCancelKey = xlDisabled
.ScreenUpdating = False

Call HideSheets

.ScreenUpdating = True
.EnableCancelKey = xlInterrupt

Cancel = True
End With
End Sub

Private Sub HideSheets()
Dim sh As Object '< Includes worksheets and chartsheets

With ThisWorkbook

With .Sheets(WS_MASTER)

.Unprotect
'the hiding of the sheets constitutes a change that generates
'an automatic "Save?" prompt, so IF the book has already
'been saved prior to this point, the next line and the lines
'relating to .range("A100").Value below bypass the "Save?" dialog...
If ThisWorkbook.Saved = True Then .Range("A100").Value = "Saved"

.Visible = xlSheetVisible

For Each sh In Sheets
If Not Sheet.Name = WS_MASTER Then
Sheet.Visible = xlSheetVeryHidden
End If
Next

If .Range("A100").Value = "Saved" Then
.Range("A100").Value.ClearContents
End If

.Protect

End With

.Save

End With

End Sub

[Gert Jan]

Hi Bob,
Thanks for your reply, seems yor no fan of shorthand notation :devil2:
I get an error 424 "Object required" and this line is highligted:
.Range("A100").Value.ClearContents
What would be causing that?

Gert Jan

[Gert Jan]

I changed
.Range("A100").Value.ClearContents into .Range("A100").ClearContents no more error but the workbook isn't closing down

[Bob Phillips]

Sorry about that Gert Jan, a block replace that went askew.

And yes, I do dislike shorthand notation with a passion. Sloppy, lazy and unnecessary IMO.

Anyway, tested code



Option Explicit

Private Const WS_MASTER As String = "Prompt"

Private Sub Workbook_Open()

With Application
'disable the ESC key
.EnableCancelKey = xlDisabled
.ScreenUpdating = False

Call UnhideSheets

.ScreenUpdating = True
're-enable ESC key
.EnableCancelKey = xlInterrupt
End With

End Sub
'
Private Sub UnhideSheets()
Dim sh As Object

For Each sh In Sheets
sh.Visible = xlSheetVisible
Next

Sheets("Prompt").Visible = xlSheetVeryHidden

Application.Goto Worksheets(1).Range("A1"), True '< Optional

ActiveWorkbook.Saved = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.EnableCancelKey = xlDisabled
.ScreenUpdating = False

Call HideSheets

.ScreenUpdating = True
.EnableCancelKey = xlInterrupt

End With
End Sub

Private Sub HideSheets()
Dim sh As Object '< Includes worksheets and chartsheets

With ThisWorkbook

With .Sheets(WS_MASTER)

.Unprotect
'the hiding of the sheets constitutes a change that generates
'an automatic "Save?" prompt, so IF the book has already
'been saved prior to this point, the next line and the lines
'relating to .range("A100").Value below bypass the "Save?" dialog...
If ThisWorkbook.Saved = True Then .Range("A100").Value = "Saved"

.Visible = xlSheetVisible

For Each sh In Sheets
If Not sh.Name = WS_MASTER Then
sh.Visible = xlSheetVeryHidden
End If
Next

If .Range("A100").Value = "Saved" Then
.Range("A100").ClearContents
End If

.Protect

End With

.Save

End With

End Sub

[Gert Jan]

Thanks, got it working now,


Sloppy, lazy and unnecessary IMO

Oh-Oh you're gonna get yourself into trouble on this one :rotlaugh:

[Bob Phillips]

Already have ... many times.