vb组合框回车添加项目
 

  
There are a few versions of the data validation combo box technique on my website. The files use programming to make a combo box appear when you click, or double-click, on a cell that has a drop down list. The latest version shows how to add new items to data validation combo box lists.
 
  
 我的网站上有几种版本的数据验证组合框技术。 在具有下拉列表的单元格上单击或双击时，文件将使用编程方式使组合框出现。 最新版本显示了如何向数据验证组合框列表中添加新项目。 
 
  
 组合框的好处 (Combo Box Benefits)
 
  
The advantage to using a combo box is that you can show the text in a larger font, and it autocompletes as you type, unlike a cell with a drop down list.
 
  
 使用组合框的优势在于，您可以使用较大的字体显示文本，并且键入时它会自动完成，这与带有下拉列表的单元格不同。 
 
  

   
  
  
 将新项目添加到下拉列表 (Add New Item to Drop Down List)
 
  
Another one of my sample files let you add new items to a drop down list, on the fly. If you type a new item, a message box asks if you want to add it. Then, if you click Yes, it’s put into the list, in alphabetical order.
 
  
 我的另一个示例文件可让您随时将新项目添加到下拉列表中。 如果键入新项目，则会出现一个消息框，询问您是否要添加它。 然后，如果您单击“是”，则会按字母顺序将其放入列表。 
 
  

   
  
  
 将新项目添加到组合框 (Add New Items to Combo Box)
 
  
Someone recently asked if it was possible to combine those techniques, so I’ve created a new sample file that does just that! When you click on a cell that has a data validation list, a combo box appears. If you type an item that isn’t in the list, you’re prompted to add it.
 
  
 最近有人问是否可以结合使用这些技术，所以我创建了一个新的示例文件来实现这一目的！ 当您单击具有数据验证列表的单元格时，将出现一个组合框。 如果您键入不在列表中的项目，则会提示您添加它。 
 
  
NOTE: The Add New Item code is triggered when you press Tab or Enter to get out of the Combo box.
 
  
 注意：当您按Tab或Enter退出组合框时，将触发“添加新项”代码。 
 
  
To see the file, and how it works, you can download it from my Contextures website. On the Excel Samples page, look for DV0062 - Data Validation Click Combobox - Add New Items.
 
  
 若要查看文件及其工作方式，可以从我的Contextures网站下载它。 在“ Excel样本”页面上，查找DV0062-数据验证单击组合框-添加新项目 。 
 
  
The file is in xls format, and contains macros. ___________________
 
  
 该文件为xls格式，并包含宏。 ___________________ 
 
 
 

  
翻译自: https://contexturesblog.com/archives/2013/12/19/add-new-items-to-data-validation-combo-box/
 
 
vb组合框回车添加项目

 
excel 中vb组合框
 

  
Instead of selecting a product code in an Excel drop down list, it's usually easier to select a product name instead. However, your pricing calculations might use the product code, instead of the product name, so you need that information.
 
  
 与其在Excel下拉列表中选择产品代码，通常不如选择一个产品名称。 但是，您的定价计算可能会使用产品代码而不是产品名称，因此您需要该信息。 
 
  
With a bit of programming in Excel, you can show a description in a combo box, but enter the matching code for the selected item into the cell. You've seen other versions of my data validation combo box code:
 
  
 通过Excel中的一些编程，您可以在组合框中显示说明，但是将所选项目的匹配代码输入到单元格中。 您已经看到了我的数据验证组合框代码的其他版本： 
 
  
Double-click on data validation cell to open combo box
 双击数据验证单元格以打开组合框 
Click on data validation cell to open combo box
 单击数据验证单元格以打开组合框 
Use named lists with data validation combo box
 将命名列表与数据验证组合框一起使用 
  
Keep reading, to see how the new combo box codes example works.
 
  
 继续阅读，以了解新的组合框代码示例如何工作。 
 
  
 从组合框中选择 (Select From a Combo Box)
 
  
