数据交互箭头_缺少数据验证箭头

最新推荐文章于 2024-10-03 09:02:12 发布

culiao2169

最新推荐文章于 2024-10-03 09:02:12 发布

阅读量165

点赞数

文章标签： python java linux 数据库 excel

原文链接：https://contexturesblog.com/archives/2018/10/11/missing-data-validation-arrows/

版权

数据交互箭头

To make data entry easier, you can create drop down lists in a worksheet, using Excel's data validation. Usually, those lists are trouble free, but sometimes the arrows disappear, for no apparent reason. See some of the reasons for that behaviour, and how to fix or avoid the problems.

为了简化数据输入，您可以使用Excel的数据验证在工作表中创建下拉列表。通常，这些列表没有问题，但是有时箭头会消失，没有明显的原因。查看该行为的一些原因，以及如何解决或避免这些问题。

箭遗失的原因 (Reasons for Missing Arrows)

In the past, I've posted details on some of the reasons for missing arrows. Sometimes, it's a simple thing, like an unchecked option in the validation settings.

过去，我发布了有关丢失箭头的一些原因的详细信息。有时，这很简单，例如验证设置中未选中的选项。

But things that don't seem related to data validation can cause missing arrows too. For example, Freeze Panes can make them disappear, in some situations.

但是似乎与数据验证无关的事情也会导致箭头丢失。例如，在某些情况下，“冻结窗格”可以使它们消失。

Even a linked picture can make the arrows disappear, in some versions of Excel.

在某些版本的Excel中，即使链接的图片也可以使箭头消失。

宏和丢失的数据验证箭头 (Macros and Missing Data Validation Arrows)

Last week, I added another cause to the Missing Arrows page -- macros that accidentally delete those arrows.

上周，我在“箭头丢失”页面中添加了另一个原因- 宏，这些宏意外删除了这些箭头。

If you run a macro that deletes shapes on a worksheet, it might also delete the drop down arrow. Excel sees that arrow as a worksheet shape.

如果您运行一个宏，该宏将删除工作表上的形状，则它也可能会删除下拉箭头。 Excel将该箭头视为工作表形状。

Here is my sample sheet, with a circle, a rectangle, and a drop down list.

这是我的样本表，带有一个圆形，一个矩形和一个下拉列表。

删除形状的宏 (Macro to Delete Shapes)

The sample macro below has a line of code – "sh.Delete", and it runs that code for each shape on the active sheet.

下面的示例宏有一行代码–“ sh.Delete”，它为活动工作表上的每个形状运行该代码。

Sub DeleteShapesALL()
'WARNING: Deletes data val arrow
'         if it is visible
Dim sh As Shape
Dim ws As Worksheet
Set ws = ActiveSheet
For Each sh In ws.Shapes
  sh.Delete
Next sh
End Sub

I ran that macro in my sample worksheet, and you can see the results below. The circle and rectangle are gone, and the data validation arrow is missing too!

我在示例工作表中运行了该宏，您可以在下面看到结果。圆和矩形不见了，数据验证箭头也丢失了！

已删除的箭头无法还原 (Deleted Arrow Can't Be Restored)

There is only one data validation arrow on each Excel worksheet, and it appears in the active cell, if that cell has a drop down list.

每个Excel工作表上只有一个数据验证箭头，并且该箭头显示在活动单元格中(如果该单元格具有下拉列表)。

If your macro accidentally deletes the data validation arrow on a worksheet, I don't know of any way to get an arrow back on that worksheet.

如果您的宏意外删除了工作表上的数据验证箭头，则我不知道有什么方法可以使该工作表上的箭头返回。

Even if you copy a working drop down list from a different sheet, the arrow doesn't appear on the old sheet.

即使您从其他工作表中复制工作下拉列表，箭头也不会出现在旧工作表上。

You'll have to insert a new sheet, and copy all your data, formulas and formatting onto the new sheet.

您必须插入一个新工作表，然后将所有数据，公式和格式复制到新工作表上。

安全删除形状 (Delete Shapes Safely)

To delete other worksheet shapes safely, without deleting the data validation arrows, don't use a macro with the simple Delete method shown above.

为了安全地删除其他工作表形状而不删除数据验证箭头，请不要将宏与上面显示的简单Delete方法一起使用。

Instead, use one of the macros to delete objects on Ron de Bruin's website. His code checks the type for each shape, and doesn't delete the data validation arrows.

而是使用宏之一删除 Ron de Bruin网站上的对象。他的代码检查每种形状的类型，并且不删除数据验证箭头。