Results 1 to 3 of 3

Thread: ListBox error
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2015
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default ListBox error

    Hi,

    On my userform I have a ListBox that is auto generated based on a list on my main worksheet. From the list on the userform the user can select which people they want the file to be sent to. The code I wrote loops through each of the users in the ListBox to see which ones are selected. If they are selected than an email is sent. The code always works until it gets to the last name on the ListBox. Once it gets to the last name I get a "run-time error - Could not get the Selected property. Invalid argument.

    Code:
    Private Sub CommandButton1_Click()
    Dim LastRow As Long
    Dim toList As String
    Dim eSubject As String
    Dim eBody As String
    Dim curColumn   As Long
    Dim ctrl As Control
    Dim i As Integer
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    
    
    curColumn = 1
    LastRow = Worksheets("sht_data").Cells(Rows.Count, curColumn).End(xlUp).Row
    
    
            
            For i = 7 To LastRow
                If ListBox1.Selected(i - 6) = True Then
                    Set OutApp = CreateObject("Outlook.Application")
                    Set OutMail = OutApp.CreateItem(0)
                    
                    toList = Cells(i, 6)
                    eSubject = "Hotshop Metrics"
                    eBody = "Please see your attached Hotshop metrics report"
            
                    On Error Resume Next
            
                    With OutMail
                        .To = toList
                        .CC = ""
                        .BCC = ""
                        .Subject = eSubject
                        .BodyFormat = olFormatHTML
                        .Display
                        .HTMLBody = eBody & vbCrLf & .HTMLBody
                        '.Send
                    End With
            
                    On Error GoTo 0
                    Set OutMail = Nothing
                    Set OutApp = Nothing
                Else
                End If
            Next i
    End Sub

  2. #2
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,823
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ListBox error

    Hello zJenkins,

    Both the ListBox and the ComboBox indices start at zero, not one.

    Code:
    If ListBox1.Selected(i - 7) = True Then
    Sincerely,
    Leith Ross

  3. #3
    Board Regular
    Join Date
    Jun 2015
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ListBox error

    Quote Originally Posted by Leith Ross View Post
    Hello zJenkins,

    Both the ListBox and the ComboBox indices start at zero, not one.

    Code:
    If ListBox1.Selected(i - 7) = True Then
    Thanks Leith!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •