Changing the value of an adjacent cell

[King_irc]

Hi,

Using a vb code found on this forum, I was able to generate the word ‘sent’ in a cell adjacent to a typed hyperlink, when clicked.

I am trying to achieve the same thing when the clicked cell contains a hyperlink generated by the hyperlink() function, rather than a typed direct hyperlink.

So far I’ve not been able to achieve this. I also need this to happen for each cell in a given column.
If anything this orojext has shown me what an excel novice I am. Hoping you can help

 

[tonyyy]

The following might get you started...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Columns(1), Target) Is Nothing Then '<-- Change column number to match your hyperlink column
    If UCase(Left(Target, 4)) = "HTTP" Then Target.Offset(0, 1) = "sent"
End If
End Sub

Copy/Paste the code into the Sheet module that contains your hyperlinks.

Cheers,

Tony

 

[King_irc]

The following might get you started...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Columns(1), Target) Is Nothing Then '<-- Change column number to match your hyperlink column
    If UCase(Left(Target, 4)) = "HTTP" Then Target.Offset(0, 1) = "sent"
End If
End Sub

Copy/Paste the code into the Sheet module that contains your hyperlinks.

Cheers,

Tony

Thanks Tony, would this work if the hyperlink() function generates mailto? I assume that I would change the ‘HTTP’ to ‘mailto’?

 

[tonyyy]

If "mailto" will always be lower case, then you can modify the line to:

If Left(Target, 6) = "mailto" Then Target.Offset(0, 1) = "sent"

 

[King_irc]

If Left(Target, 6) = "mailto" Then Target.Offset(0, 1) = "sent"

I can't seem to get this to work. My mailto cell is in column AB, so I make that column 28, would that be correct? Also, mailto are the first 6 letters, then the rest of Hyperlink() function is included.

I've got the friendly name as 'Send Email' and that is what is actually displayed in the cell.

What I'm trying to achive is that once a certain condition is met, then the link appears to send email. Once that has been clicked, i'm trying to get the formula to realise that the hyperlink has been clicked - so the action cannot be repeated.

I know that I cannot insert something into the formula that sets the value of a different cell, but that is what I am trying to achieve.

SO as an example this what I have ( I have only set up the subject at this stage and not the body of the email:

In cell AB4:

=IF(OR(Y4="Underway",Y4=""),"",IF(AO4<>"","Email Sent",IF(Y4="Completed",IF(AO4="",HYPERLINK("mailto:"&AU4&"?subject="&AV4,"Send Email"),"Email Sent"),IF(Y4="Overdue",IF(AO4="",HYPERLINK("mailto:"&AU4&"?subject="&AW4,"Send Email"),"Email Sent")))))

AO4 is where I am trying to change once the email has been sent, which prevents the link from activating again. I've even tried timestamping with circular cell reference iteration being allowed but I cant get that to work either

 

[tonyyy]

Yes, column AB would be column 28. And if "Send Email" is displayed then that should be the target.value.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Columns(28), Target) Is Nothing Then
    If Target.Value = "Send Email" Then Target.Offset(0, 1) = "sent"
End If
End Sub

 

[King_irc]

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Columns(28), Target) Is Nothing Then If Target.Value = "Send Email" Then Target.Offset(0, 1) = "sent" End If End Sub

This is brilliant! working exactly as I needed - you're a star

 

[tonyyy]

Glad we got it to work for you.