如何一次将多个数据行从UserForm添加到Excel数据库 [英] How to add multiple data rows at once from UserForm to Excel DataBase

问题描述

我正在建立某种足球数据库，在该数据库中，我将使用用户窗体输入数据，并从Excel数据库中检索数据.

I'm making some sort of football database where I would input data using a userform and where I want to retrieve data from my excel database.

我有一个名为"wedstrijden"的工作表，该工作表包含以下几列:日期，主队，离队，主得分，主得分，主赔率和主赔率

I have a worksheet named: "wedstrijden" This worksheet contain the columns: Date, HomeTeam, AwayTeam, HomeScore,AwayScore, HomeOdds and AwayOdds

我的另一个工作表被命名为:"ingevenuitslagen"该工作表包含我的名为UitslagenIngeven的用户表单

My other worksheet is named: "ingevenuitslagen" This worksheet contains my userform called UitslagenIngeven

使用下面的代码，我可以将用户窗体中的数据输入到我的"wedstrijden"工作表中

Using the code below I'm able to input my data from the userform to my "wedstrijden" worksheet

Private Sub putAway_Click()
Dim ingevenuitslagen As Worksheet
Set ingevenuitslagen = ThisWorkbook.Sheets("wedstrijden")
NextRow = ingevenuitslagen.Cells(Rows.Count, 1).End(xlUp).Row + 1
ingevenuitslagen.Cells(NextRow, 1) = CDate(date_txt.Text)
ingevenuitslagen.Cells(NextRow, 2) = UitslagenIngeven.cboHomeTeam
ingevenuitslagen.Cells(NextRow, 3) = UitslagenIngeven.cboAwayTeam
ingevenuitslagen.Cells(NextRow, 4) = UitslagenIngeven.cboHScore
ingevenuitslagen.Cells(NextRow, 5) = UitslagenIngeven.cboAScore
ingevenuitslagen.Cells(NextRow, 6) = Val(UitslagenIngeven.hodds_txt.Text)
ingevenuitslagen.Cells(NextRow, 7) = Val(UitslagenIngeven.aodds_txt.Text)
End Sub

但这只是收起1行.我希望可以一​​次收起10或15行.因此，我将使一个用户表单有可能丢弃20行，但它应该只能丢弃那些已填写的行.

But this is only to put away 1 row. I would like to make the possibility to put away 10 or 15 rows at once. So I would make a userform with the possibility to put away 20 rows BUT it should be able to put away only those rows that are filled in.

这可能吗?以及我应该如何调整我的用户形式?我可以复制文本和组合框区域吗?

Is this possible? And how should I adjust my userform? Can I just copy the text and combobox areas ?

推荐答案

如何使用数据数组

您需要创建一个新按钮，您将拥有:

How to work with a Data Array

You'll need to create a new button, you'll have :

1. 一种用于将数据集添加到数据数组(此处为 CommandButton1 )和
2. 一个将数据数组添加到数据库中(此处为 CommandButton2 ).

1. one for adding the data set to the data array (here CommandButton1) and
2. one to add the data array to the data base (here CommandButton2).

我也更喜欢使用数据库的命名范围，在这里它称为 Db_Val ，但是您可以根据需要重命名它！;)

I also prefer to work with a Named Range for the Data Base, here it is called Db_Val but you can rename this to fit your needs! ;)

Public ingevenuitslagen As Worksheet
Public DataA() '----These lines should be at the top of the module

'----Code to Set the dimension of the Data array
Private Sub UserForm_Initialize()
    Dim DataA(7, 0)
    Set ingevenuitslagen = ThisWorkbook.Sheets("wedstrijden")
    '----Rest of your code
End Sub

'----Code to add a data set to the data array
Private Sub CommandButton1_Click()
    UnFilter_DB '----See below procedure

    DataA(1) = CDate(date_txt.Text)
    DataA(2) = UitslagenIngeven.cboHomeTeam
    DataA(3) = UitslagenIngeven.cboAwayTeam
    DataA(4) = UitslagenIngeven.cboHScore
    DataA(5) = UitslagenIngeven.cboAScore
    DataA(6) = Val(UitslagenIngeven.hodds_txt.Text)
    DataA(7) = Val(UitslagenIngeven.aodds_txt.Text)

    ReDim Preserve DataA(LBound(DataA, 1) To UBound(DataA, 1), LBound(DataA, 2) To UBound(DataA, 2) + 1)
End Sub

'----Code to sent the data array to the DB
Private Sub CommandButton2_Click()
    ReDim Preserve DataA(LBound(DataA, 1) To UBound(DataA, 1), LBound(DataA, 2) To UBound(DataA, 2) - 1)

    SetData DataA
End Sub

在数据库中打印从用户表单传递的数据数组的过程:

这里的数据库是 ingevenuitslagen 表中的命名范围 Db_Val

Public Sub SetData(ByVal Data_Array As Variant)
Dim DestRg As Range, _
    A()
'----Find the last row of your DataBase
Set DestRg = ingevenuitslagen.Range("Db_Val").Cells(ingevenuitslagen.Range("Db_Val").Rows.Count, 1)
'----Print your array starting on the next row
DestRg.Offset(1, 0).Resize(UBound(Data_Array, 1), UBound(Data_Array, 2)).Value = Data_Array
End Sub

Sub以取消过滤正在使用的数据库:

Public Sub UnFilter_DB()
'----Use before "print" array in sheet to unfilter DB to avoid problems (always writing on the same row if it is still filtered)
Dim ActiveS As String, CurrScreenUpdate As Boolean
CurrScreenUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False
ActiveS = ActiveSheet.Name
    ingevenuitslagen.Activate
    ingevenuitslagen.Range("A1").Activate
    ingevenuitslagen.ShowAllData
    DoEvents
    Sheets(ActiveS).Activate
Application.ScreenUpdating = CurrScreenUpdate
End Sub

这篇关于如何一次将多个数据行从UserForm添加到Excel数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！