Need help with additing additional parameters for VBA code

crossfire266

New Member
Joined
May 12, 2019
Messages
2
Hi everyone!

Hoping to get some help with the following issue..

I have the VBA code (below) which will protect and unlock grouping options for a excel sheet, however I would like to add the option for user to edit/add cells, use filter options too .. pretty much get all the functionality in a protect sheet ticked out.

Can anyone assist me with this on letting me know where I should add the code to allow the extra functionality for a protected sheet?

Current code:

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

Thanks in advance,
Jo
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,182
Office Version
2007
Platform
Windows
Try this

Code:
Sub Workbook_Open()
    'Update 20140603
    Dim xWs As Worksheet
    Set xWs = Application.ActiveSheet
    Dim xPws As String
    xPws = "225" ''Application.InputBox("Password:", xTitleId, "", Type:=2)
    xWs.Protect Password:=xPws, _
        DrawingObjects:=False, Contents:=True, _
        Scenarios:=False, Userinterfaceonly:=True, _
        AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, _
        AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, _
        AllowDeletingColumns:=True, AllowDeletingRows:=True, _
        AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True
    
    xWs.EnableOutlining = True
End Sub
 

crossfire266

New Member
Joined
May 12, 2019
Messages
2
Legend this worked!

Much Appreciated ;)!

Try this

Code:
Sub Workbook_Open()
    'Update 20140603
    Dim xWs As Worksheet
    Set xWs = Application.ActiveSheet
    Dim xPws As String
    xPws = "225" ''Application.InputBox("Password:", xTitleId, "", Type:=2)
    xWs.Protect Password:=xPws, _
        DrawingObjects:=False, Contents:=True, _
        Scenarios:=False, Userinterfaceonly:=True, _
        AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, _
        AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, _
        AllowDeletingColumns:=True, AllowDeletingRows:=True, _
        AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True
    
    xWs.EnableOutlining = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,102,345
Messages
5,486,323
Members
407,539
Latest member
ltwkuav

This Week's Hot Topics

Top