用户表格 [英] Userform

问题描述

我正在使用以下示例创建用于在电子表格中添加和编辑交易的用户表单.添加部分工作正常，但是我在编辑和保存时遇到问题.还有我用于保存和取消按钮的代码.请帮忙.我是VB新手

第20章:创建高级用户表单

内容

捕获信息

有时在工作表中输入信息可能会很痛苦.很难确保数据有效且格式正确.确保将数据添加到正确的位置也可能很困难.在此示例中，您将学习如何构造一个简单的表单来接受用户的数据并将其存储在工作表中.

表单应用程序概述

此示例应用程序中使用的Excel工作表仅记录了有关The Garden Company的客户的六项信息:CustomerId，姓名，城市，州，邮政编码和DateAdded. (见图20-1.)

此表单是通过运行宏启动的，一直运行到用户显式关闭该表单为止.表单上的按钮控制通过表单显示工作表的哪一行，并且表单本身允许用户输入或编辑存储在特定行中的任何数据.

图20-1.一个简单的工作表会跟踪客户信息，例如姓名和地址以及添加客户的日期.

设计表单

对于大多数数据驱动的表单，应将字段放在单个字段中，并在每个字段旁边添加标签.这种布置使用户容易找到表格上的信息.但是，对于某些字段(例如，属于地址的字段)，您可能会发现更直观地排列这些字段会更好，例如将城市"，州"和邮政编码"字段放在同一行上.

重要的是要注意，字段在表单上的放置完全独立于访问它的代码.如果您希望将所有字段排列成一个圆圈，则对您的代码没有什么影响.尽管这一事实看似显而易见，但正是革命性的概念引发了Microsoft Visual Basic的革命.

提示:使用有效的工具在设计用户表单时，请查看Excel和其他应用程序中内置的各种窗口和对话框以获取设计思想. /blockquote>

请按照以下步骤创建将与用户交互的表单:

1. 启动Visual Basic编辑器，然后从主菜单中选择插入"，用户窗体".这将在您的应用程序中创建一个新的 UserForm 对象.
2. 从工具箱中，为工作表中的每一列拖动一个 TextBox 控件和一个 Label 控件.拖动 ComboBox 控件以保存状态列表.您可能必须调整用户表单的大小以容纳控件.

   提示:双击以节省时间如果您希望将同一控件的多个副本添加到用户表单，请在工具箱中双击该控件.鼠标指针将更改以反映所选控件.然后，您可以在用户窗体上绘制多个控件.添加完该特定控件后，可以双击工具箱中的另一个控件以添加该控件的多个副本，或者单击工具箱中的箭头以将鼠标指针恢复为正常状态.

3. 使用属性"窗口更改每个文本框的 Name 属性，以反映数据库字段( CustomerId ， CustomerName ，城市，邮政编码和 DateAdded ).将组合框控件的 Name 属性更改为 State .还可以将每个 Label 控件的 Caption 属性更改为更具描述性的内容. (见图20-2.)

   图20-2.按照指示为工作表中的每一列及其属性添加TextBox和Label控件.

4. 将 CommandButton 控件添加到用户表单，该表单将允许用户滚动行.将标题属性更改为读取第一，上一个，下一个和最后.在上一个和下一个控件之间留一个空格.

   提示:控件具有移动自由一旦控件位于窗体上，您就可以选择它并将其移动到所需窗体上的任何位置.您也可以通过单击表单并拖动以选择要移动的控件组来对多个控件执行相同的操作.然后，您可以在窗体上拖动选定的组.

5. 在上一个和下一个控件之间添加一个 TextBox 控件.将 Name 属性更改为 RowNumber .将 Text 属性设置为 2 .
6. 再添加三个 CommandButton 控件.将第一个的 Caption 属性更改为保存，将第二个属性的 Caption 属性更改为取消，将最后一个属性更改为添加 >.
7. 将保存和取消 CommandButton 控件上的 Enabled 属性更改为 False .
8. 添加完所有控件后，您可以调整它们的大小和在表单上的确切位置，直到找到令人愉悦的布置. (请参见图20-3.)

   图20-3.完成表单布局.

显示数据

构建表单后，是时候将数据从工作表复制到表单了. RowNumber 文本框包含应在表单上显示的行号，所以真正的技巧是将 RowNumber 文本框中的值转换为一个可以使用 Cells 方法从工作表中提取数据.

以下程序清单显示了 GetData 例程，该例程位于与用户表单关联的模块中. GetData 将数据从当前活动的工作表复制到用户表单.在声明一个临时变量 r 来保存当前行后，该例程将验证 RowNumber 控件中的值是数字.此步骤很重要，因为用户可以在此字段中键入任何值.

解决方案

要了解您的问题所在，有必要查看代码！

哪里出错了?

 

Leif

