英文:
VBA code to protect worksheet but allow filtering
问题
以下是代码的中文翻译部分：
Private Sub Workbook_Open()
MsgBox "已更新文件至 3 月 23 日"
With Sheets("3 月 23 日")
.Protect AllowFiltering:=True
.Protect Password:="密码", UserInterfaceOnly:=True
.EnableOutlining = True
End With
End Sub
英文:
I have a worksheet where i want to limit changes made by users but also allow columns to be grouped so i used the following code. I then realised i can't filter on the protected sheet so amended the code but it's still not allowing to filter and not sure where i have gone wrong as new to vba
Private Sub Workbook_Open()
MsgBox "File updated for March 23"
With Sheets("March 23")
.Protect AllowFiltering:=True
.Protect Password:="Secret", UserInterfaceOnly:=True
.EnableOutlining = True
End With
End Sub
答案1
得分: 1
您可以通过设置 AllowFiltering:=True 来允许用户筛选数据，但是在保护工作表之前，您需要激活筛选（显示筛选按钮），因为此命令在受保护的工作表上不可用：
Private Sub Workbook_Open()
With Sheets("March 23")
If Not .AutoFilterMode Then .AutoFilter
.Protect AllowFiltering:=True, _
Password:="Secret", _
UserInterfaceOnly:=True
.EnableOutlining = True
End With
End Sub
英文:
You can allow the users to filter the data by setting AllowFiltering:=True, however, you will need to activate the filter (show the filter buttons) before the sheet is protected as this command is not available on a protected sheet:
Private Sub Workbook_Open()
With Sheets("March 23")
If Not .AutoFilterMode then .AutoFilter
.Protect AllowFiltering:=True, _
Password:="Secret", _
UserInterfaceOnly:=True
.EnableOutlining = True
End With
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论