如何在Excel中的UserForm上基于组合框选择填充文本框？
假设您有一个包含“名称”和“职员编号”列的表，如下图所示。 现在，您需要创建一个嵌入有一个组合框的用户窗体，该组合框收集内部的所有名称，并且在从组合框选择名称时将填充一个文本框，该文本框将填充相应的职员编号。 本文中的方法可以帮助您轻松实现目标。
使用VBA根据用户窗体上的组合框选择填充文本框
使用VBA根据用户窗体上的组合框选择填充文本框
请执行以下操作，以根据用户窗体上的组合框选择为文本框填充相应的值。
1。 按 其他 + F11 键打开 Microsoft Visual Basic应用程序 窗口。
2.在 Microsoft Visual Basic应用程序 窗口中，单击 插页 > 用户表格。 看截图：
3.然后将组合框控件和文本框控件插入到创建的用户窗体中，如下图所示。
4.右键单击用户窗体的任何空白，然后单击 查看代码 从上下文菜单中。 看截图：
5.在打开的用户窗体（代码）窗口中，请复制并粘贴以下VBA代码以替换原始代码。
VBA代码：根据用户窗体上的组合框选择填充文本框
Dim xRg As Range
Updated by Extendoffice 2018/1/30
Private Sub UserForm_Initialize()
Set xRg = Worksheets("Sheet5").Range("A2:B8")
Me.ComboBox1.List = xRg.Columns(1).Value
End Sub
Private Sub ComboBox1_Change()
Me.TextBox1.Text = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, xRg, 2, False)
End Sub
Note: In the code, A2:B2 is the range contains the values you will populate separately in both the Userform combo box and text box. And Sheet5 is the name of the worksheet contains the data range. Please change them as you need.
6. Press the Alt + Q keys to exit the Microsoft Visual Basic for Applications window.
7. Click Developer > Insert > Command Button (ActiveX Control) to insert a command button into the worksheet.
8. Right-click the sheet tab and select View Code from the right-clicking menu. Then copy and paste VBA code into the code window.
VBA code: Show userform
Private Sub CommandButton1_Click()
Updated by Extendoffice 2018/1/30
UserForm1.Show
End Sub
9. Turn off the Design Mode in the workbook.
Click the command button to open the specified userform. Then you can see all na
mes are collected in the combo box. When selecting a name from the combo box, the corresponding staff number will be populated into the textbox automatically as below screenshot shown.
Related articles:
How to populate a combo box with specified data on Workbook open?
How to auto populate other cells when selecting values in Excel drop down list?
How to auto populate other cells when selecting values in Excel drop down list?
How to populate google search results to worksheet in Excel?
Best Office Productivity Tools
Transform Hours into Minutes with Kutools for Excel!
Ready to supercharge your Excel tasks? Harness the power of Kutools for Excel - your ultimate time-saving tool. Streamline intricate tasks and glide through your data like a pro. Experience Excel at lightning speed!
Why You Need Kutools for Excel
🛠️ Over 300 Powerful Features: Kutools is packed with more than 300 advanced features, simplifying your work in over 1500 scenarios.
📈 Superior Data Processing: Merge cells, remove duplicates, and perform advanced data conversions – all without breaking a sweat!
⏱️ Efficient Batch Operations: Why put in extra effort when you can work smart? Import, export, com
bine, and tweak data in bulk with ease.
📊 Customizable Charts and Reports: Access a broad variety of additional charts and generate insightful reports that tell a story.
🗄️ Powerful Navigation Pane: Gain an advantage with the robust Column Manager, Worksheet Manager, and Custom Favorites.
📝 Seven Types of Drop-down Lists: Make data entry a breeze with drop-down lists of various features and types.
🎓 User-Friendly: A breeze for beginners and a powerful tool for experts.
Download Now and Soar Through Time with Excel!
Read More... Free Download... Purchase...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Read More... Free Download... Purchase...
