Target.Row not working right in VBA

Question

Some weirdness I don't understand here. Small routine here that launches an email composition window. Target.Row is returning the correct row when clicked and concatenates correctly when done as a separate variable (see ??? section in middle). When I use it directly within the 'With MItem' block it pulls the values from the next row of the spreadsheet, not the row indicated by the Target.Row value. ex: row #3 is clicked on, and it loads the values from row #4. When I use a pre-concatenated variable, it pulls the correct cell values. Any ideas?? Thanks in advance for any help.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' checking to see if update number cell is picked
    If Not Intersect(Target, Range("B3:B50")) Is Nothing Then
        ' testing for blank subject box
        If (Worksheets("Email").Range("C" & Target.Row).Value <> "") Then
            ' initialize variables
            Dim OutlookApp As Outlook.Application
            Dim MItem As Outlook.MailItem
            Dim sBlock As String


            ' ????
             Dim nTest As Integer
             Dim sRange As String
             nTest = Target.Row
             sRange = "C" & Target.Row                

            ' creating html signature block
            sBlock = "<br><br><font size=4>Name Here</font><br>"
            sBlock = sBlock & "<font size=2><b>Title</b><br>"
            sBlock = sBlock & "<b>phone 1</b><br>"
            sBlock = sBlock & "<b>phone 2</b></font><br>"

            'Create Outlook object
            Set OutlookApp = New Outlook.Application

            'Create Mail Item and view before sending
            Set MItem = OutlookApp.CreateItem(olMailItem)
            With MItem
                .To = Worksheets("Emails").Range("D" & Target.Row).Value
                .Subject = Worksheets("Emails").Range("C" & Target.Row).Value
                .HTMLBody = Worksheets("Emails").Range("E" & Target.Row).Value & sBlock
                .Display
            End With
        End If
    End If
End Sub

If it is consistent you could just go with ("D" & Target.Row -1) if you don't hear back with a better answer as to why. ... also, I don't suppose you have a worksheet named email and another named emails and they are different by one row being offset? your line 5 references "Email" and your with block uses "Emails" — Rodger, Jul 13 '16 at 13:50
Just FYI, you should add code to confirm that the `Target` is only a single cell, or this may throw an error if you were to select multiple cells — RGA, Jul 13 '16 at 13:54
Another FYI - if the user has a default signature already set up you can use the accepted answer in [this question](http://stackoverflow.com/questions/8994116/how-to-add-default-signature-in-outlook) which may help you. — Scott Holtzman, Jul 13 '16 at 13:56
Thanks for the replies. Target -1 is working for now. The "Email" "Emails" worksheet was a duplicate sheet I had for testing purposes. As for the link pointing to the other solution, I tried that and the email composition window refused to come up. Not sure why not as I don't have access to any Outlook dll errors that are throwing. — Grymjack, Jul 13 '16 at 14:40

score 1 · Answer 1 · answered Jul 13 '16 at 14:09

1

UseWorksheet_Change event and like RGA commented I would check to see if more then one cell is being changed.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub

               ... Your Code.....

End Sub

answered Jul 13 '16 at 14:09

**Always** when working with Worksheet_change and target consider a loop because user may do so -unless you protect the sheet- _Private Sub Worksheet_Change(ByVal Target As Range)_ **Dim ItemInRange as Range** For each ItemInRange in Target 'your code .... Next ItemInRange – Sgdva Jul 13 '16 at 15:11
This is because user may delete multiple data and even it doesn't fall in your condition it will set an error exception due to target being only "one _celled_" – Sgdva Jul 13 '16 at 15:14

Target.Row not working right in VBA

1 Answers1