Date format excel

[xdciro]

Hello,

I have created a userform that will allow users to input multiple lines of data into a table based on a selection in a list box. The userform consists of some textboxes and a listbox (multiple selection). I have a problem with the textbox where i have to insert date. The problem is that even if i write the date in right format (in my case "dd.mm.yyyy") once the date is inserted into a table, excel doesnt recognize it as date - when I want to filter the table by date, the dates aren't grouped by month. I tried to use the Date format for these columns, but it doesnt work for the data that was inserted by the created userform. If i manually wrote the date in the same format (dd.mm.yyyy) into a cell in the same column of the table it was recognized as date. I'm new to VBA and I think the problem is that I have to define Date format inside textobox or commandbutton, etc.

Here is some relevant code from VBA (for info: txbDatumPrispetja is textbox for the Date it has to be inserted by user):
txb... textbox
lbx... listbox
cbb... combobox

Private Sub cmbVstavi_Click() 'this is CommandButton

Dim rng As Range
Dim I As Long

Set rng = Range("B" & Rows.Count).End(xlUp).End(xlUp).Offset(1)
    For I = 0 To lbxMetoda.ListCount - 1
        If lbxMetoda.Selected(I) = True Then
            rng.Resize(, 9).Value = Array(txbDatumPrispetja.Value, txbLabID.Value, txbSerijskaStevilka.Value, txbSifra.Value, txbDrugo.Value, txbRokIzvedbe.Value, cbbProjekt.Value, cbbVrstaVzorca.Value, lbxMetoda.List(I))
            Set rng = rng.Offset(1)
        End If
    Next I

txbLabID.Text = ""
txbSerijskaStevilka.Text = ""
txbSifra.Text = ""
txbDrugo.Text = ""
txbRokIzvedbe.Text = ""
cbbProjekt.Text = ""

End Sub

I would really appreciate your help. Thank you in advance

KR

 

[Fluff]

Try

rng.Resize(, 9).Value = Array(Format(txbDatumPrispetja.Value,"dd.mm.yyyy"), txbLabID.Value,

 

[xdciro]

Thank you for quick reply
I tried to write the code as suggested, but it doesnt work (error - overflow)

This is what I wrote:

Set rng = Range("B" & Rows.Count).End(xlUp).End(xlUp).Offset(1)
    For I = 0 To lbxMetoda.ListCount - 1
        If lbxMetoda.Selected(I) = True Then
             rng.Resize(, 9).Value = Array(Format(txbDatumPrispetja.Value, "dd.mm.yyyy"), txbLabID.Value, txbSerijskaStevilka.Value, txbSifra.Value, txbDrugo.Value, txbRokIzvedbe.Value, cbbProjekt.Value, cbbVrstaVzorca.Value, lbxMetoda.List(I))
            Set rng = rng.Offset(1)
        End If
    Next I

 

[Fluff]

What about

If lbxMetoda.Selected(i) = True Then
     Rng.Value = Format(txbDatumPrispetja.Value, "dd.mm.yyyy")
     Rng.Offset(, 1).Resize(, 8).Value = Array(txbLabID.Value, txbSerijskaStevilka.Value, txbSifra.Value, txbDrugo.Value, txbRokIzvedbe.Value, cbbProjekt.Value, cbbVrstaVzorca.Value, lbxMetoda.List(i))
    Set Rng = Rng.Offset(1)
End If

 

[xdciro]

It still doesnt work, return the same error. Debugger returns an error at

rng.Value = Format(txbDatumPrispetja.Value, "dd.mm.yyyy")

Private Sub cmbVstavi_Click()

Dim rng As Range
Dim I As Long

Set rng = Range("B" & Rows.Count).End(xlUp).End(xlUp).Offset(1)
    For I = 0 To lbxMetoda.ListCount - 1
        If lbxMetoda.Selected(I) = True Then
        rng.Value = Format(txbDatumPrispetja.Value, "dd.mm.yyyy")
        rng.Offset(, 1).Resize(, 8).Value = Array(txbLabID.Value, txbSerijskaStevilka.Value, txbSifra.Value, txbDrugo.Value, txbRokIzvedbe.Value, cbbProjekt.Value, cbbVrstaVzorca.Value, lbxMetoda.List(I))
        Set rng = rng.Offset(1)
        End If
    Next I

txbLabID.Text = ""
txbSerijskaStevilka.Text = ""
txbSifra.Text = ""
txbDrugo.Text = ""
txbRokIzvedbe.Text = ""
cbbProjekt.Text = ""

End Sub

 

[Fluff]

In that case I'm afraid I can't help any further.
I do not understand why you would get an overflow error.