Run Macro if Cell Contains...

[eli_m]

Hi,

In my worksheet, I have Column U that sometimes has "Create Calendar Invite - DATEHERE" in the cell.

I want it so if any cell in Column U has "Create Calendar Invite*" in it and then I click that cell, it runs a Macro I have set up named 'CalendarInvite'

Is there a way to make a hyperlink to run a macro or does it have to be VBA?

I've tried the below with no success:

If Not Intersect(Selection, Range("U:U")) Is Nothing And Selection.Count = 1 Then
        If InStr(1, Cells(ActiveCell.Row, "U").Value, "Create Calendar Invite*") > 0 Then
            Call CalendarInvite
        End If
    End If

Full code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CutCopyMode = False Then
Application.Calculate ' Refresh for Grey Line
End If

If Not Intersect(Selection, Range("U:U")) Is Nothing And Selection.Count = 1 Then
        If InStr(1, Cells(ActiveCell.Row, "U").Value, "Create Calendar Invite*") > 0 Then
            Call CalendarInvite
        End If
    End If

'Check Timeout timer
checktime = True
Lastchange = Now()

End Sub

Thanks in advance!

 

[kevin9999]

Try this:

If Not Intersect(Selection, Range("U:U")) Is Nothing And Selection.Count = 1 Then
    If Target.Value Like "Create Calendar Invite*" Then
        Call CalendarInvite
    End If
End If

 

[eli_m]

Try this:

If Not Intersect(Selection, Range("U:U")) Is Nothing And Selection.Count = 1 Then
    If Target.Value Like "Create Calendar Invite*" Then
        Call CalendarInvite
    End If
End If

Thanks for your help! Sadly that didn't work. I think it has to do with the fact that the "Create Calendar Invite*" comes from the Formula:

=IF(ISBLANK([@[Surgery Date]]),"",
IF(AND([@[Hospital Type]]="Private",COUNTIF('Rooms Form'!$A$1:$A$50,[@Surgeon])>0,[@[Surgery Date Checker]]="No Date"),
"Automatically Created - 25-Dec",
IF([@[Surgery Date Checker]]="No Date","Surgery Date TBC",
IF(OR(ISNUMBER([@[Loan Booking Text Date]]),AND([@[Hospital Type]]="Private",COUNTIF('Rooms Form'!$A$1:$A$50,[@Surgeon])>0)),CONCATENATE("Create Calandar Invite - ",TEXT([@[Surgery Date]],"dd-MMM")),""))))

Is there a way around this?

Also when I type the text in the cell I get this error:

 

[Skyybot]

Try typing

Call CalendarInvite.[NameOfSub]

no brackets obviously.

 

[eli_m]

T

Try typing

Call CalendarInvite.[NameOfSub]

no brackets obviously.

That worked but now I just need to get the other part working as it doesn't work when the formula says "Create Calendar Invite"

 

[Skyybot]

If Not Target.Find ("Create Calendar Invite") = Nothing Then

 

[eli_m]

If Target.Value = "Create Calendar Invite" Then

Just confirming my whole code which didn't work:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CutCopyMode = False Then
Application.Calculate ' Refresh for Grey Line
End If

If Not Intersect(Selection, Range("U:U")) Is Nothing And Selection.Count = 1 Then
    If Target.Value = "Create Calendar Invite" Then
        Call CalendarInvite.CalendarInvite
    End If
End If

'Check Timeout timer
checktime = True
Lastchange = Now()

End Sub

I also tried with *:
If Target.Value = "Create Calendar Invite" Then

 

[Skyybot]

I edited Post 6 after you replied.

 

[eli_m]

I edited Post 6 after you replied.

I get this error:

 

[Skyybot]

If Not Target.Find("Create Calendar Invite") Is Nothing Then