防止在Excel工作表上滚动

最新推荐文章于 2024-10-16 23:34:20 发布

culiao2169

最新推荐文章于 2024-10-16 23:34:20 发布

阅读量606

点赞数 1

文章标签： python linux java excel php

原文链接：https://contexturesblog.com/archives/2013/10/03/prevent-scrolling-on-excel-worksheet/

版权

Even if a worksheet is protected, you are able to scroll in any direction, and see what is on the sheet. To prevent scrolling on Excel worksheet, change the ScrollArea property for the worksheet, either manually or with a macro.

即使工作表受到保护，您也可以在任何方向上滚动并查看工作表上的内容。为防止在Excel工作表上滚动，请手动或使用宏更改工作表的ScrollArea属性。

不是安全功能 (Not a Security Feature)

Like other Excel protection settings, this is just a deterrent, or a navigation aid, rather than a security feature. Don’t depend on the ScrollArea setting to guard your worksheet!

与其他Excel保护设置一样，这只是一种威慑力或导航帮助，而不是安全功能。不要依赖ScrollArea设置来保护您的工作表！

NOTE: The Scroll Area has to be set each time the workbook opens. To automate this, use a macro that runs when the workbook opens. There is sample code below.

注意：每次打开工作簿时都必须设置滚动区域。若要使其自动化，请使用在工作簿打开时运行的宏。下面有示例代码。

手动设置滚动区域 (Set the Scroll Area Manually)

To see how the Scroll Area setting works, you can enter the range address manually.

要查看“滚动区域”设置的工作方式，您可以手动输入范围地址。

手动设置滚动区域： (To manually set the Scroll Area:)

On the Ribbon, click the Developer tab, and click Properties
在功能区上，单击“开发人员”选项卡，然后单击“属性”。

In the Properties window, enter a range address in the ScrollArea property box
在“属性”窗口中，在“ ScrollArea”属性框中输入一个范围地址

Click on the worksheet, and you will be restricted to clicking and scrolling in the Scroll Area.
单击工作表，您将只能在“滚动区域”中单击和滚动。

手动清除滚动区域： (To manually clear the Scroll Area:)

On the Ribbon, click the Developer tab, and click Properties
在功能区上，单击“开发人员”选项卡，然后单击“属性”。
In the Properties window, delete the address in the ScrollArea property box
在“属性”窗口中，删除ScrollArea属性框中的地址。
Click on the worksheet, and you will be able to click and scroll freely (assuming that the sheet is not protected).
单击工作表上，您将可以单击并自由滚动(假定工作表不受保护)。

用宏设置滚动区域 (Set Scroll Areas With a Macro)

The Scroll Area settings are cleared when you close the workbook, and you have to set them again, each time the file opens. You can use a macro to set them, as part of the Workbook_Open procedure.

当您关闭工作簿时，将清除“滚动区域”设置，并且每次打开文件时都必须再次进行设置。您可以使用宏来设置它们，作为Workbook_Open过程的一部分。

Copy this macro to a regular module in your workbook. It will set the Scroll area for each worksheet in the file, based on the Used Range on that sheet.

将此宏复制到工作簿中的常规模块。它将基于该工作表上的“使用范围”为文件中的每个工作表设置“滚动”区域。

Sub SetAllScrollAreas()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.ScrollArea = ws.UsedRange.Address
Next ws
End Sub

Add the macro name to the Worksheet_Open procedure in the ThisWorkbook module.

将宏名称添加到ThisWorkbook模块中的Worksheet_Open过程。

Private Sub Workbook_Open()
    SetAllScrollAreas
End Sub

Now, the ScrollArea settings will be added each time the file opens, if you enable macros.

现在，如果启用了宏，则每次打开文件时都会添加ScrollArea设置。

下载样本文件 (Download the Sample File)

To see the code and test worksheets, you can download my sample file. Go to the Worksheet Tips page on my Contextures site, and look for the Download Sample Files section.

要查看代码和测试工作表，可以下载我的示例文件。转到我的Contextures网站上的“ 工作表提示”页面，然后找到“下载示例文件”部分。