userform 检索_在Excel UserForm中添加新的ComboBox项

userform 检索

If you want to enter data in an Excel worksheet, while keeping the data sheet hidden, you can create an Excel UserForm.

如果要在Excel工作表中输入数据，而又不隐藏数据表，则可以创建Excel UserForm 。

I've updated my sample file, so you can now add new parts, while entering data. It's almost working the way it should, but I'm stuck on one step, so if you have a solution, please let me know!

我已经更新了示例文件，因此现在可以在输入数据的同时添加新零件。 它几乎可以按应有的方式工作，但是我只能坚持一步，因此，如果您有解决方案，请告诉我！

[Update: Problem solved with a workaround -- see below.]

[更新：解决方法已解决问题-参见下文。]

从组合框下拉列表中选择一个零件 (Select a Part from the ComboBox Drop Down List)

In the sample file, you can click the Add Parts Information button, to open the UserForm.

在示例文件中，您可以单击添加零件信息按钮，以打开用户窗体。

Then, select a Part ID from the combo box drop down list.

然后，从组合框下拉列表中选择一个部件ID。

零件清单 (The Parts List)

On another sheet in the workbook, there are two lists – Location, and Parts. These are dynamic named ranges, and will expand automatically, as new items are added to the lists.

在工作簿的另一张纸上，有两个列表–位置和零件。 这些是动态的命名范围 ，并且会随着新项目的添加而自动扩展。

将新零件添加到列表 (Add a New Part to the List)

In the latest version of the sample file, you can add new parts to the list, while you are entering data in the UserForm.

在最新版本的示例文件中，您可以在用户窗体中输入数据时将新零件添加到列表中。

1. If the Part ID that you want is not in the list, type it into the Part ID combo box.
   如果所需的部件ID不在列表中，请在“部件ID”组合框中键入它。
2. When you press the Tab key, to move to the next control, a Part Description text box will appear.
   当您按Tab键时，要移至下一个控件，将显示“零件描述”文本框。
3. Enter the description, then fill in the rest of the data.
   输入描述，然后填写其余数据。
4. Click the Add This Part button
   单击添加此零件按钮

选择新零件 (Select the New Part)

After you click the Add This Part button, the new item is added to the Parts List, and the Parts list is sorted A-Z.

单击“添加此零件”按钮后，新项目将添加到“零件列表”，并且“零件列表”按AZ排序。

The new item now appears in the Part ID combo box drop down list.

现在，新项目将出现在“零件ID”组合框下拉列表中。

SetFocus问题 (SetFocus Problem)

My goal was to have the Part Description activated, as soon as it was made visible. However, the VBA code wouldn't cooperate, so I've commented out this line:

我的目标是在零件说明一显示出来就将其激活。 但是，VBA代码无法配合，因此我注释掉了这一行：

Me.txtPartDesc.SetFocus

If you have a solution for getting that line to work, please share it in the comments, or send me an email. I'd appreciate it!

如果您有解决该问题的解决方案，请在评论中分享它，或者给我发送电子邮件。 我将不胜感激！

Update: Thanks to JeanMarc, Jon and Dave, the tab order is working now.You can see their suggestions in the comments below.

更新：感谢JeanMarc，Jon和Dave，制表顺序现在可以使用了，您可以在下面的评论中看到他们的建议。

• Instead of being hidden, the Parts Description textbox moves to the far right, so it's not in the visible part of the form, then moves back when needed.
  “零件描述”文本框不会被隐藏，而是移到最右边，因此它不在窗体的可见部分，然后在需要时移回。
• To keep the tab key from stopping on the "off form" textbox, its position is checked. If it's at the far right, go to the next control.
  为了防止Tab键停止在“关闭表单”文本框中，请检查其位置。 如果在最右边，请转到下一个控件。

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

To see the sample file, and check the Excel VBA code, you can download the file from my Contextures website. On the Sample Excel Files page, in the UserForm section, look for UF0017 - Parts Database with Updateable Comboboxes

若要查看示例文件，并检查Excel VBA代码，可以从我的Contextures网站下载该文件。 在“示例Excel文件”页上的“用户窗体”部分中，查找UF0017-具有可更新组合框的零件数据库

The file is available in Excel 2007/2010 or Excel 2003 format, and zipped. It contains macros, so enable those if you want to test the UserForm. _____________________

该文件具有Excel 2007/2010或Excel 2003格式，并已压缩。 它包含宏，因此如果要测试用户窗体，请启用这些宏。 _____________________

翻译自: https://contexturesblog.com/archives/2012/05/08/add-new-combobox-items-in-excel-userform/

userform 检索