Excel组合框编号问题

Let's file today's blog's post under "Obscure Excel Problems".  I heard from Y.B., who was using my Data Validation Combo Box, and numbers were being entered as text. The code is supposed to fix them, so why was it failing?

让我们在“晦涩的Excel问题”下归档今天的博客文章。 我从正在使用我的数据验证组合框的YB那里收到消息，并且正在输入数字作为文本。 该代码应该修复它们，所以为什么会失败？

查看组合框的工作方式 (See How the Combo Box Works)

This video shows how the combo box works, when you double-click a data validation cell. The written details are below.

该视频显示了双击数据验证单元时组合框的工作方式。 书面细节如下。

演示地址

下拉列表 (Drop Down Lists)

On my website, you can learn how to make drop down lists on a worksheet, using Data Validation. That's a nice enhance to a data entry sheet, and helps reduce random errors.

在我的网站上，您可以学习如何使用数据验证在工作表上创建下拉列表 。 这对数据输入表是一个很好的增强，并有助于减少随机错误。

清单来源 (Source for the List)

The items in the drop down come from a list in the workbook. You can refer to that list by its address, such as $A$1:$A$12, or name the range, and refer to that name when setting up the drop down list.

下拉菜单中的项目来自工作簿中的列表。 您可以按其地址引用该列表，例如$ A $ 1：$ A $ 12，或命名范围 ，并在设置下拉列表时引用该名称。

下拉列表限制 (Drop Down List Limitations)

As wonderful as the in-cell drop down lists are, they do have a few limitations:

与单元内下拉列表一样好，它们确实有一些限制：

• list font can't be changed
  列表字体无法更改
• number of visible rows can't be changed (maximum of 8)
  可见行数不能更改(最多8行)
• no AutoComplete feature to find matching items in the list as you start to type
  开始输入时没有自动完成功能以在列表中查找匹配项

Those limitations can be a problem if you reduce the zoom, or if you have a long list of items.

如果缩小缩放比例或项目清单很长，那么这些限制可能会成为问题。

数据验证组合框 (Data Validation Combo Box)

To work around those limitations, I created the Data Validation Combo Box sample file for my website, and instructions on how to use it. The data entry sheet has one hidden combo box (ActiveX control), and it appears if you double-click on a cell with a drop down list.

为了解决这些限制，我为我的网站创建了数据验证组合框示例文件，并说明了如何使用它。 数据输入表有一个隐藏的组合框(ActiveX控件)，如果您双击带有下拉列表的单元格，它将出现。

组合框编号问题 (Problem With Combo Box Numbers)

The ActiveX combo box works well with text values, but not so well with numbers, dates and times. Even if the worksheet cell is formatted for those values, the combo box adds the value as text, and ignores your formatting.

ActiveX组合框适用于文本值，但不适用于数字，日期和时间。 即使工作表单元格的格式设置为这些值，组合框也会将该值添加为文本，并且会忽略您的格式设置。

代码修复文本编号 (Code Fixes Text Numbers)

To deal with that problem, there is an extra section of code on my instruction page. That code runs when you press the Tab or Enter key in the Combo Box.

为了解决该问题，我的说明页上有一段额外的代码 。 当您在组合框中按Tab或Enter键时，该代码将运行。

On Error Resume Next
'change text value to number, if possible
varVal = --ActiveCell.Value
If IsEmpty(varVal) Then
  varVal = ActiveCell.Value
End If

来源清单包含公式 (Source List Has Formulas)

In Y.B.'s workbook, there was no problem if he use typed numbers in his source list. However, with a formula in the source list, the numbers stayed as text.

在YB的工作簿中，如果他在源列表中使用键入的数字就没有问题。 但是，在源列表中有一个公式时，数字保留为文本。

Here is Y.B.'s formula, entered in cells A2:A7:

这是在单元格A2：A7中输入的YB公式：

=YEAR(TODAY())+ROWS($A$2:A2)-1

= YEAR(TODAY())+ ROWS($ A $ 2：A2)-1

I tested it, and confirmed problem -- after selecting a number from the combo box, it was aligned at the left of the cell. An error alert warned that the cell had a Number Stored as Text.

我对其进行了测试，并确认了问题-从组合框中选择一个数字后，该数字在单元格的左侧对齐。 错误警报警告该单元格有一个数字存储为文本。

测试其他公式 (Testing Other Formulas)

A formula in the source list shouldn't cause a problem, so I tried a different formula.  I typed a number in cell A2, and this formula in A3:A7.

源列表中的公式应该不会引起问题，因此我尝试了其他公式。 我在单元格A2中键入了一个数字，在A3：A7中键入了此公式。

=A2+1

= A2 + 1

That formula was fine – the combo box code changed them to numbers.

该公式很好–组合框代码将其更改为数字。

问题功能 (Problem Functions)

So, if a simple formula works, the problem must be with one of the functions in Y.B.'s formula.

因此，如果一个简单的公式起作用，那么问题肯定出在YB公式中的函数之一上。

=YEAR(TODAY())+ROWS($A$2:A2)-1

= YEAR(TODAY())+ ROWS($ A $ 2：A2)-1

After a few tests, I narrowed the problem down to the TODAY function. It's volatile, so maybe that's the issue.

经过几次测试后，我将问题缩小到了TODAY函数。 它是易变的，所以也许就是问题所在。

To test that, I added a different volatile function:

为了测试这一点，我添加了一个不同的volatile函数：

=A2+1+(RAND()*0)

= A2 + 1 +(RAND()* 0)

That caused the same result in the combo box – the numbers stayed as text.

这在组合框中产生了相同的结果–数字保留为文本。

不要使用易失性函数 (Don't Use Volatile Functions)

The simple solution to this problem is to avoid using volatile functions in the source list for a combo box.

此问题的简单解决方案是避免在组合框的源列表中使用易失函数。

They seem to lock the active cell, and its value can't be changed from text to a real number. I don't know why that happens. Do you have any ideas?

它们似乎锁定了活动单元，并且其值不能从文本更改为实数。 我不知道为什么会这样。 你有什么想法？

添加代码以解决问题 (Add Code to Fix the Problem)

If you absolutely must use a volatile function, then only solution I could find is to add code to move off the cell, then go back to it, fix the value, and then move off again. Of course, those extra steps could cause a short delay when the code runs.

如果您绝对必须使用volatile函数，那么我能找到的唯一解决方案是添加代码以移出该单元格，然后返回到该单元格，固定该值，然后再次移出。 当然，这些额外的步骤可能会在代码运行时造成短暂的延迟。

Here's the original code for the Tab key:

这是Tab键的原始代码：

  Case 9  'tab
    ActiveCell.Value = varVal
    ActiveCell.Offset(0, 1).Activate

And here is the revised code:

这是修改后的代码：

  Case 9  'tab
    ActiveCell.Offset(0, 1).Activate
    ActiveCell.Offset(0, -1).Activate
    ActiveCell.Value = varVal
    ActiveCell.Offset(0, 1).Activate

You could make a similar change to the Enter key code, if you run into this problem in your workbook.

如果您在工作簿中遇到此问题，则可以对Enter键代码进行类似的更改。

翻译自: https://contexturesblog.com/archives/2017/06/22/excel-combo-box-number-problem/