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

 

[Gideon du Toit]

I have now noticed that when I select the first item in the listbox the call runs. If I select any item below the first, the call code gets read but does not execute the actual sub. It does not even get called at all

 

[Gideon du Toit]

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.

That code does get read and the msgbox pops up. Thereafter it reads the sub call but it doesn't execute it. It moves straight to the next line of code

 

[Joe4]

OK, I don't think I can offer much more in the way of assistance without actually having access to the file where I can see/test/debug it for myself.
Many some others will have other ideas.

 

[Gideon du Toit]

I have made another sub containing only your msgbox and called that just after and just before the problematic call statement. None of them run either. They get read after the condition is met in the If statement, but they don't run., And this is only true if any list item beyond the first item is selected.

 

[dmt32]

Hi,
just a guess but see if making your If statement case insensitive helps resolving the issue

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

'rest of code

If still an issue - place copy of your workbook with dummy data in a file sharing site like dropbox & provide a link to it will help forum to understand the problem.
Dave

 

[Gideon du Toit]

Hi,
just a guess but see if making your If statement case insensitive helps resolving the issue

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

'rest of code

If still an issue - place copy of your workbook with dummy data in a file sharing site like dropbox & provide a link to it will help forum to understand the problem.
Dave

Thank you for your suggestion Dave. I have tried the UCase but it remains the same. It meets the condition and reads the code that should execute after the condition is met. It just refuses to execute any calls to other subs in the module. I will consider posting the file, although it is huge, might need some instruction and I am a little embarrassed about my coding skills and style

 

[Joe4]

If you made a "simpler version", as you mentioned in post 14, that exhibits the same behavior, maybe you could just upload that one instead?

 

[Gideon du Toit]

If you made a "simpler version", as you mentioned in post 14, that exhibits the same behavior, maybe you could just upload that one instead?

I think you must have misunderstood. I did not make a simpler version, Only added another sub that should be called instead of the more complicated sub that gets called. This sub was named "Testbox" and I placed it's call right before the problematic call and another instance after. None of them gets called. The code just reads through them.

Private Sub TestBox()

MsgBox "Condition met, calling Get_Consult procedure"

End Sub

 

[Joe4]

I think you must have misunderstood. I did not make a simpler version, Only added another sub that should be called instead of the more complicated sub that gets called. This sub was named "Testbox" and I placed it's call right before the problematic call and another instance after. None of them gets called. The code just reads through them.

Private Sub TestBox()

MsgBox "Condition met, calling Get_Consult procedure"

End Sub

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.

 

[dmt32]

Thank you for your suggestion Dave. I have tried the UCase but it remains the same.

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