使用输入框在Excel工作表上收集数据 [英] Collect Data On Excel Sheet Using Input Box

问题描述

我有一个用户表单，上面有许多按钮。每个按钮代表一种蔬菜（例如，黄瓜，胡萝卜，番茄）


这样做的目的是接受顾客订购他们想要订购的蔬菜和金额。


当点击每个按钮（例如，Carrot）时，宏会在excel表上放置Carrot字样。之后会出现一个输入框，供用户输入所需的金额。


我遇到的问题是，我想整理这些数据，但我无法设置单元格来放置单词''胡萝卜'，因为它可能不是用户订购的第一件事。


我需要一个代码，按照用户订购的任何顺序整理这些数据。


这是我目前按钮的代码use（但它只会在覆盖之前运行到两行）：

展开 | 选择 | Wrap < span class =codeDivider> | 行号

解决方案

< blockquote>亲爱的，


是否可以附上XLS文件？

所以我们可以谈论相同的事情。


br，

好吧，代码似乎很好，唯一的问题是每次te按钮是点击你得到a = 6，所以你总是写在同一行。


从第6行开始，你可以通过多种方式找到第一个空单元格。第一个来到我的minde是使用RANGE（）。END（-4124），注意-4121是常量xlDown的值（你不必拥有）。


但更直观的方式可能是DO / LOOP：

展开 | 选择 | Wrap | 行号

嗨GGeu& Kadghar，


很多人感谢你的帮助!!我非常感谢


我的VB版本是Microsoft Visual Basic 6.3。我不确定这是不是你的意思。对不起


另外，附件是我使用的文件。这应该是在接受蔬菜订单时使用的交互式菜单。基本上我需要根据用户点击菜单的内容整理订单。


我设法让它工作但是使用i = 1然后循环然后i = i + 1.


但现在我需要胡萝卜和用户键入的值（amount / foodAmt）要链接。因此，无论用户输入什么，它都将放在名为carrot的单元格旁边。因此，例如，如果''Carrot''在Cell（A1）中，那么胡萝卜的价值或数量必须在Cell（B1）中


这有意义吗？抱歉这么模糊。因为我已经尝试过菜单，如果用户碰巧输入空值，菜单上的下一个项目会向上移动，从而导致结果不准确。


此外，我希望添加更多功能，例如，如果添加的值（foodAmt）是一位数，那么输出将以千克（kg）为单位[foodAmt& kg]


如果该值包含3位数或更多，则以克（g）为单位[foodAmt& g]


让我知道你的想法和意见，因为我是该地区的一个小新人，我再次感谢所有的帮助！


干杯，

妮可（：

附加文件

< td> 测试Menu.zip （22.9 KB，108 views）

I have a user form which has a number of buttons on it. Each button represents a vegetable (Eg, Cucumber, Carrot, Tomato)

The purpose of this is to take orders from customers for the vegetable they would like to order and the amount.

When each button is clicked (eg, Carrot) the macro would place the word ''Carrot'' on the excel sheet. After which an input box will appear for the user to input the amount needed.

The problem i have is, i want to collate this data but i cannot set the cell to place the word ''Carrot'' as it might not be the first thing the user orders.

I need a code that collates these data in any order that the user orders.

This is the code for a button that i currently use (But it only runs to two line before it overwrites itself) :

Expand|Select|Wrap|Line Numbers

解决方案

dear,

Is it possible to attach the XLS file?
so we can talk about the same things.

br,

well, the code seems fine, the only problem is that each time te button is clicked you get a = 6, so you''ll be always writing in the same row.

Starting from the 6th row, you can find the first empty cell by many ways. The first one that comes to my minde is using RANGE().END(-4124), Note -4121 is the value for the constant xlDown (that you dont necesarily have).

But a bit more intuitive way could be with a DO/LOOP :

Expand|Select|Wrap|Line Numbers

Hi GGeu & Kadghar,

Many many thank you for your help!! I really appreciate it

The Version of my VB is Microsoft Visual Basic 6.3. I am not really sure if this is what you mean. Sorry

Also, attached is the file that i use. This is supposed to be an interactive menu to use when taking vegetable orders. Basically i need to collate the orders based on what the user clicks on the menu.

I managed to get it working but using i = 1 and then loop then i = i + 1.

But now i need the Carrot and the value(amount / foodAmt) that the user types in to be linked. So that whatever the user types, it will be placed beside the cell named carrot. So for example, if ''Carrot'' is in Cell (A1) then the value or the amount of carrot must be in Cell (B1)

Does this make sense? Sorry to be so vague. Because i''ve tried the menu and if the user so happens to enter an empty value, the next item on the menu will move up and thus resulting with inaccurate results.

Additionally, i wish to add more functions like if the value (foodAmt) added is a single digit then the output would be in kilograms (kg) [foodAmt & "kg"]

and if the value contains 3 digits or more it would be in grams (g) [foodAmt & "g"]

Let me know your thoughts and opinions as i am a little new in the area and i appreciate all the help once again!

Cheers,
Nicole(:

Attached Files

Testing Menu.zip (22.9 KB, 108 views)

这篇关于使用输入框在Excel工作表上收集数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！