Posts by Saggaf2012

Saggaf2012 · Apr 26th 2021

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.

Code

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

Display More