I haven't used VBA before I just used it recently to create an Automated Entry Form in excel for a current project in my work following a tutorial on Youtube. However, whenever I run the code I get an error (Run-time error 381: Could not set the error property. Invalid property array index) which appears to be due the number of columns in my worksheet (16) and it is supposed to be less than 10 to work with the list function ( that is what I understood after googling the error code.).
While searching for a solution on internet I found a suggestion of using another method (arrays) rather than using lists. However, I couldn't understand how it works. I would appreciate it if any one can help me overcome this problem.
Attached is a copy of the excel file.
Option Explicit
Sub Reset()
Dim iRow As Long
iRow = [Counta(Database!A:A)] ' identifying the last row
With frmForm
.LetterNo.Value = ""
.LetterDate.Value = ""
.LetterAmount.Value = ""
.NameBenf.Value = ""
.AddressBenf.Value = ""
.Reference.Value = ""
.AcctBenf.Value = ""
.BankBenf.Value = ""
.BenfBankAdd.Value = ""
.SwiftBenf.Value = ""
.IBANBenf.Value = ""
.SwiftNotif.Value = False
.LetterCurrency.Clear
.LetterCurrency.AddItem "EURO"
.LetterCurrency.AddItem "USD"
.AccountHold.Clear
.AccountHold.AddItem "ZZZZZZZ"
.AccountHold.AddItem "CCCCCCC"
.AcctNo.Clear
.AcctNo.AddItem "0000 0000 1111 1111"
.AcctNo.AddItem "1111 1111 0000 0000"
.OurBankCode.Clear
.OurBankCode.AddItem "44444444"
.OurBankCode.AddItem "3333333"
.OurBankCode.AddItem "22222222"
.OurBankCode.AddItem "11111111"
.lstDatabase.Column = 16
.lstDatabase.ColumnHeads = True
If iRow > 1 Then
.lstDatabase.RowSource = "Database!A2:I" & iRow
Else
.lstDatabase.RowSource = "Database !A2:I2"
End If
End With
End Sub
Sub Submit()
Dim sh As Worksheet
Dim iRow As Long
Set sh = ThisWorkbook.Sheets("Database")
iRow = [Counta(Database!A:A] + 1
With sh
.Cells(iRow, 1) = frmForm.LetterNo.Value
.Cells(iRow, 2) = [Text(Now(), "DD/MM/YYY")]
.Cells(iRow, 3) = frmForm.LetterCurrency.Value
.Cells(iRow, 4) = frmForm.LetterAmount.Value
.Cells(iRow, 5) = frmForm.AccountHold.Value
.Cells(iRow, 6) = frmForm.AcctNo.Value
.Cells(iRow, 7) = frmForm.OurBankCode.Value
.Cells(iRow, 8) = frmForm.NameBenf.Value
.Cells(iRow, 9) = frmForm.AddressBenf.Value
.Cells(iRow, 10) = frmForm.AcctBenf.Value
.Cells(iRow, 11) = frmForm.BankBenf.Value
.Cells(iRow, 12) = frmForm.BenfBankAdd.Value
.Cells(iRow, 13) = frmForm.SwiftBenf.Value
.Cells(iRow, 14) = frmForm.IBANBenf.Value
.Cells(iRow, 15) = frmForm.Reference.Value
.Cells(iRow, 16) = IIf(frmForm.SwiftNotif.Value = True, "YES", "")
End With
End Sub
Sub Show_Form()
frmForm.Show
End Sub