VBA to allow grouping +- after reopening of the sheet

[Greenbehindthecells]

Hey there.
I have been trying various VBA code from tutorials to allow my sheet to be password protected (this sheet is full of formulas, The only part of the sheet that should be editable is D3 and G3 for data validation drop-downs).


Process: sheet is not password protected, developer tab, visual basic,select sheet, insert module, select Thisworkbook, paste, edit password in VBA code, save, F5. Immediately the worksheet is password protected and plus minus grouping is available for use. Save file. Reopen file, error 'You cannot use this command on a protected sheet...'


This code was promising, meaning it actually worked and allowed the plus and minus selections for grouping, however once you close the file, it no longer works and the error comes up 'you cannot do in password protected...".
VBA Source
VBA:
Sub Workbook_Open()
'Update 20140603
Dim xWs As Worksheet
Set xWs = Application.ActiveSheet
Dim xPws As String
xPws = "rtc" ''Application.InputBox("Password:", xTitleId, "", Type:=2)
xWs.Protect Password:=xPws, Userinterfaceonly:=True
xWs.EnableOutlining = True
End Sub

In a below message on this tutorial, a user wrote additional steps: first to keep the sheet where the code is needed to work open but to place the code in This workbook, not the worksheet.

This worked, but again once I close the worksheet and reopened it it was back to the same message. I've seen many different requests for help with this kind of thing all with different parameters; some users want certain sections to be editable, some wants the formatting to be open for users... Really just need this whole sheet lockdown except for the grouping section expansions, D3 &G3.

This post has VBA code, which I added to This workbook, The window comes up asking to password protect, I enter the password, click F5, and that results in a runtime error 1004.

I'm not sure what I'm doing wrong, and I guess I'm doing a lot wrong. Any advice or different code that might resolve this would be greatly appreciated. Thank you.





EddieYeah

about 4 years ago
#27727

Someone might need this, I think I figured out how to make this work.

First, your code needs to be written in "ThisWorkbook" under Microsoft Excel Objects, as @peachyclean suggests.
Second, take the code that @Sravanthi wrote, and paste to the above mentioned location.

Sub Workbook_Open()
'Update 20140603
Dim xWs As Worksheet
Set xWs = Application.ActiveSheet
Dim xPws As String
xPws = "rfc" ''Application.InputBox("Password:", xTitleId, "", Type:=2)
xWs.Protect Password:=xPws, Userinterfaceonly:=True
xWs.EnableOutlining = True
End Sub

The thing is that you need to be on the sheet which you want to protect but allowing using grouping, and save the workbook and close, without protecting. Now if you open it, the macro starts automatically, it will make the sheet protected with the password "rfc". Now you can use the grouping, the sheet is protected.

For my solution, I've modified the password applied, so you can rewrite any password HERE:
xPws = "WRITEANYPASSWORDHERE" ''Application.InputBox("Password:", xTitleId, "", Type:=2)

Furthermore, I didn't want the to-be-protected sheet active when opening the file, therefore I've modified this part:
Set xWs = Application.ActiveSheet ->
Set xWs = Application.Worksheets("WRITEANYSHEET'SNAMEHERE")

Now it works like charm, sheet named 'WRITEANYSHEET'SNAMEHERE' is protected but the grouping applicable. On the long run, I think the problem will be that if I want to modify this file and keep the solution, I need to unprotect this sheet to make it work on the next opening. I guess you can write another macro to automatically unprotect when closing


I hope it helped.

 

[HighAndWilder]

Hey there.
I have been trying various VBA code from tutorials to allow my sheet to be password protected (this sheet is full of formulas, The only part of the sheet that should be editable is D3 and G3 for data validation drop-downs).


Process: sheet is not password protected, developer tab, visual basic,select sheet, insert module, select Thisworkbook, paste, edit password in VBA code, save, F5. Immediately the worksheet is password protected and plus minus grouping is available for use. Save file. Reopen file, error 'You cannot use this command on a protected sheet...'


This code was promising, meaning it actually worked and allowed the plus and minus selections for grouping, however once you close the file, it no longer works and the error comes up 'you cannot do in password protected...".
VBA Source
VBA:
Sub Workbook_Open()
'Update 20140603
Dim xWs As Worksheet
Set xWs = Application.ActiveSheet
Dim xPws As String
xPws = "rtc" ''Application.InputBox("Password:", xTitleId, "", Type:=2)
xWs.Protect Password:=xPws, Userinterfaceonly:=True
xWs.EnableOutlining = True
End Sub

In a below message on this tutorial, a user wrote additional steps: first to keep the sheet where the code is needed to work open but to place the code in This workbook, not the worksheet.

This worked, but again once I close the worksheet and reopened it it was back to the same message. I've seen many different requests for help with this kind of thing all with different parameters; some users want certain sections to be editable, some wants the formatting to be open for users... Really just need this whole sheet lockdown except for the grouping section expansions, D3 &G3.

This post has VBA code, which I added to This workbook, The window comes up asking to password protect, I enter the password, click F5, and that results in a runtime error 1004.

I'm not sure what I'm doing wrong, and I guess I'm doing a lot wrong. Any advice or different code that might resolve this would be greatly appreciated. Thank you.





EddieYeah

about 4 years ago
#27727

