Simple Sub Procedure Call not running

[Gideon du Toit]

I am busy on a VBA/ Excel project. Most of everything runs fine and I can step through the code as I debug. However, in the one click event, I call a sub named, Get_Consult. For some reason, this sub refuses to run. I added breaks inside the sub to step through the code, but although the call to the sub gets read from the original click event, the sub never runs. In effect the code jut passes the call and continues executing the code below.

I have no Error handling in this click event as yet, so it can't be a case of On Error Resume Next. This problem has been baffling me and has been preventing me from making progress.
Any help would be appreciated.

PS: My coding skills are pretty basic, so please keep that in mind

I am including the call as well as the sub below...

For X = 4 To intLastWBS 'Look for Client match in Client DB SHEET - 2222222222

If CStr(lstClients.List(iClient)) = CStr(Sheet2.Range("B" & X).Value) Then

Call Get_Consult

and the sub....

Private Sub Get_Consult() ' Lookup Selected Consultant Details

Dim iiFindSumConsult As Integer
Dim iListboxCount As Integer
Dim iConsultContactDetails As Integer
Dim LastCell As Range
Dim LastCellColRef As Long
Dim intLastWBS As Integer

For iListboxCount = 0 To lstClients.ListCount - 1

If lstClients.Selected(iListboxCount) Then

For iiFindSumConsult = 15 To 295 Step 45

If CStr(Sheet6.Range("F" & iiFindSumConsult).Value) = CStr(lstClients.List(iListboxCount)) Then

intCompanyCounter = Sheet6.Range("F" & iiFindSumConsult).Row

LastCellColRef = 7 'Column number to look in when finding last cell

Set LastCell = Sheet14.Cells(Rows.Count, LastCellColRef).End(xlUp).Offset(1, 0)

intLastWBS = CInt(LastCell.Row) - 1 'Pass Row address to variable

With Sheet16

.Range("E13").Value = Sheet6.Range("F" & (intCompanyCounter + 25))

For iConsultContactDetails = 5 To intLastWBS

If Sheet14.Range("G" & iConsultContactDetails).Value = .Range("E13").Value Then

.Range("E14").Value = Sheet14.Range("H" & iConsultContactDetails).Value
.Range("E15").Value = Sheet14.Range("I" & iConsultContactDetails).Value
Exit Sub

End If

Next iConsultContactDetails

End With

End If

Next iiFindSumConsult

End If

Next iListboxCount

End Sub


EDIT:
I have to add that both the procedure that I call from and the sub called are in the same module and adding a stop at the start of the called sub does nothing

 

[AOB]

Three things I would suggest...

• Don't wait until the code is finished before adding error handling. Error handling is actually the very first piece of code I write when putting together any sub or function. Come up with a structure that works for you and apply it immediately and consistently whenever you create a new sub or function. It will actually help you debug problems like this - there may be an error occurring somewhere that you are oblivious to and you can't see it because you don't have an appropriate handler.

Public Sub SomeNewSub()

    On Error GoTo ErrorHandler

    ....

Exit_SomeNewSub:
    On Error Resume Next
    ' Put any code here that MUST run before the sub exits, regardless of whether an error occurs or not
    ' Could be object terminations, variable cleanup, application-level property settings - whatever
    Exit Sub

ErrorHandler:
    ' Put any code here that you want to run in the event of an error being thrown
    ' Could be a message box prompting the error, or a function to log the error somewhere - whatever
    ' Suggest familiarising with the Err object to get the Err.Number, Err.Description, Err.Source etc.
    ' You may want to handle different errors in different ways etc.
    Resume Exit_SomeNewSub
    
End Sub

• Add Option Explicit to the very top of the module in question, if it's not there already (this, similar to error handling with subs/functions, should be the very first thing in any new module) Then Debug > Compile [ProjectName] - does the code compile? It may be that you have a variable out of scope somewhere...

• Your initial code appears to triggered on the back of a click event - have you checked to confirm events are enabled? Is the "click" registering (if you put a breakpoint in the first line of the event procedure, does the code break at that point when you perform the click?) Open the Immediate Window (View > Immediate Window or Ctrl+G) and key : Debug.Print Application.EnableEvents - if it returns False, your events are disabled, including your click event.

 

[Joe4]

Many times, when someone's code works when they step through the code manually, but not when run when called, it is often because they are NOT on the correct sheet with the code is called.
So unqualified range references (range references without a sheet component) often do not work correctly, as they are looking at the wrong sheet.

Not saying that this is definitely what is going on here, I just wanted to mention that this is commonly the issue, so you probably want to verify that and make adjustments where necessary.

 

[AOB]

Many times, when someone's code works when they step through the code manually, but not when run when called, it is often because they are NOT on the correct sheet with the code is called.
So unqualified range references (range references without a sheet component) often do not work correctly, as they are looking at the wrong sheet.

