Unable to Call Subroutine

Question

I am unable to call sub BCD if user input valid week number in userform. If user input invalid or empty week number, sub BCD will execute.

What wrong with my code?

Private Sub cmdOK1_Click()

Call ABC
Call BCD

End Sub

Sub ABC()

'Prompt message box when week number is empty
    If Me.txtWeekNo.Value = "" Then
        MsgBox "Please enter week number to print.", vbExclamation, "Print Work Order by Schedule"
        Me.txtWeekNo.SetFocus
        Exit Sub
    End If

'Check for valid week number
LColumn = Worksheets("RIG PMS").Cells(5, Worksheets("RIG PMS").Columns.Count).End(xlToLeft).Column 'Define last column in week number
Set WeekNoRng = Worksheets("RIG PMS").Range(Cells(5, 10), Cells(5, LColumn))
    If WorksheetFunction.CountIf(WeekNoRng, txtWeekNo.Value) = 0 Then
         MsgBox "Invalid Week Number"
         Exit Sub
        Else
            Unload UserForm1
    End If

    'Determine Column
        For i = 10 To LColumn
            If WorksheetFunction.CountIf(Worksheets("RIG PMS").Cells(5, i), txtWeekNo.Value) > 0 Then
                Acolumn = i
            End If
        Next i

LRow = Worksheets("RIG PMS").Cells(Rows.Count, "I").End(xlUp).Row
Set OpenWORng = Worksheets("Open WO").Range("B3:B" & Worksheets("Open WO").Range("B" & Rows.Count).End(xlUp).Row)

    For a = 7 To LRow Step 3
        AA = 0
        If Worksheets("RIG PMS").Cells(a, Acolumn).Value <> "" And Worksheets("RIG PMS").Cells(a, Acolumn - 2).Value = 1 Then
            b = Worksheets("RIG PMS").Cells(a, 7).Value
            Worksheets("Power Pack Monthly").Range("C5").Value = Worksheets("RIG PMS").Cells(a - b, 2).Value 'Asset Code
            Worksheets("Power Pack Monthly").Range("C6").Value = Worksheets("RIG PMS").Cells(a - b, 3).Value 'Description
            Worksheets("Power Pack Monthly").Range("P5").Value = Worksheets("RIG PMS").Cells(a, Acolumn).Value 'WO
            Worksheets("Power Pack Monthly").Range("P6").Value = Worksheets("RIG PMS").Cells(a + 1, Acolumn).Value 'Proposed Date
            AA = 1
        End If

        If Worksheets("RIG PMS").Cells(a, Acolumn).Value <> "" And Worksheets("RIG PMS").Cells(a, Acolumn - 2).Value = 2 Then
            b = Worksheets("RIG PMS").Cells(a, 7).Value
            Worksheets("Power Pack 6 Monthly").Range("C5").Value = Worksheets("RIG PMS").Cells(a - b, 2).Value 'Asset Code
            Worksheets("Power Pack 6 Monthly").Range("C6").Value = Worksheets("RIG PMS").Cells(a - b, 3).Value 'Description
            Worksheets("Power Pack 6 Monthly").Range("P5").Value = Worksheets("RIG PMS").Cells(a, Acolumn).Value 'WO
            Worksheets("Power Pack 6 Monthly").Range("P6").Value = Worksheets("RIG PMS").Cells(a + 1, Acolumn).Value 'Proposed Date
            AA = 1
        End If

    'Copy Open WO to Open WO Sheet
         If WorksheetFunction.CountIf(OpenWORng, Worksheets("RIG PMS").Cells(a, Acolumn).Value) = 0 And AA = 1 Then
            Worksheets("Open WO").Range("B" & Worksheets("Open WO").Range("B" & Rows.Count).End(xlUp).Row + 1).Value = Worksheets("RIG PMS").Cells(a, Acolumn).Value 'WO No.
            Worksheets("Open WO").Range("C" & Worksheets("Open WO").Range("C" & Rows.Count).End(xlUp).Row + 1).Value = Worksheets("RIG PMS").Cells(a - b, 2).Value 'Asset Code
            Worksheets("Open WO").Range("D" & Worksheets("Open WO").Range("D" & Rows.Count).End(xlUp).Row + 1).Value = Worksheets("RIG PMS").Cells(a - b, 3).Value 'Asset Description
            Worksheets("Open WO").Range("E" & Worksheets("Open WO").Range("D" & Rows.Count).End(xlUp).Row).Value = 1 'For trigger purpose
        End If

        If a = LRow - 2 Then
            MsgBox "Continue"
            Exit Sub
        End If
    Next a

End Sub

Sub BCD()

   MsgBox "ABC"

End Sub

call BCD will be insert of the bottom of the sub ABC, then It play only when all condition in the sub BCD was confirmed — Fabrizio, Nov 19 '15 at 08:39
Fabrizio, i did insert call BCD at the bottom of sub ABC but it still not worked — cklim, Nov 20 '15 at 07:08
I haven't your file then I can't test it, but is logical if you have insert BCD in the bottom of sub ABC, and you has deleted the call BCD on cmdOK1_Click(), the sub BCD run only if the conditions that you have check will be true. — Fabrizio, Nov 23 '15 at 15:48

EEM · Answer 1 · 2015-12-04T22:30:04.063

Error reproduction: I was able to reproduce the behavior mentioned in the question by having all procedures posted located in the CodeModule of the UserForm.

Reason for the behavior: The second procedure BCD is not running when the week number is valid because the UserFrom is unloaded as per this line:

Unload UserForm1

The remarks at Unload Statement says:

Remarks: When an object is unloaded, it's removed from memory and all memory associated with the object is reclaimed. Until it is placed in memory again using the Load statement, a user can't interact with an object, and the object can't be manipulated programmatically.

Solution: Although several solutions apply, in my opinion the easiest is to move procedure BCD to a standard module - tested.

Other Solutions:

Move both procedures out of the UserForm but the all references to objects in the UserForm have to be qualified (i.e. replace Me with UserForm1 and references to TxtWeekNo have to be qualified as UserForm1.TxtWeekNo) - tested.
Hide the user form instead and unload it at the end of the cmdOK1_Click procedure.
and probably many others.

score -3 · Answer 2 · answered Nov 19 '15 at 05:55

-3

The code is in the class module for the form. It should be in a standard module.

answered Nov 19 '15 at 05:55

shg

323
2
7

shg and Davesexcel : no i doesn't need to be in the command button code, nor in a separate module. You can call it from anywhere in the same workbook (if not a private sub). By the way, you say "my code was unable to call sub BCD" , meaning error message, infinite loop, code Stop ,.... ?? My advice , add a simple break point to ABC and debug it step by step (F8 key)... – Patrick Lepelletier Nov 19 '15 at 16:14
Patrick, no error pop up when execute this code. I used F8 for step by step debug. It work for Sub ABC as per expected. MsgBox "Continue" did pop up as well then it jump back to Private Sub cmdOK1_Click() and step into Sub Call in Private Sub then directly go to End Sub instead of jump to Sub BCD to pop up MsgBox "ABC" – cklim Nov 20 '15 at 06:25

Unable to Call Subroutine

2 Answers2