Someone might need this, I think I figured out how to make this work.

First, your code needs to be written in "ThisWorkbook" under Microsoft Excel Objects, as @peachyclean suggests.
Second, take the code that @Sravanthi wrote, and paste to the above mentioned location.

Sub Workbook_Open()
'Update 20140603
Dim xWs As Worksheet
Set xWs = Application.ActiveSheet
Dim xPws As String
xPws = "rfc" ''Application.InputBox("Password:", xTitleId, "", Type:=2)
xWs.Protect Password:=xPws, Userinterfaceonly:=True
xWs.EnableOutlining = True
End Sub

The thing is that you need to be on the sheet which you want to protect but allowing using grouping, and save the workbook and close, without protecting. Now if you open it, the macro starts automatically, it will make the sheet protected with the password "rfc". Now you can use the grouping, the sheet is protected.

For my solution, I've modified the password applied, so you can rewrite any password HERE:
xPws = "WRITEANYPASSWORDHERE" ''Application.InputBox("Password:", xTitleId, "", Type:=2)

Furthermore, I didn't want the to-be-protected sheet active when opening the file, therefore I've modified this part:
Set xWs = Application.ActiveSheet ->
Set xWs = Application.Worksheets("WRITEANYSHEET'SNAMEHERE")

Now it works like charm, sheet named 'WRITEANYSHEET'SNAMEHERE' is protected but the grouping applicable. On the long run, I think the problem will be that if I want to modify this file and keep the solution, I need to unprotect this sheet to make it work on the next opening. I guess you can write another macro to automatically unprotect when closing


I hope it helped.

Give this a try.

Put this code into the worksheet code module.

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.CountLarge > 1 Then
    Exit Sub
  End If

  If Intersect(Target, Range("D3")) Is Nothing And _
    Intersect(Target, Range("G3")) Is Nothing Then
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
  End If

End Sub

 

[Greenbehindthecells]

Give this a try.

Put this code into the worksheet code module.

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.CountLarge > 1 Then
    Exit Sub
  End If

  If Intersect(Target, Range("D3")) Is Nothing And _
    Intersect(Target, Range("G3")) Is Nothing Then
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
  End If

End Sub

Thank you. Do I still continue with the steps
Sheet is not password protected, developer tab, visual basic,select sheet, insert module, select Thisworkbook, paste, save, F5?

F5 brings up the macro window and there is no macro shown to select, so I have to cancel.

 

[Greenbehindthecells]

Also, I have protect sheet set up to allow users to select lock cells and select unlock cells. D3 and G3 have been unlocked, well every other cell in the worksheet has been locked. I'm not sure if that is a part of my problem, so I just wanted to put that here. Thank you.

 

[HighAndWilder]

Thank you. Do I still continue with the steps
Sheet is not password protected, developer tab, visual basic,select sheet, insert module, select Thisworkbook, paste, save, F5?

F5 brings up the macro window and there is no macro shown to select, so I have to cancel.

Try it just by itself and see if it gives you what you need.

 

[Greenbehindthecells]

Try it just by itself and see if it gives you what you need.

Good morning. I am sorry for not explaining properly. I did try this using the steps that I have been doing, when I press F5, a macro screen appears, however there is no macro to run with the code. I'm not sure if my steps were incorrect, so I added them to see if it's user error.

With this code, the code does not start if I don't press F5. If I save the workbook and reopen it, the sheet is not protected. If I protect the sheet, the grouping ungrouping is unavailable.

 

[Greenbehindthecells]

Try it just by itself and see if it gives you what you need.

As a follow up from using your code by itself and having the issue of the code not starting on workbook open (after closing) and the group +- not being usable on sheet protection, I tried to combine code that allowed group +- use on sheet protection with your code to see if that would work.

This workbook will most likely be opened (and closed) a few times a day... our work laptops do not have the RAM needed to keep large excel files with this much data and formulas open indefinitely and I have at least 3 large data heavy workbooks needed for different workflows. If I remove the first line 'Private Sub Worksheet_Change by val Target as Range' and replace with ''Private Sub Workbook_open()' it ends with a compile error. From what I understand, the code that allows the use of grouping +- with sheet protection may run once added and f5, but does not run on workbook open, which is what I need so the users don't delete all the very complex formulas I have to make this dashboard.

Its obvious I have no idea how to frankenstien code to get things to work, because I don't know how to code. I am sorry if this is not a helpful post, just thought my trails (and fails) might help show how hard I am trying to make things work. Thank you again for trying to help me.

Sub EnableOutliningWithProtection()
ActiveSheet.Protect Password:="password", UserInterfaceOnly:=True
ActiveSheet.EnableOutlining = True
ActiveSheet.Unprotect "password"
ActiveSheet.Protect "password"

If I add 'Private Sub Workbook_open()' and f5, the code does not work (encasing below code).

 

[Greenbehindthecells]

Thank you for your help.
I will use this code to allow grouping+- upon my protected sheet (Sub EnableOutliningWithProtection()
ActiveSheet.Protect Password:="password", UserInterfaceOnly:=True
ActiveSheet.EnableOutlining = True
ActiveSheet.Unprotect "password"
ActiveSheet.Protect "password") and will instruct users to alt+f8 and run macro upon opening.