I've uploaded a new sample file on the Contextures website, which lets you select a month name, or weekday name, from an Excel combo box.
 
  
 我已经在Contextures网站上上传了一个新的示例文件，该文件可让您从Excel组合框中选择月份名称或工作日名称。 
 
  

   
  
  
When you leave the combo box, the month number, or weekday code is automatically entered in the cell, instead of the full description.
 
  
 当您离开组合框时，单元格中会自动输入月号或工作日代码，而不是完整的说明。 
 
  

   
  
  
 命名范围 (The Named Ranges)
 
  
In the data validation cells, lists are allowed, and they refer to the named ranges – DayList and MonthList – where the short codes are stored.
 
  
 在数据验证单元格中，允许使用列表，它们引用存储短代码的命名范围– DayList和MonthList。 
 
  

   
  
  
Another range is created for each list, to include the description column, and these names end with "Codes" – DayListCodes and MonthListCodes.
 
  
 为每个列表创建另一个范围，以包括描述列，并且这些名称以“代码”结尾– DayListCodes和MonthListCodes。 
 
  
These "Codes" ranges are used to fill the combo box, which is formatted with 2 columns. The first column has a width of zero, so the codes aren't visible.
 
  
 这些“代码”范围用于填充组合框，该组合框的格式为2列。 第一列的宽度为零，因此看不到代码。 
 
  

   
  
  
 下载样本文件 (Download the Sample File)
 
  
To test the combo box codes example, and see how it works, you can download the sample file from the Contextures website.
 
  
 若要测试组合框代码示例，并查看其工作方式，可以从Contextures网站下载示例文件。 
 
  
In the Data Validation section look for DV0057 – Data Validation Combobox Codes.
 
  
 在“数据验证”部分中查找“ DV0057 –数据验证组合框代码” 。 
 
  
The file is in Excel 2007/2010 format, and contains macros. ______________________
 
  
 该文件为Excel 2007/2010格式，包含宏。 ______________________ 
 
 
 

  
翻译自: https://contexturesblog.com/archives/2012/05/03/excel-data-validation-combo-box-codes/
 
 
excel 中vb组合框

 
excel 中vb组合框
 

  
 Excel组合框上的标题 (Headers on Excel Combo Boxes)
 
 
 
 
 
 
 

  
You can use combo boxes to show a drop down list, either in a UserForm, or on a worksheet. Sometimes a list is easy to understand, like this one, which shows a part number and part name.
 
  
 您可以使用组合框在UserForm或工作表上显示下拉列表。 有时像这样的清单很容易理解，其中显示了零件编号和零件名称。 
 
  

  

   
  
 
  
 
  
If the list has multiple columns, it might help if there is a heading for each column, like the one in the next screen shot.
 
  
 如果列表有多列，则每列都有一个标题可能会有所帮助，例如下一个屏幕截图中的标题。 
 
  

  

   
  
 
  
 
  
You could probably figure it out without the headings, but it removes any potential confusion, if you show the labels at the top of the list. 
 
  
 您可能会想出没有标题的方法，但是如果您将标签显示在列表的顶部，它将消除任何潜在的混乱。 
 
  
 组合框源 (The Combo Box Source)
 
  
If you decide to show column headings, the text in the row above the row source will be used as headings.
 
  
 如果决定显示列标题，则行源上方行中的文本将用作标题。 
 
  
In this example, the row source is a named range – MonthTable – which is based on a formatted table’s data range. The named range doesn’t include the table’s heading row.
 
  
 在此示例中，行源是一个命名范围– MonthTable –基于格式化表的数据范围。 命名范围不包括表的标题行。 
 
  

  

   
  
 
  
 
  
NOTE: If the source data starts in row 1, the column letters will show as the combo box headings.
 
  
 注意：如果源数据从第1行开始，则列字母将显示为组合框标题。 
 
  
 打开柱头 (Turn on Column Heads)
 
  
To show the column headings for a UserForm combo box, follow these steps.
 
  
 要显示UserForm组合框的列标题，请按照下列步骤操作。 
 
  
