excel 2013 vba: Invalid procedure call or argument

[jedwardo]

Hello,

I have this bit of code below pretty much working with one exception. The code takes the selections from 4 separate multi select listboxes on UserFormSplitBid and places the values from each listbox in cells A2:D2 respectively on sheet8. As long as all listboxes have a selection made when the command button is clicked the code works fine but it throws this error below when one of the listboxes has no selections made. In the code below the blue colored line was the highlighted line in debug. That section of code was for ListBoxBidC which had no selections.

Run-time error '5':
Invalid procedure call or argument

Private Sub CommandButton1_Click()
'Create single string with line feeds'from listbox selections.
Dim DestCellA As Range
Dim DestCellB As Range
Dim DestCellC As Range
Dim DestCellD As Range
Dim iCtrA As Long
Dim iCtrB As Long
Dim iCtrC As Long
Dim iCtrD As Long
Dim inextRow As Long
Dim strTempA
Dim strTempB
Dim strTempC
Dim strTempD
inextRow = 2


With Sheet8
  Set DestCellA = .Range("A" & inextRow)
End With


With UserFormSplitBid.ListBoxBidA
  DestCellA.ClearContents
  
  For iCtrA = 0 To .ListCount - 1
    If .Selected(iCtrA) Then
      strTempA = strTempA & .List(iCtrA) & vbLf
    End If
  Next iCtrA
End With


'Remove trailing linefeed
strTempA = Left(strTempA, Len(strTempA) - 1)
DestCellA.Value = strTempA




With Sheet8
  Set DestCellB = .Range("B" & inextRow)
End With


With UserFormSplitBid.ListBoxBidB
  DestCellB.ClearContents
  
  For iCtrB = 0 To .ListCount - 1
    If .Selected(iCtrB) Then
      strTempB = strTempB & .List(iCtrB) & vbLf
    End If
  Next iCtrB
End With


'Remove trailing linefeed
strTempB = Left(strTempB, Len(strTempB) - 1)
DestCellB.Value = strTempB






With Sheet8
  Set DestCellC = .Range("C" & inextRow)
End With


With UserFormSplitBid.ListBoxBidC
  DestCellC.ClearContents
  
  For iCtrC = 0 To .ListCount - 1
    If .Selected(iCtrC) Then
      strTempC = strTempC & .List(iCtrC) & vbLf
    End If
  Next iCtrC
End With


'Remove trailing linefeed
[COLOR=#0000ff]strTempC = Left(strTempC, Len(strTempC) - 1)[/COLOR]
DestCellC.Value = strTempC






With Sheet8
  Set DestCellD = .Range("D" & inextRow)
End With


With UserFormSplitBid.ListBoxBidD
  DestCellD.ClearContents
  
  For iCtrD = 0 To .ListCount - 1
    If .Selected(iCtrD) Then
      strTempD = strTempD & .List(iCtrD) & vbLf
    End If
  Next iCtrD
End With


'Remove trailing linefeed
strTempD = Left(strTempD, Len(strTempD) - 1)
DestCellD.Value = strTempD




With UserFormMain
.TextBoxBidA.Value = Sheet8.Cells(2, 1).Value
.TextBoxBidB.Value = Sheet8.Cells(2, 2).Value
.TextBoxBidC.Value = Sheet8.Cells(2, 3).Value
.TextBoxBidD.Value = Sheet8.Cells(2, 4).Value
End With




Unload UserFormSplitBid
UserFormMain.Show


End Sub

If any information needed is lacking just let me know.

Regards,

Jordan

 

[NdNoviceHlp]

If UserFormSplitBid.ListBoxBidC.Listindex = -1 then
msgbox "Make a selection"
exit sub
end if

HTH. Dave

 

[Trebor76]

You can also account for no selection made ie,

If Len(strTempC) > 0 Then
    strTempC = Left(strTempC, Len(strTempC) - 1)
End If

HTH

Robert

 

[jedwardo]

Thanks Dave and Robert, that second one did the trick for me as there won't always be a selection made in every listbox. Happy Holidays!

 

[Trebor76]

You're welcome

You can also use the TRIM function as this will remove leading and trailing spaces and will not error out even if your variable is empty, ie:

strTempC = Trim(strTempC)

Regards,

Robert

 

[jedwardo]

Hey thanks again, I love this forum