vba 在用户窗体上显示单元格范围；然后更新

Show Cell Range on UserForm; then update

提问by Chris Gunner

I've been using a crude method to help the user update some cells - by having them in a sheet. Is there any way I can display the various ranges in a userform, one by one, then have the user update them, click a button and move onto the next one?

我一直在使用一种粗略的方法来帮助用户更新某些单元格 - 通过将它们放在工作表中。有什么方法可以在用户表单中一个一个地显示各种范围，然后让用户更新它们，单击一个按钮并移动到下一个？

Essentially, can I have Excel automatically generate an input form based on a range? The process of updating and saving back to the sheet I can do; it's the production of the correct form that I can't.

本质上，我可以让 Excel 根据范围自动生成输入表单吗？更新和保存回工作表的过程我可以做；这是我做不到的正确形式的产生。

采纳答案by Jon Fournier

It's possible to do this, but the only way I can think of is to make a userform that automatically populates itself based on a range passed in. This way you could have different macros in Excel that call the form to populate based on different ranges. I built a proof of concept Excel file for trying this, and it seems to work, the only issue I can think of being that you need to figure out a way to tell the user what input field is what.

这样做是可能的，但我能想到的唯一方法是制作一个根据传入的范围自动填充自己的用户表单。这样，您可以在 Excel 中使用不同的宏来调用表单以根据不同的范围进行填充。我构建了一个概念证明 Excel 文件来尝试这个，它似乎有效，我能想到的唯一问题是你需要找出一种方法来告诉用户输入字段是什么。

I think what needs to be done is to add controls programmatically to a userform (I name the textboxes as the cell address it's going to populate) then when the form is closed loop through all the textboxes and populate the cells with the textbox values.

我认为需要做的是以编程方式向用户窗体添加控件（我将文本框命名为它要填充的单元格地址），然后当窗体通过所有文本框闭环并用文本框值填充单元格时。

You can see what I did at: https://my.syncplicity.com/share/uicgbs3rl0/InputForm.xls

你可以看到我做了什么：https: //my.syncplicity.com/share/uicgbs3rl0/InputForm.xls

I think all that would need to be done is for you to work out how to add labels for the textboxes, and make sure the form is resized based on the controls you add...

我认为您需要做的就是弄清楚如何为文本框添加标签，并确保根据您添加的控件调整表单的大小...

回答by sdfx

I am not quite shure what you are looking for, but you could insert a second sheet and use it as a "form". An other way could be a dialog box with an input field.

我不太确定您要找什么，但您可以插入第二张纸并将其用作“表格”。另一种方式可能是带有输入字段的对话框。

Either way, you present the cells you want the user to change one by one, using a vba-function. You implement a "previous field" and a "next field" button, so the user can step through the range of cells. If the user hits "next field", you save his input and take the next cell from a previous defined range of cells.

无论哪种方式，您都可以使用 vba 函数一一呈现您希望用户更改的单元格。您实现了“上一个字段”和“下一个字段”按钮，因此用户可以逐步浏览单元格范围。如果用户点击“下一个字段”，您将保存他的输入并从先前定义的单元格范围中获取下一个单元格。

You could have a "config field" in which you define the range of cells you want to change.

您可以有一个“配置字段”，您可以在其中定义要更改的单元格范围。

回答by barrowc

This is pretty rough and old-fashioned but if you have the data in standard list format - i.e. column headers in the first row of your range and then one record of data in each row below - then selecting a cell within the range and going Data > Formwill give you a crude input form with roughly the functionality you need.

这是相当粗糙和老式的，但如果你有标准列表格式的数据-你的范围的第一行，即列标题，然后一个下面每一行数据的记录-那么的范围内选择一个细胞中去Data > Form的意愿为您提供一个粗略的输入表单，其中包含您需要的大致功能。

You can also do this in VBA by calling the ShowDataFormmethod of the appropriate worksheet. Just select a cell within whichever range you need first. The macro will remain paused until the user closes the data form

您也可以通过调用ShowDataForm相应工作表的方法在 VBA 中执行此操作。只需先在您需要的范围内选择一个单元格。宏将保持暂停状态，直到用户关闭数据表单