hide, unprotect, unhide, protect

[Juriemagic]

Hi good people!,

I know how to hide, unhide, protect, etc, worksheets with vba, but this one thing has got me. how do I tell vba that "when (if) sheet "log" is hidden, it must be unprotected, and when un-hidden, it must be protected. No passwords are required. I have tried several ways, but either I get errors or no errors, but always not working. please help...

[p45cal]

I'm guessing: You want people viewing the sheet when it's visible not to be able to change it, and when it's not visible you want to allow changes made by vba code?

Try using the UserInterfaceOnly option when protecting the sheet. This setting remains valid until the file is closed. When it's reopened you'll have to set it again (once only for the whole time the workbook remains open). You can do this in the workbook_open event; in the Thisworkbook code module:

Private Sub Workbook_Open()
Sheets("TheSheetInvolved").Protect userinterfaceonly:=True
End Sub

So this code doesn't repeatedly change the protected status of the sheet, that remains the same, sheet visible or not. The argument when set to True "protects the user interface, but not macros. If this argument is omitted, protection applies both to macros and to the user interface."

see https://msdn.microsoft.com/en-us/library/office/ff840611.aspx

[Juriemagic]

Okay, this is totally a new idea for me. I will have a look at this and see if it helps me. Will come back to you. Thanx for your time..

[Bob Phillips]

Why not just protect it, always, and unprotect it before you make a change in VBA?

[Juriemagic]

This is what I am trying to accomplish. Sheet "log" is always hidden, so now it must be unprotected so that data may be written to it. When, however this sheet is unhidden, visible, becomes the active sheet, it MUST be protected, to avoid any changes made to it. what I have so far is:

Private Sub Worksheet_Activate()
With Sheets("log").protect
End With
End Sub

this part works.


Private Sub Worksheet_Deactivate()
With Sheets("log").Unprotect
End With
End Sub


this part does not seem to work, because when I try to write to it when it's hidden, I get a message saying "the cell you're trying to edit is on a protected sheet"...

But if I remove these 2 codes and physically unprotect the sheet, and hide it, all writing happens without any problems..Will you PLEASE help me with this?

[Juriemagic]

Hi xld,

I have now also tried this, and exactly the same result.

Private Sub Worksheet_Activate()
With Sheets("LOG")
If .Visible = xlSheetVisible Then
.protect
Else
.Unprotect
End If
End With
End Sub

[Juriemagic]

xld,

I have a feeling that although the worksheet is hidden, and assuming it's unprotected, that when the submit button is clicked to write to the hidden "log" sheet, it still is "selected", or "activated", although it stays hidden, and I have a feeling that this then calls the "sheet_activate event to run, which immediately protects the sheet, and therefore the error to write. Do you think I might be on the right track with this view?

[Aflatoon]

I don't know why you didn't just carry on your question at MrExcel, but you just need:

Private Sub Worksheet_Activate()
Me.Protect
End Sub


Private Sub Worksheet_Deactivate()
Me.Unprotect
End Sub


in the code module for the Log sheet.

[Bob Phillips]

What I am suggesting is as below, in your code that makes change


With Worksheets("LOG")

.Unprotect

'your code that amends cells

.Protect
End With

[Juriemagic]

Well, to answer you truthfully, I started out on this forum, but waited almost the day out for response, (which I fully understand), but because of desperateness I thought to ask the question on MrExcel as well. I got a response almost right away. If you follow that thread you will notice I started losing track of things and called the whole thing off. This morning I feel recharged and saw a reply from xld, so I was obliged to respond. Apologies if I had caused any difficulties. To get back to the problem at hand, I have tried your code, but when the log sheet is hidden, and I write to it via macro, I get the same error as in post #5. Please help me with this, I am 24/7 on the net looking for answers but just no luck, as you are reading this, I'm still searching..

[Aflatoon]

How do you hide it - manually or in code? I've just tested the code and it works as expected.

FWIW I agree with Bob - that approach is simpler.

[Juriemagic]

xld,

Yes, I have this built into the code already which makes the change.
If ActiveSheet.Range("A100").Value = 1 Then Exit Sub
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Set copySheet = Worksheets("Update Room")
Set pasteSheet = Worksheets("Log")
copySheet.Unprotect
Range("A100").Value = 1
copySheet.Range("F3").Copy
pasteSheet.Unprotect
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
copySheet.Select
Range("A1").Select
copySheet.protect


and what I currently have in the log sheet is this :
Private Sub Worksheet_Activate()
Me.protect
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rChange As Range
Set rChange = Intersect(Target, Range("A:A"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
rCell.Offset(0, 1).Value = UserName()
rCell.Offset(0, 2).Value = Date & " " & Time()
Else
rCell.Offset(0, 1).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
Public Function UserName()
UserName = Environ$("UserName")
End Function
Private Sub Worksheet_Deactivate()
Me.Unprotect
End Sub


So..I don't know anymore..

[Juriemagic]

Aflatoon,

Log is hidden via code, I have 8 spreadsheets, and all 8 has 7 buttons with the names of the 7 hidden sheets. If I click any button, the sheet corresponding to the name of that button opens, and the sheet I was on, closes.

[p45cal]

For my suggestion in msg#2 above to work:
1. all other .protect/.unprotect code for that sheet needs to be removed, anywhere you may have put it in the code
2. you need to add the code, save and CLOSE the workbook, then reopen it, just once; after that, use the workbook as normal

Here's the code again, adjusted for the right sheet name, to be put in the Thisworkbook code-module:
Private Sub Workbook_Open()
Sheets("LOG").Protect userinterfaceonly:=True
End Sub

You can add some of the other arguments if you want to be certain exactly what is protected in the sheet, in case those are not as you expect (I think Excel remembers what options were used in the last protect operation (manual or coded) and re-applies them). Those arguments are listed Help and the link I gave: https://msdn.microsoft.com/en-us/library/office/ff840611.aspx

Perhaps I'm being thick, but it seems to me this is the most straightforward solution to your problem? You never have to think about the protection of that sheet in the code ever again.

[Juriemagic]

p45cal,

I am DEFINITELY going to look at that, will surely come back to you...thank you..

[Juriemagic]

p45cal,

you got it!..goodness, finally...it does exactly what I need!..thank you very very much!!, and also, I apologise for this long never-ending thread. To all you guys out there, thank you very much..