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]

Are you stepping through the code line-by-line (F8), or relying on breakpoints alone?

Can I suggest using F8 to "step into" the code here, one step at a time :

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
Next X

And add watches for each of the following :

• X
• intLastWBS
• lstClients
• iClient
• lstClients.List(iClient)
• CStr(lstClients.List(iClient))
• Sheet2.Range("B" & X).Value
• CStr(Sheet2.Range("B" & X).Value)

For each iteration of the loop, check and confirm in the Watch window that the values for each of those variables is exactly as you would expect it to be (and nothing suddenly "drops off", throws an odd message or holds a value contrary to your expectations)

 

[Gideon du Toit]

OK. People often do try to create a simplified example that show the issue (strip out all the unnecessary components), which often helps to isolate the problem.
You can do that, or try posting up your entire workbook with instructions on how to recreate the scenario.

Thank you for your continued suggestions. However, this project is a 250-hour work in progress and this procedure consists of several nestled loops pulling data from several locations on different sheets in order to complete the task. So simplifying it would be a mammoth task. I will post both the click event as well as the sub that gets called hereunder. I apologise beforehand for the quality of my coding as I am not really a coder...

Private Sub cmdLoad_Click()

Dim LastCell As Range
Dim LastCellColRef As Long
Dim intLastWBS As Integer
Dim iClient As Integer
Dim iListCount As Integer
Dim iLearnerCount As Integer

Dim X As Integer 'Internal Internal loop through client DB

On Error GoTo ErrTrap

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

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

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

For iClient = 0 To iListCount - 1 'Looping through LISTBOX to get Selections 

    If lstClients.Selected(iClient) Then
    
        Sheet16.Range("D20").Value = lstClients.List(iClient)
        Dim strListClients As String
        strListClients = CStr(lstClients.List(iClient))
        
            For X = 4 To intLastWBS 'Look for Client match in Client DB SHEET 
            
                If strListClients = CStr(Sheet2.Range("B" & X).Value) Then
                
                            Call Get_Consult
                            Call TestBox
                
                        With Sheet16
                
                            .Range("E16").Value = Format(Date, "Long Date")
                            .Range("D21").Value = Sheet2.Range("G" & X).Value 'Reg
                            .Range("D22").Value = Sheet2.Range("I" & X).Value 'SDL
                            .Range("D23").Value = Sheet2.Range("J" & X).Value 'SETA
                            .Range("D24").Value = Sheet2.Range("C" & X).Value 'Contact Person
                            .Range("D25").Value = Sheet2.Range("D" & X).Value 'Contact Number
                            .Range("D26").Value = Sheet2.Range("E" & X).Value 'Contact EMail
                            .Range("D27").Value = Sheet2.Range("F" & X).Value 'Physical Address
                            .Range("D28").Value = Sheet2.Range("H" & X).Value 'VAT Number
                            
                            Dim iTitleEnd As Integer
                            Dim strTitleReference As String
                            
                            iTitleEnd = InStr(1, Sheet1.Range("F4").Value, "-") 'Extract The Title Only
                            strTitleReference = Mid(Sheet1.Range("F4").Value, 1, (iTitleEnd - 1))
                            .Range("D37").Value = strTitleReference
                            
                            Dim iQIDEnd As Integer
                            Dim strQID As String
                            
                            iQIDEnd = InStr(1, Sheet1.Range("F4").Value, "-") ' Start Extracting the Qualification Code
                            strQID = Mid(Sheet1.Range("F4").Value, (iQIDEnd + 2))
                            iQIDEnd = InStr(1, strQID, ")") ' Finish Extracting the Qualification Code
                            strQID = Mid(strQID, 1, (iQIDEnd - 1))
                            iQIDEnd = InStr(1, strQID, "(") ' Remove the brackets
                            strQID = Mid(strQID, (iQIDEnd + 1), Len(strQID))
                            .Range("D38").Value = strQID
                            
                                If Sheet6.Range("F" & (intCompanyCounter + 7)).Value = "Yes" Then 'See if it is a BEE client
                                
                                    .Range("G36").Value = "BEE"
                                    
                                ElseIf Sheet6.Range("F" & (intCompanyCounter + 7)).Value = "No" Then
                                
                                    .Range("G36").Value = "Private"
                                    
                                End If
                                
                                If Sheet6.Range("F" & (intCompanyCounter + 21)).Value = "Yes" Then 'See if Bursary
                                
                                    .Range("J36").Font.Strikethrough = True 'Strikethrough Invalid Option
                                    
                                ElseIf Sheet6.Range("F" & (intCompanyCounter + 21)).Value = "No" Then
                                
                                    .Range("I36").Font.Strikethrough = True
                                    
                                End If
                                
                                Dim strNQF As String
                                
                                strNQF = GetNQF(Sheet1.Range("F4").Value) 'Get NQF from string
                                .Range("J38").Value = strNQF
                                
                                If Sheet6.Range("F" & (intCompanyCounter + 15)).Value = "Yes" Then 'See if E-Learning
                                
                                    .Range("F39").Value = "No" 'Not Traditional
                                    .Range("J39").Value = "Yes" 'Yes Elearning
                                    
                                ElseIf Sheet6.Range("F" & (intCompanyCounter + 15)).Value = "No" Then
                                
                                    .Range("F39").Value = "Yes" 'Yes Traditional
                                    .Range("J39").Value = "No" 'Not Elearning
                                    
                                End If
                                
                                .Range("E40").Value = Sheet1.Range("B4").Value 'Project START Date
                                .Range("I40").Value = Sheet1.Range("B5").Value 'Project END Date
                                
                                .Range("D32").Value = Sheet1.Range("B1").Value 'Project Reference same as Project Name
                                
                                .Range("D62").Value = Sheet6.Range("F" & (intCompanyCounter + 14)) 'Stipend Amount
                                .Range("E64").Value = Sheet6.Range("F" & (intCompanyCounter + 12)) 'Stipend Start Date
                                .Range("I64").Value = Sheet6.Range("F" & (intCompanyCounter + 13)) 'Stipend End Date
                                
                                Dim strCombDemographics As String
                                
                                strCombDemographics = "Able Body - " & Sheet6.Range("F" & (intCompanyCounter + 32)).Value & _
                                vbNewLine & "Disabled - " & Sheet6.Range("G" & (intCompanyCounter + 32)).Value 'Able Body Demographics & Disabled Demographics
                                .Range("D43").Value = strCombDemographics
                                '.Range("").Value = Sheet6.Range("G" & (intCompanyCounter + 22)).Value
                                
                            
                        End With
                
                End If
            
            Next X 
            
        Call SOC_Learners
           
    End If
    
    Unload frmSOC
   