In the Visual Basic Editor (VBE), select the combo box
 在Visual Basic编辑器(VBE)中，选择组合框 
In the properties window, change the ColumnHeads property to True.
 在属性窗口中，将ColumnHeads属性更改为True。 
You can also set the ColumnWidths property, to make sure there is enough room for the data in each column. This also ensures that the headings line up correctly with the column items.
 您还可以设置ColumnWidths属性，以确保每列中的数据都有足够的空间。 这还可以确保标题与列项目正确对齐。 
  

  

   
  
 
  
 
  
 下载样本文件 (Download the Sample File)
 
  
To see how the headings work, you can download the sample file from my Combo Box Tips page.
 
  
 要查看标题是如何工作的，可以从“ 组合框提示”页面下载示例文件。 
 
  
The zipped file is in xlsm format, and contains macros, so enable them if you want to test the UserForm.
 
  
 压缩文件为xlsm格式，包含宏，因此如果要测试UserForm，请启用它们。 
 
  
 您会问微软的Excel团队什么？ (What Would You Ask Microsoft’s Excel Team?)
 
  
Do you have questions about UserForms, or Combo Boxes, or any other Excel features?
 
  
 您是否对UserForms，组合框或任何其他Excel功能有疑问？ 
 
  
Tomorrow, Friday, April 4th, Microsoft’s Excel team will be hosting an AMA (Ask Me Anything) on Reddit. The live interaction starts at 4 PM Eastern Time (1 PM Pacific).
 
  
 明天，4月4日，星期五，Microsoft的Excel团队将在Reddit上托管AMA(“问我什么”) 。 实时互动从美国东部时间下午4点(太平洋时间下午1点)开始。 
 
  
Starting thinking about your questions now, so you’ll have them ready to post tomorrow. It should be fun, and informative!
 
  
 现在就开始考虑您的问题，以便明天准备好发布。 它应该很有趣，而且内容丰富！ 
 
  
Here’s a picture they made in Excel, to celebrate this event.
 
  
 这是他们在Excel中制作的庆祝活动的图片。 
 
 
 

  
翻译自: https://contexturesblog.com/archives/2014/04/03/headers-on-excel-combo-boxes/
 
 
excel 中vb组合框

 
excel 中vb组合框
 

  
Last week, I was updating my web page that shows how to show a combo box in a cell that has a drop down list. Here's a screen shot of that workbook.
 
  
 上周，我正在更新我的网页，该网页显示了如何在具有下拉列表的单元格中显示组合框 。 这是该工作簿的屏幕截图。 
 
  

   
  
  
The combo box has several advantages over the default data validation list. You can:
 
  
 与默认数据验证列表相比，组合框具有多个优点。 您可以： 
 
  
change the font size,
 更改字体大小， 
increase the number of visible rows
 增加可见行数 
start typing an entry, and it autocompletes, if a match is found
 开始输入一项，如果找到匹配项，它将自动完成 
  
I’ve used this technique in several versions of Excel, and never had problems – until now.
 
  
 我在多个版本的Excel中都使用了此技术，直到现在都从未遇到过问题。 
 
  
 点击并崩溃 (Click and Crash)
 
  
Because you can set the font size in a combo box, it should work better if the worksheet is zoomed below 100%. At lower zoom settings, it can be hard to read those tiny letters in the data validation list, so a larger font helps.
 
  
 因为您可以在组合框中设置字体大小，所以如果将工作表缩放到100％以下，效果会更好。 在较低的缩放设置下，可能很难读取数据验证列表中的那些小字母，因此较大的字体会有所帮助。 
 
  
To show the difference in font sizes, I zoomed the worksheet to 70%, then clicked on the arrow in the combo box. Excel 2013 crashed. I tried it a few more times, and it crashed again and again.
 
  
 为了显示字体大小的差异，我将工作表放大到70％，然后单击组合框中的箭头。 Excel 2013崩溃了。 我尝试了几次，然后又一次崩溃了。 
 
  

   
  
  
