2

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.

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

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

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

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 ?

Improve this question

2 Answers 2

Reset to default
2

How to work with a Data Array

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

  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).

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! ;)

Code to place in the UserForm to fill the data array :

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

Procedure to print in the database the data array that you pass from the user form :

Here the data base is the Named Range Db_Val in ingevenuitslagen sheet

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 to unfilter the DB you are working with :

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
Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

So I can now create a userform with the ability of as many rows as I want?
Can I send you my excel workbook so you can take a look at it??
Indeed you can have as many rows as you want with this method. But I tuned everything to fit the code you gave and explained what to change when I didn't know the name of something, so everything is done already. You just have to create a duplicate of your present UserForm and add the code that I gave and rename CommandButton1, CommandButton2 and the Named Range Db_Val to fit your need, so you should be able to handle it from there.
one little question though. Is it possible to go the other way around. So for example returning the last 15 rows in my userform?
It may be possible but then how would you display it? You'd need a new button or something to move through these rows in your UF. Give it a try and post a question if you're stuck, but with what you have here, you should be able to have good basis for that functionnality! ;)
|
0

Good day all.

I have this same challenge. Mine is to be able to place a Customer's Orders. With the code I have I can only place one product per order at a time for the customer. I want to be able to place multiple products per order for one customer at the same time in a Userform and it will update multiple rows. The code below can only update one row with one product in a row for one customer:

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim ws As Worksheet
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
    .Cells(lRow, 1).Value = Me.Data1.Value
    .Cells(lRow, 2).Value = Me.Data2.Value
    .Cells(lRow, 3).Value = Me.Data3.Value
    .Cells(lRow, 4).Value = Me.Data4.Value
    .Cells(lRow, 5).Value = Me.Data5.Value
    .Cells(lRow, 6).Value = Me.Data6.Value
    .Cells(lRow, 7).Value = Me.Data7.Value
    .Cells(lRow, 8).Value = Me.Data8.Value
    .Cells(lRow, 9).Value = Me.Data9.Value
    .Cells(lRow, 10).Value = Me.Data10.Value  
End With
End Sub

The above can only update One product per customer. A customer could place order for more than one product.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.