How to call a sub from User form

[Guinaba]

Hi all,

I am trying to call a sub (IBPData_1), code below, from a user form, but keep getting the Run-time Error "1004". Any suggestion how to avoid this error?

UserForm:

Private Sub CommandButton6_Click()
  Call IBPData_1
End Sub

IBPData_1:

Sub Data_Array_Set_IBPData_1(vDtaHdr() As Variant, vDtaBdy() As Variant)

   Dim wrksht As Worksheet
   Dim objListObj As ListObject
   Dim vArray As Variant
 
  'Find the last non-blank cell in column A(1)
   LRow = ThisWorkbook.Worksheets("IBPData1").Cells(Rows.Count, 2).End(xlUp).Row
  
   With ThisWorkbook.Worksheets("IBPData1").Range(Cells(2, 2), Cells(LRow, 6))
        vArray = .Rows(1)
        vDtaHdr = vArray
        vArray = .Offset(1, 0).Resize(-1 + .Rows.Count)
        vDtaBdy = vArray
  End With
End Sub
  
Sub IBPData_1()

    Dim MyTable As ListObject
    Dim vDtaHdr() As Variant, vDtaBdy() As Variant
    Dim lRowsAdj As Long
        
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    Set MyTable = ThisWorkbook.Worksheets("IBPData1").ListObjects("tFcst_1") 'Change as required

    Call Data_Array_Set_IBPData_1(vDtaHdr, vDtaBdy)

    With MyTable.DataBodyRange
        Rem Get Number of Rows to Adjust
        lRowsAdj = 1 + UBound(vDtaBdy, 1) - LBound(vDtaBdy, 1) - .Rows.Count

        Rem Resize ListObject
        If lRowsAdj < 0 Then
            Rem Delete Rows
            .Rows(1).Resize(Abs(lRowsAdj)).Delete xlShiftUp

        ElseIf lRowsAdj > 0 Then
            Rem Insert Rows
            .Rows(1).Resize(lRowsAdj).Insert Shift:=xlDown

    End If: End With

    Rem Overwrite Table with New Data
    MyTable.HeaderRowRange.Value = vDtaHdr
    MyTable.DataBodyRange.Value = vDtaBdy
      
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
   
End Sub

 

[Joe4]

Does it give you a "Debug" button option when you get that error?
If so, and you click it, what line of code does it highlight?
That is where you want to first focus your attention.

Otherwise, I would recommend putting a breakpoint on the

  Call IBPData_1

line, and then stepping through your code one line at a time, and seeing exactly where things break.

 

[Guinaba]

Hi @Joe4,

Sorry for the late answer, the code is stopping at this line, just wondering if I am missing any object in the hierarchy?

 

[RoryA]

That code should be:

With ThisWorkbook.Worksheets("IBPData1").Range(ThisWorkbook.Worksheets("IBPData1").Cells(2, 2), ThisWorkbook.Worksheets("IBPData1").Cells(LRow, 6))

You can tidy that up by using a Worksheet variable.

 

[Guinaba]

That code should be:

With ThisWorkbook.Worksheets("IBPData1").Range(ThisWorkbook.Worksheets("IBPData1").Cells(2, 2), ThisWorkbook.Worksheets("IBPData1").Cells(LRow, 6))

You can tidy that up by using a Worksheet variable.

Thanks @RoryA! Spot on!