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!

 

[eli_m]

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

That works if I type in the cell "Create Calendar Invite - 02-Dec" it works:

but If the formula puts the text in it doesn't work:

So I think there's an issue when the formula says it..

 

[Skyybot]

Only other thing I can think of is

If Not Target.Find("Create Calendar Invite",LookIn:= xlValues) Is Nothing Then

 

[eli_m]

Only other thing I can think of is

If Not Target.Find("Create Calendar Invite",LookIn:= xlValues) Is Nothing Then

Sadly that didnt' work either.. I wonder if there is another way to run the macro by clicking the cell if it has Formula Text

 

[bebo021999]

The word "Calandar" is definitely a misspelling, and does it actually appear in your formula?
Try correcting it and see if it works

 

[Skyybot]

*Giggles*

Definition of CALENDAR

a system for fixing the beginning, length, and divisions of the civil year and arranging days and longer divisions of time (such as weeks and months) in a definite order… See the full definition

www.merriam-webster.com

 

[eli_m]

The word "Calandar" is definitely a misspelling, and does it actually appear in your formula?
Try correcting it and see if it works

View attachment 118677

You sir are a GENIUS!!!

 

[eli_m]

This code has been working well most of the week but every now and then I get an error on line:

If Not Intersect(Selection, Range("U:U")) Is Nothing And Selection.Count = 1 Then

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 Not Target.Find("Create Calendar Invite",LookIn:= xlValues) Is Nothing Then
            Call CalendarInvite
        End If
    End If

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

End Sub

It normally happens when I change the sheet to another, but when I try to recreate it again, it doesn't error again to take a screenshot.

Basically I just want the the below part of the code to run only if I am in Column U so its not running on every SelectionChange:

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

Is there anything I can do to stop this error?

 

[eli_m]

This code has been working well most of the week but every now and then I get an error on line:

If Not Intersect(Selection, Range("U:U")) Is Nothing And Selection.Count = 1 Then

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 Not Target.Find("Create Calendar Invite",LookIn:= xlValues) Is Nothing Then
            Call CalendarInvite
        End If
    End If

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

End Sub

It normally happens when I change the sheet to another, but when I try to recreate it again, it doesn't error again to take a screenshot.

Basically I just want the the below part of the code to run only if I am in Column U so its not running on every SelectionChange:

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

Is there anything I can do to stop this error?

I managed to get the screenshots:

 

[bebo021999]

1- Use "Target" instead of "Selection" may help?
or
2- Do not use "intersect":
If target.column=21 ... (21 is column index of column U)

If they still does not help:

3- Try to exit sub first:
If Intersect(Target, Range("U:U")) is Nothing or Target.count>1 then Exit Sub

 

[eli_m]

1- Use "Target" instead of "Selection" may help?
or
2- Do not use "intersect":
If target.column=21 ... (21 is column index of column U)

If they still does not help:

3- Try to exit sub first:
If Intersect(Target, Range("U:U")) is Nothing or Target.count>1 then Exit Sub

Thanks for that but no luck.

1. Doesn't run the macro

2.

3.