I tried the same thing in Excel 2010, where that old familiar message appeared – “Not enough system resources to display correctly”. And then Excel 2010 crashed.
 
  
 我在Excel 2010中尝试了相同的操作，在该处出现了熟悉的旧消息–“系统资源不足，无法正确显示”。 然后，Excel 2010崩溃了。 
 
  
 问题在2000年解决了吗？ (The Problem Was Fixed in 2000?)
 
  
After a lengthy Google search, I found a couple of forum posts that linked to an MSKB article about this problem. Apparently this article listed the conditions that led to the error, and had a workaround.
 
  
 经过长时间的Google搜索，我发现了一些论坛帖子，这些帖子链接到有关此问题的MSKB文章。 显然，本文列出了导致错误的条件，并且有一种解决方法。 
 
  
Unfortunately, that article is retired, because the problem was solved in Excel 2000. Well, it seems to be back, on my machine, at least. I’m on a Windows 8 computer, using Excel 2013, so that’s well past the version in which the problem was “solved”.
 
  
 不幸的是，该文章已退休，因为该问题已在Excel 2000中解决。嗯，至少在我的机器上似乎又回来了。 我在使用Excel 2013的Windows 8计算机上，因此远远超出了“解决”问题的版本。 
 
  
 导致问题的条件 (The Conditions That Cause the Problem)
 
  
Fortunately, you can find all kinds of things in the WayBack Machine, and that’s where I found an archive of the missing article. You can see it at the end of this article.
 
  
 幸运的是，您可以在WayBack Machine中找到各种各样的东西，这就是我找到丢失文章的档案的地方。 您可以在本文末尾看到它。 
 
  
What conditions can create this error message, or in my case, an Excel crash?
 
  
 哪些条件可以产生此错误消息，或者在我的情况下会导致Excel崩溃？ 
 
  
The Input range of the control is linked to a second worksheet.
 控件的输入范围链接到第二个工作表。 
You zoom both worksheets to percentages other than 100 percent.
 您将两个工作表缩放到100％以外的其他百分比。 
The zoom percentage of the two sheets are not equal.
 两张纸的缩放百分比不相等。 
  
And that’s how my workbook was set up:
 
  
 这就是我的工作簿的设置方式： 
 
  
My combo box gets its list from a named range on another worksheet
 我的组合框从另一个工作表上的命名范围获取列表 
Both sheets were at 80% zoom
 两张纸都放大80％ 
I changed the combo box sheet to 70%, to show the difference in font size
 我将组合框工作表更改为70％，以显示字体大小的差异 
  
 解决方法 (The Workaround)
 
  
Here are three workarounds for the problem:
 
  
 这是此问题的三种解决方法： 
 
  
Change the zoom setting of either worksheet to 100 percent.
 将任一工作表的缩放设置更改为100％。 
Change the zoom setting of both sheets to the same percentage.
 将两个图纸的缩放设置更改为相同的百分比。 
Select an input range that is on the sheet with the list box, drop-down list box, or combo box.
 使用列表框，下拉列表框或组合框选择表单上的输入范围。 
  
I chose option 1, and set the zoom level for the sheet with the named ranges to 100%. I will add a big message on that sheet, warning people to leave the zoom alone.
 
  
 我选择了选项1，并将具有指定范围的图纸的缩放级别设置为100％。 我将在该工作表上添加一条大消息，警告人们不要放大。 
 
  
With that zoom setting, there were no more crashes. But, when I changed it to 90%, and tested the combo box, it crashed again. Don’t do that!
 
  
 有了该缩放设置，就不再发生崩溃。 但是，当我将其更改为90％并测试组合框时，它再次崩溃了。 不要那样做！ 
 
  
 组合框应如何工作 (How the Combo Box Should Work)
 
  
In this video, you can see how the combo box works, when it isn’t crashing. You can download the sample file, and get the setup instructions, on my Contextures website: Data Validation Combo Box using Named Ranges
 
  
 在此视频中，您可以看到组合框没有崩溃时的工作方式。 您可以在我的Contextures网站上下载示例文件，并获取设置说明： 使用命名范围的数据验证组合框 
 
  
