VBA " out of memory"

[Sr Juan]

Mijn vraag
Ik heb een VBA script geschreven, maar ik krijg nu "out of memory" meldingen elke keer als ik de userform afsluit. Hoe krijg ik dat weg

Relevante software en hardware die ik gebruik
VBA 7.1
Office 365


Wat ik al gevonden of geprobeerd heb

Dim iRow As Integer (stond als long)

code:

Option Explicit
Dim Rng As Range, fnd As Range
Dim Ctrl As Control
Dim iRow As Integer
Dim wsAE As Worksheet

In dit stuk code was resize gezet op (,50) Nu 22.

code:

Private Sub CMB_addnew_Click()
Set wsAE = Worksheets("INFO_PAGO")
If MsgBox("Correct entry?", vbYesNo + vbQuestion, "Check the data!") = vbNo Then Exit Sub
iRow = wsAE.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
wsAE.Cells(iRow, 1).Resize(, 22).Value = Array(C_INVENTARIO.Value, T_M2.Value, T_ARRENDADOR.Value, T_AREA.Value, T_SUELO.Value, _
C_MET.Value, T_PROP.Value, T_REF.Value, T_NOTA.Value, T_ABONO.Value, T_RAD.Value, _
T_NRAD.Value, T_NPAGO.Value, C_CONCEPTO.Value, T_CP.Value, T_TRANS.Value, T_CALLE.Value, T_COL.Value, _
T_CARGO.Value, T_PAGO.Value, NEWD.Value, ENDD.Value)
'Columns.AutoFit
MsgBox "The new entry has been saved.", vbInformation, "Done"
For Each Ctrl In Controls
    If TypeName(Ctrl) = "TextBox" Or TypeName(Ctrl) = "ComboBox" Then Ctrl.Value = ""
Next Ctrl
LB_01.ListIndex = -1
LB_01.TopIndex = 0
Call UserForm_Initialize
End Sub

hele VBA code

code:

Option Explicit
Dim Rng As Range, fnd As Range
Dim Ctrl As Control
Dim iRow As Integer
Dim wsAE As Worksheet

Private Sub C_INVENTARIO_Click()
T_AREA.Value = C_INVENTARIO.Column(1)
T_M2.Value = C_INVENTARIO.Column(3)
T_SUELO.Value = C_INVENTARIO.Column(4)
T_COL.Value = C_INVENTARIO.Column(9)
T_PROP.Value = C_INVENTARIO.Column(13)
T_ARRENDADOR.Value = C_INVENTARIO.Column(11)
T_CALLE.Value = C_INVENTARIO.Column(12)
T_CP.Value = C_INVENTARIO.Column(14)
NEWD.Value = C_INVENTARIO.Column(5)
ENDD.Value = C_INVENTARIO.Column(6)
DAYSEND.Value = C_INVENTARIO.Column(7)
T_EST.Value = C_INVENTARIO.Column(16)
End Sub

Private Sub CMB_addnew_Click()
Set wsAE = Worksheets("INFO_PAGO")
If MsgBox("Correct entry?", vbYesNo + vbQuestion, "Check the data!") = vbNo Then Exit Sub
iRow = wsAE.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
wsAE.Cells(iRow, 1).Resize(, 22).Value = Array(C_INVENTARIO.Value, T_M2.Value, T_ARRENDADOR.Value, T_AREA.Value, T_SUELO.Value, _
C_MET.Value, T_PROP.Value, T_REF.Value, T_NOTA.Value, T_ABONO.Value, T_RAD.Value, _
T_NRAD.Value, T_NPAGO.Value, C_CONCEPTO.Value, T_CP.Value, T_TRANS.Value, T_CALLE.Value, T_COL.Value, _
T_CARGO.Value, T_PAGO.Value, NEWD.Value, ENDD.Value)
'Columns.AutoFit
MsgBox "The new entry has been saved.", vbInformation, "Done"
For Each Ctrl In Controls
    If TypeName(Ctrl) = "TextBox" Or TypeName(Ctrl) = "ComboBox" Then Ctrl.Value = ""
Next Ctrl
LB_01.ListIndex = -1
LB_01.TopIndex = 0
Call UserForm_Initialize
End Sub

Private Sub CMB_change_Click()

Set wsAE = Worksheets("IPSA_DATA")
Set Rng = wsAE.Range("A2:A" & wsAE.Cells(Rows.Count, "A").End(xlUp).Row)

  MsgBox "First choose a item in the list!", vbCritical, "Attention!"
  Exit Sub
