Greenbehindthecells
Board Regular
- Joined
- May 9, 2023
- Messages
- 50
- Office Version
- 365
- Platform
- Windows
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![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
I hope it helped.
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.