Solved: unptotect workbook

[nbqleebarnes]

Hi All,
I have been busy with some code for some time and I need to protect the workbook to prevent the user from copying, moving or name changing the work sheets.
Although when I protect the workbook I get any error, because I am checking and reseting a value in a hidden sheet. I need to unlock the workbook, perform the check and reset,then lock the workbook again.
Can anyone give me a hand with this.
Thanks
Lee

[Bob Phillips]

Just unprotect the sheet in your code, do the stuff, and protect it again.

[nbqleebarnes]

Thanks XLD,
I have unprotected then protected again, but my code, I'm sure is very poor and the workbook becomes unprotected when I close the workbook and re-open, not very clued up with protect("admin", structure), can you help. here is what I have coded:

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"
Unprotect ("admin")
.Visible = xlSheetVisible
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Unprotect ("admin")
Sheet.Visible = xlSheetVeryHidden
Protect ("admin")
End If
Next
If .[A100] = "Saved" Then
Unprotect ("admin")
.[A100].ClearContents
ThisWorkbook.Save
Protect ("admin")
End If
'>>Not sure why this is here, as 'Sheet' is never set.<<
Set Sheet = Nothing
End With
End Sub

Thanks
Lee

[Bob Phillips]

I think this is what you want



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 .Range("A100").Value = "Saved"
.Unprotect "admin"
.Visible = xlSheetVisible
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Unprotect "admin"
Sheet.Visible = xlSheetVeryHidden
Sheet.Protect "admin"
End If
Next
If .Range("A100").Value = "Saved" Then
.Range("A100").ClearContents
ThisWorkbook.Save
End If

.Protect "admin"

'>>Not sure why this is here, as 'Sheet' is never set.<<
Set Sheet = Nothing
'>>yes it is, in the For ... Next loop
End With
End Sub


You do realise that if the workbook has been saved, those changes get ignored?

[nbqleebarnes]

Hi XLD,
Thanks for correcting my code, works for the sheet, but I have protected the workbook structure to prevent move, copy sheet, using tools-protection-protect workbook, if there is a way to do this in code then it will also work but I have used the excell's features. I am actually getting an error on close of unable to set visible properties, because my work book structure is protected.
How do I get around this.
Thanks
Lee

[Bob Phillips]

Maybe



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 .Range("A100").Value = "Saved"

.Unprotect "admin"
.Visible = xlSheetVisible

For Each Sheet In Sheets

If Not Sheet.Name = "Prompt" Then

Sheet.Unprotect "admin"
Sheet.Visible = xlSheetVeryHidden
Sheet.Protect "admin"
End If
Next

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

ThisWorkbook.Unprotect "admin"
.Range("A100").ClearContents
ThisWorkbook.Save
ThisWorkbook.Protect "admin", True
End If

.Protect "admin"

'>>Not sure why this is here, as 'Sheet' is never set.<<
Set Sheet = Nothing
'>>yes it is, in the For ... Next loop
End With
End Sub

[nbqleebarnes]

Thanks XLD,
Came right with:

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 .Range("A100").Value = "Saved"

ThisWorkbook.Unprotect "admin"
.Visible = xlSheetVisible

For Each Sheet In Sheets

If Not Sheet.Name = "Prompt" Then

ThisWorkbook.Unprotect "admin"
Sheet.Visible = xlSheetVeryHidden
ThisWorkbook.Protect "admin", True
End If
Next

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

ThisWorkbook.Unprotect "admin"
.Range("A100").ClearContents
ThisWorkbook.Save
ThisWorkbook.Protect "admin", True
End If

ThisWorkbook.Protect "admin", True

'>>Not sure why this is here, as 'Sheet' is never set.<<
Set Sheet = Nothing
'>>yes it is, in the For ... Next loop
End With
End Sub

Seems to work perfectly