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
Just unprotect the sheet in your code, do the stuff, and protect it again.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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:
[VBA]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[/VBA]
Thanks
Lee
I think this is what you want
[vba]
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
[/vba]
You do realise that if the workbook has been saved, those changes get ignored?
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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
Maybe
[vba]
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
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Thanks XLD,
Came right with:
[VBA]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[/VBA]
Seems to work perfectly
Posting Permissions
Reply With Quote