Not saying that this is definitely what is going on here, I just wanted to mention that this is commonly the issue, so you probably want to verify that and make adjustments where necessary.

Excellent point @Joe4 and I was just about to edit my original response with a 4th point around the qualification of lstClients - if this code is in a Module (and not a Worksheet or Form object) then it has no way of identifying what lstClients is and must be declared & defined like any other variable. I figured Option Explicit might catch that...

 

[Gideon du Toit]

Three things I would suggest...

• Don't wait until the code is finished before adding error handling. Error handling is actually the very first piece of code I write when putting together any sub or function. Come up with a structure that works for you and apply it immediately and consistently whenever you create a new sub or function. It will actually help you debug problems like this - there may be an error occurring somewhere that you are oblivious to and you can't see it because you don't have an appropriate handler.

Public Sub SomeNewSub()

    On Error GoTo ErrorHandler

    ....

Exit_SomeNewSub:
    On Error Resume Next
    ' Put any code here that MUST run before the sub exits, regardless of whether an error occurs or not
    ' Could be object terminations, variable cleanup, application-level property settings - whatever
    Exit Sub

ErrorHandler:
    ' Put any code here that you want to run in the event of an error being thrown
    ' Could be a message box prompting the error, or a function to log the error somewhere - whatever
    ' Suggest familiarising with the Err object to get the Err.Number, Err.Description, Err.Source etc.
    ' You may want to handle different errors in different ways etc.
    Resume Exit_SomeNewSub
   
End Sub

• Add Option Explicit to the very top of the module in question, if it's not there already (this, similar to error handling with subs/functions, should be the very first thing in any new module) Then Debug > Compile [ProjectName] - does the code compile? It may be that you have a variable out of scope somewhere...

• Your initial code appears to triggered on the back of a click event - have you checked to confirm events are enabled? Is the "click" registering (if you put a breakpoint in the first line of the event procedure, does the code break at that point when you perform the click?) Open the Immediate Window (View > Immediate Window or Ctrl+G) and key : Debug.Print Application.EnableEvents - if it returns False, your events are disabled, including your click event.

Thank you for your reply. I will definitely follow your advice. I have indeed place breakpoint at the start and throughout the click event, and it all fires and executes perfectly. It is only that one line of code, the line calling the other sub that gets read but not executed.

 

[Gideon du Toit]

Excellent point @Joe4 and I was just about to edit my original response with a 4th point around the qualification of lstClients - if this code is in a Module (and not a Worksheet or Form object) then it has no way of identifying what lstClients is and must be declared & defined like any other variable. I figured Option Explicit might catch that...

All of the the code is inside of a form.

 

[Joe4]

All of the the code is inside of a form.

Right, but you are still referencing things on sheets.

I think this line:

Set LastCell = Sheet14.Cells(Rows.Count, LastCellColRef).End(xlUp).Offset(1, 0)

should actually probably be written like this:

Set LastCell = Sheet14.Cells(Sheet14.Rows.Count, LastCellColRef).End(xlUp).Offset(1, 0)

 

[Gideon du Toit]

Many times, when someone's code works when they step through the code manually, but not when run when called, it is often because they are NOT on the correct sheet with the code is called.
So unqualified range references (range references without a sheet component) often do not work correctly, as they are looking at the wrong sheet.

Not saying that this is definitely what is going on here, I just wanted to mention that this is commonly the issue, so you probably want to verify that and make adjustments where necessary.

I have added Option Explicit to the top of the Form Module and have added an Error Handler that would display the error in a msgbox, but there seems to be no error and the code still refuses to execute that one line

 

[Gideon du Toit]

Right, but you are still referencing things on sheets.

I think this line:

Set LastCell = Sheet14.Cells(Rows.Count, LastCellColRef).End(xlUp).Offset(1, 0)

should actually probably be written like this:

Set LastCell = Sheet14.Cells(Sheet14.Rows.Count, LastCellColRef).End(xlUp).Offset(1, 0)

Thank you for that. I have fixed the line but still nothing fires. Not even the "Private Sub Get_Consult()" gets fired when I but a breakpoint on it.

 

[Joe4]

I think you may need to look more closely at the sub that calls it.
You have only posted a small snippet of it, so it is hard to fully analyze it.

However, you can make one small change to check/verify to see if the condition needed to call the other sub is being met, i.e.

For X = 4 To intLastWBS 'Look for Client match in Client DB SHEET - 2222222222
    If CStr(lstClients.List(iClient)) = CStr(Sheet2.Range("B" & X).Value) Then
        MsgBox "Condition met, calling Get_Consult procedure"
        Call Get_Consult

If you add that line in red, and then test it, if you do not get the MsgBox to pop up, then your condition is NOT being met.
That could indicate an issue with your IF or your For loop. You can add more MsgBox to test/confirm those conditions are being.