如何将用户表单数据分配给具有动态名称的Excel单元格 [英] How to assign userform data in to excel cells with dynamic names

问题描述

大家好，



我有一个包含10个texbox的用户表单。想要将所有文本框数据保存在工作表中，每个文本框数据都包含单独的Excel表格行。



我用循环输入数据但是我遇到了问题代码就像无法将动态控件名分配给单元格...



控件名称类似于txtName1，txtName2 ..... txtName10



查看代码...



Dim i As Integer

Dim iRow As Long

Dim ws As Worksheet

Set ws = Worksheets（" Sales"）

for i = 1 To 10

iRow = ws .Cells（Rows.Count，1）.End（xlUp）.Offset（1,0）.Row

ws.Cells（iRow，1）= txtName + i + .Text - 这是问题

Hi All,

I have a userform containing 10 texboxes. Want to save all the text boxes data in sheet with seperate rows of Excelsheet for each textbox data.

I have used for loop to enter data but I am getting problem in code like not able to assign dynamic controlname to cell...

control name are like txtName1, txtName2..... txtName10

see code...

Dim i As Integer
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sales")
For i = 1 To 10
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ws.Cells(iRow, 1) = txtName+i+.Text -- Here is the problem

推荐答案

如你在代码中提到的，你不能像txtName +那样写i + .Text。



但这是可能的方法之一，创建一个包含你想要的文本框名称的数组......



Dim Con trolNames（1到8）As Variant

Hi,

As you mentioned in code, you can't write like txtName + i + .Text.

But this is one of the possible way, Create an array with the names of text boxes you want...

Dim ControlNames(1 To 8) As Variant

ControlNames(1) = "TextBox1"
ControlNames(2) = "TextBox2"
ControlNames(3) = "TextBox3"
ControlNames(4) = "TextBox4"
ControlNames(5) = "TextBox5"
ControlNames(6) = "TextBox6"
ControlNames(7) = "TextBox7"
ControlNames(8) = "TextBox8"

之后，您可以访问如下所示，

after this, you can access as shown below,

 For i = 1 To 8  '10
    sht.Cells(i, 1).Value = UserForm1.Controls(ControlNames(i)).Text
Next i

希望这个对你有帮助。

Hope this is helpful to you.

这篇关于如何将用户表单数据分配给具有动态名称的Excel单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！