I am using the example below to Create a userform for adding and editing transactions in a spreadsheet. The adding part works fine but I'm having a problem editing and saving. Also what code i am using for the save and cancel buttons. please help.  I am new to VB

 

 

Chapter 20: Creating Advanced User Forms

Contents

Capturing Information
Building a Multi-Step Wizard

User forms are a critical part of Microsoft Excel programming in that they provide a surface that is totally under your control with which you can interact with a user. This makes it possible to build more complex Excel applications. Forms can also be used to collect and verify information from a user before it's entered into a worksheet. They can also be used as part of an add-in to display options and control execution of a particular task. In this chapter, you'll learn how to build a user form that allows a user to input data into the worksheet, plus how to build an add-in that invokes a multi-step wizard that creates a chart based on selections made by a user.

Capturing Information

Entering information into a worksheet can be painful sometimes. It's difficult to ensure that the data is valid and is properly formatted. It can also be difficult to ensure that the data is added at the proper location. In this example, you'll learn how to construct a simple form that accepts data from the user and stores it in a worksheet.

Form Application Overview

The Excel worksheet used in this sample application merely records six pieces of information about a customer at The Garden Company: CustomerId, Name, City, State, ZipCode, and DateAdded. (See Figure 20-1.)

This form is started by running a macro and remains up until the user explicitly closes the form. Buttons on the form control which row of the worksheet is displayed through the form, and the form itself allows the user to enter or edit any data stored in a particular row.

Figure 20-1. A simple worksheet tracks customer information such as name and address and the date the customer was added.

Designing a Form

For most data-driven forms, you should place the fields in a single column with labels next to each field. This arrangement makes it easy for the user to find information on the form. However, with some fields, such as those that are part of an address, you might find it better to arrange the fields more intuitively, such as placing the City, State, and ZipCode fields on the same line.

It's important to note that the placement of the fields on the form is completely independent of the code that accesses it. If you wished to arrange all of the fields in a circle, it wouldn't make a bit of difference to your code. While this fact is something that might seem obvious, it was the revolutionary concept that started the Microsoft Visual Basic revolution.

Tip: Work with What Works   When designing user forms, take a look at the various windows and dialog boxes built into Excel and other applications for design ideas.

Follow these steps to create a form that will interact with the user:

1. Start the Visual Basic Editor, and choose Insert, UserForm from the main menu. This will create a new UserForm object in your application.
2. From the Toolbox, drag a TextBox control and a Label control for each column in the worksheet. Drag a ComboBox control to hold the list of states. You might have to adjust the size of the user form to accommodate the controls.

   Tip: Double-Click to Save Time   If you wish to add multiple copies of the same control to a user form, double-click the control in the toolbox. The mouse pointer will change to reflect the selected control. You may then draw multiple controls on the user form. When you're finished adding that particular control, you may double-click another control in the toolbox to add multiple copies of that control or click the arrow in the toolbox to return the mouse pointer to normal.

3. Use the Properties window to change the Name property of each text box to reflect the database fields (CustomerId, CustomerName, City, ZipCode, and DateAdded). Change the Name property of the combo box control to State. Also change the Caption property for each Label control to something more descriptive. (See Figure 20-2.)

   Figure 20-2. Add TextBox and Label controls for each column in the worksheet and their properties as directed.

4. Add CommandButton controls to the user form that will allow the user to scroll through the rows. Change the Caption property to read First, Previous, Next, and Last. Leave a space between the Previous and Next controls.

   Tip: Controls Have Freedom of Movement   Once a control is on the form, you can select it and move it anywhere on the form you wish. You can also do the same thing with multiple controls by clicking the form and dragging to select the group of controls you want to move. Then you can drag the selected group around on the form.

5. Add a TextBox control in between the Previous and Next controls. Change the Name property to RowNumber. Set the Text property to 2.
6. Add three more CommandButton controls. Change the Caption property of the first one to Save, the second one to Cancel, and the last one to Add.
7. Change the Enabled property on the Save and Cancel CommandButton controls to False.
8. Once all of the controls have been added, you can tweak their sizes and exact placement on the form until you find a pleasing arrangement. (See Figure 20-3.)

   Figure 20-3. Finishing the form layout.

Displaying Data

With the form constructed, it's time to copy data from the worksheet to the form. The RowNumber text box contains the number of the row that should be displayed on the form, so the real trick is to convert the value in the RowNumber text box into a value that can be used to extract the data from the worksheet using the Cells method.

The following program listing shows the GetData routine, which is located in the module associated with the user form. GetData copies the data from the currently active worksheet to the user form. After declaring a temporary variable r to hold the current row, the routine verifies that the value in the RowNumber control is numeric. This step is important because the user could type any value into this field.

解决方案

To understand where your problem lies, it is necessary to see your code!

Where does it go wrong?

 

Leif

这篇关于用户表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！