Set wsAE = Worksheets("INFO_PAGO")
Set Rng = wsAE.Range("A2:A" & wsAE.Cells(Rows.Count, "A").End(xlUp).Row)
Set fnd = Rng.Find(What:=T_id.Value, LookIn:=xlValues, Lookat:=xlWhole)
  If LB_01.ListIndex = -1 Then
  MsgBox "First choose a item in the list!", vbCritical, "Attention!"
  Exit Sub
  Else
    If T_id = vbNullString Then
        MsgBox "Customizing is not possible, no entries found", vbExclamation, "Attention!"
        Exit Sub
    ElseIf Not fnd Is Nothing Then
        Application.EnableEvents = False
     If MsgBox("Correct entry?", vbYesNo + vbQuestion, "Check the data!") = vbNo Then Exit Sub
       wsAE.Cells(fnd.Row, "A").Resize(, 22).Value = Array(C_INVENTARIO.Value, T_M2.Value, C_INVENTARIO.Value, T_AREA.Value, T_SUELO.Value, _
C_MET.Value, T_PROP.Value, T_REF.Value, , T_NOTA.Value, T_ABONO.Value, T_RAD.Value, _
T_NRAD.Value, T_NPAGO.Value, C_CONCEPTO.Value, T_CP.Value, T_TRANS.Value, T_CALLE.Value, T_COL.Value, _
T_CARGO.Value, T_PAGO.Value, T_CP.Value, NEWD.Value, ENDD.Value)
'Columns.AutoFit
MsgBox "The changes have been saved.", vbInformation, "Done"
      Application.EnableEvents = True
    End If
    For Each Ctrl In Controls
    If TypeName(Ctrl) = "TextBox" Or TypeName(Ctrl) = "ComboBox" Then Ctrl.Value = ""
Next Ctrl
LB_01.ListIndex = -1
LB_01.TopIndex = 0
    Call UserForm_Initialize
    End If
End Sub
Private Sub CMB_clear_Click()
For Each Ctrl In Controls
    If TypeName(Ctrl) = "TextBox" Or TypeName(Ctrl) = "ComboBox" Then Ctrl.Value = ""
Next Ctrl
LB_01.ListIndex = -1
LB_01.TopIndex = 0
Call UserForm_Initialize
End Sub
Private Sub CMB_close_Click()
Unload Me
End Sub


Private Sub LB_01_Click()
T_id.Value = LB_01.Column(0)
C_INVENTARIO.Value = LB_01.Column(1)
T_TRANS.Value = LB_01.Column(2)
T_ARRENDADOR = LB_01.Column(3)
T_AREA.Value = LB_01.Column(4)
T_SUELO.Value = LB_01.Column(5)
T_PROP.Value = LB_01.Column(6)
T_M2.Value = LB_01.Column(7)
T_CALLE.Value = LB_01.Column(8)
T_COL.Value = LB_01.Column(9)
T_CP.Value = LB_01.Column(10)
T_EST.Value = LB_01.Column(11)
NEWD.Value = LB_01.Column(12)
ENDD.Value = LB_01.Column(13)
C_CONCEPTO.Value = LB_01.Column(14)
T_CARGO.Value = LB_01.Column(15)
T_ABONO.Value = LB_01.Column(16)
C_MET.Value = LB_01.Column(17)
T_REF.Value = LB_01.Column(18)
T_PAGO.Value = LB_01.Column(19)
T_RAD.Value = LB_01.Column(20)
T_NRAD.Value = LB_01.Column(21)
T_NPAGO.Value = LB_01.Column(22)
T_NOTA.Value = LB_01.Column(23)

End Sub




Private Sub UserForm_Initialize()
LB_01.List = [database].Value

C_INVENTARIO.List = [datalist].Value
C_MET.List = [nouns].Value
C_CONCEPTO.List = [matgroup].Value
T_TRANS.Value = Now
End Sub

[Sr Juan]

Mildpower schreef op dinsdag 11 februari 2020 @ 00:07:

Private Sub LB_01_Click()
T_id.Value = LB_01.Column(0)
C_INVENTARIO.Value = LB_01.Column(1)
T_TRANS.Value = LB_01.Column(2)
T_ARRENDADOR = LB_01.Column(3)
T_AREA.Value = LB_01.Column(4)


[/code]

Ik denk dat ik het al gevonden heb, ik ben een .value vergeten in de T_ARRENDADOR!

Op dit moment geen meldingen meer