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

 

[Alex Blakenburg]

I am unable to work out why your call to a sub in the same Forms module doesn't run the way it is.
My workaround in post #27 will work. If that is of any interest in the form change the Call line to:

Call Get_Consult(Me.lstClients)

And move the sub to a Standard module adding the below to the top of the module and getting the sub to take the list box as a parameter.

Option Private Module

Public Sub Get_Consult(lstClients As MSForms.ListBox) ' Lookup Selected Consultant Details

 

[RoryA]

Your code unloads the form inside the loop through the listbox items. So if the first item isn't selected, the code has already unloaded the form by the time you process any other items. Although the running code will continue until it's finished, it won't call any other routines in the form.

 

[Gideon du Toit]

Your code unloads the form inside the loop through the listbox items. So if the first item isn't selected, the code has already unloaded the form by the time you process any other items. Although the running code will continue until it's finished, it won't call any other routines in the form.

Wow! Thank you so much! You are right. Somehow had the unload on the wrong side of the loop. I moved it to the other side and now everything works as it should. This was driving me insane! Thank you once again, you are a star!

 

[RoryA]

Glad we could help.