Next iClient

Exit Sub
ErrTrap:
MsgBox Err.Description
Err.Clear

Unload frmSOC

End Sub

Subs that get called...

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 'if listbox item is selected

    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(Sheet14.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)) 'Client Rep/Consultant
                            
                                For iConsultContactDetails = 5 To intLastWBS
                                
                                    If Sheet14.Range("G" & iConsultContactDetails).Value = .Range("E13").Value Then
                                                                
                                        .Range("E14").Value = Sheet14.Range("H" & iConsultContactDetails).Value 'Client Rep/Consultant Phone
                                        .Range("E15").Value = Sheet14.Range("I" & iConsultContactDetails).Value 'Client Rep/Consultant Email
                                        Exit Sub
                                        
                                    End If
                                
                                Next iConsultContactDetails

                            End With
        
        End If
    
    Next iiFindSumConsult
End If
Next iListboxCount

End Sub

This second sub also gets called but just for testing. It also doesn't run...

Private Sub TestBox()

MsgBox "Condition met, calling Get_Consult procedure"

End Sub

 

[Gideon du Toit]

Are you stepping through the code line-by-line (F8), or relying on breakpoints alone?

Can I suggest using F8 to "step into" the code here, one step at a time :

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
Next X

And add watches for each of the following :

• X
• intLastWBS
• lstClients
• iClient
• lstClients.List(iClient)
• CStr(lstClients.List(iClient))
• Sheet2.Range("B" & X).Value
• CStr(Sheet2.Range("B" & X).Value)

For each iteration of the loop, check and confirm in the Watch window that the values for each of those variables is exactly as you would expect it to be (and nothing suddenly "drops off", throws an odd message or holds a value contrary to your expectations)

Thank you for your reply. I have watched each of those values, but they don't behave in any other way than they should. I have stepped through the code one line at a time from the top and everything behaves exactly as it should. I have to add that this "call pass" only happens when I select any other item but the first item on the list.

 

[Gideon du Toit]

No worries worth a try - you clearly have something more profound going on - if can upload a dummy file that will be very helpful - you are getting plenty of good support & hopefully, someone here can resolve for you.

Dave

I would be prepared to upload the entire file, I'm just not sure how...

 

[Joe4]

I would be prepared to upload the entire file, I'm just not sure how...

You just upload it to any file sharing site (Google Drive, OneDrive, BoxNet, etc) and post a link to it right here in the thread.
Just make sure that it is not password protected so people can download it.

 

[Gideon du Toit]

You just upload it to any file sharing site (Google Drive, OneDrive, BoxNet, etc) and post a link to it right here in the thread.
Just make sure that it is not password protected so people can download it.

Once the file is opened, navigate to a sheet named "SOC" and click on the "Populate SOC" button. A form named frmSOC will appear with a Listbox containing three options. Selecting the first option will run the code in the frmSOC's cmdLoad_Click which includes the problematic Call statement. Selecting the second or third option in the Listbox will run the code in the cmdLoad_Click sub but will ignore the call statements, which is the problem at hand.

The Code is in the frmSOC form module...

Beta Project 15.xlsm

drive.google.com

 

[Alex Blakenburg]

Will this work for you ?
• Create a standard module
• Put Option Private Module at the top of the module
• Move your called sub to that module and change it from Private to Public

 

[Gideon du Toit]

Will this work for you ?
• Create a standard module
• Put Option Private Module at the top of the module
• Move your called sub to that module and change it from Private to Public

The click event that performs the call and the sub that gets called both contain references to form controls. I have tried calling events in some of my modules from this click event, but the code just ignores those as well. For some reason, it does not want to execute any Calls. It reads them, and executes the rest of the code, just not anything that calls another sub

 

[Gideon du Toit]

I have posted a link to the document with instructions in Post number 26, in case anyone missed it

 

[Gideon du Toit]

I am posting the entire file in the hope that someone who is truly an Excel VBA expert might help me find a solution.

Once the file is opened, navigate to a sheet named "SOC" and click on the "Populate SOC" button. A form named frmSOC will appear with a Listbox containing three options. Selecting the first option will run the code in the frmSOC's cmdLoad_Click which includes the problematic Call statement. Selecting the second or third option in the Listbox will run the code in the cmdLoad_Click sub but will ignore the call statements, which is the problem at hand.

In other words, the Call statement will only execute when the first item in the listbox is selected. Whenever the second or third list item is selected, will will READ the call statement but not actually execute it. It never goes into the called sub.

The Code is in the frmSOC form module...

Beta Project 15.xlsm

drive.google.com