演示地址
 
  
 缺少的MSKB文章 (The Missing MSKB Article)
 
  
Here is the retired MSKB article that I found in the Internet Archive: ________________
 
  
 这是我在Internet存档中找到的MSKB退休文章：________________ 
 
  
 缩放工作表上的控件“系统资源不足” ("Not Enough System Resources" with Controls on Zoomed Sheets)
 
  
Article ID: 183503 Retired KB Content Disclaimer This article was previously published under Q183503 SYMPTOMS If you click the following items in a worksheet multiple times or if you move a vertical toolbar over a worksheet with any of the following items
 
  
 文章ID：183503退休的KB内容免责声明如果您在工作表中多次单击以下各项，或者在具有以下任何一项的工作表上移动了垂直工具栏，则可能会出现以下问题： 
 
  
list box
 列表框 
drop-down list box (Microsoft Excel versions 5.0 or 7.0 only)
 下拉列表框(仅适用于Microsoft Excel 5.0或7.0版) 
combo box (Microsoft Excel 97 only)
 组合框(仅Microsoft Excel 97) 
  
the following message may appear: Not enough system resources to display completely CAUSE This problem may occur when the following conditions are true.
 
  
 可能会出现以下消息：没有足够的系统资源无法完整显示原因当满足以下条件时，可能会出现此问题。 
 
  
The Input range of the control is linked to a second worksheet.
 控件的输入范围链接到第二个工作表。 
 
 -and-
 -和- 
 
You zoom both worksheets to percentages other than 100 percent.
 您将两个工作表缩放到100％以外的其他百分比。 
 
 -and-
 -和- 
 
The zoom percentage of the two sheets are not equal.
 两张纸的缩放百分比不相等。 
 
 -and-
 -和- 
 
You move a toolbar over the control, click the control and choose values, or you alternately select each of the two worksheets.
 您将工具栏移到控件上，单击控件并选择值，或者交替选择两个工作表中的每一个。 
  
NOTE: Sometimes an action does not result in the message, but repeated actions do. The message only occurs when the worksheet that contains the control is active. WORKAROUND To work around this problem, do any of the following:
 
  
 注意：有时某个操作不会导致出现该消息，但是重复的操作却会导致该消息。 仅当包含该控件的工作表处于活动状态时，才会出现此消息。 要变通解决此问题，请执行以下任一操作： 
 
  
Change the zoom setting of either worksheet to 100 percent. Click Zoom on the View menu, click 100% and click OK.
 将任一工作表的缩放设置更改为100％。 单击视图菜单上的缩放，单击100％，然后单击确定。 
 
 -or-
 -要么- 
 
Change the zoom setting of both sheets to the same percentage. Click Zoom on the View menu to make this change.
 将两个图纸的缩放设置更改为相同的百分比。 在视图菜单上单击缩放以进行此更改。 
 
 -or-
 -要么- 
 
Select an input range that is on the sheet with the list box, drop-down list box, or combo box.
 使用列表框，下拉列表框或组合框选择表单上的输入范围。 
  
Changing the Input Range of a Control To change the input range of a control, follow these steps:
 
  
 更改控件的输入范围若要更改控件的输入范围，请按照下列步骤操作： 
 
  
Hold down the CONTROL key and click the form control to select the control.
 按住CONTROL键，然后单击窗体控件以选择该控件。 
On the Format menu, click Control.
 在格式菜单上，单击控件。 
Click the Control tab and type a range on the active worksheet.
 单击控制选项卡，然后在活动的工作表上键入一个范围。 
  
STATUS Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem no longer occurs in Microsoft Excel 2000.
 
  
 回到顶端状态Microsoft已经确认这是本文开头列出的Microsoft产品中的问题。 在Microsoft Excel 2000中不再发生此问题。 
 
 
 

  
翻译自: https://contexturesblog.com/archives/2013/07/02/excel-crashing-after-combo-box-click/
 
 
excel 中vb组合框