Assign text to a Macro

E

ExcelRoy

Well-known Member
Joined
Oct 2, 2006
Messages
2,540
Office Version
  1. 365
Platform
  1. Windows
Hi,

Is there a way to assign text to run a macro when clicked

Many thanks for any help
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Hello ExcelRoy,

A few ways come to mind. You can do this with text in worksheet cells, text entered into TextBoxes, ListBoxes, or Comboxes.
 
Upvote 0
Hi Leith,

Yes text in worksheet cells is what I am looking for

How do I link the text to a macro?

Many thanks
 
Upvote 0
Hello ExcelRoy,

You will have to call the macro from the Worksheet_Change event. In the change event, you will need to compare the cell text with one or more text values. If there is a match then the macro will be called during the event.

The variable Text holds the text the cell must match. Macro1 should be changed to match the name of the macro you want to run.

Code: 
Private Sub Worksheet_Change(Target As Range)


    Dim Cell As Range
    Dim Text As String


        Text = "My Text"


	If Target.Cells.Count > 1 Then Exit Sub
        
        If Target.Value = Text Then
	    Call Macro1
        End If


End Sub
 
Upvote 0
Here is an example (I haven't tried it yet). Very simple ... test it out.

Code: 
[COLOR=#242729][FONT=Arial]es you can, follow the below Simple Steps to do so:
[/FONT][/COLOR][COLOR=#242729][FONT=Arial]Step 1. Select the Cell Where you want to make the Hyperlink 

Step 2. Righ Click –> Hyperlink… 

Step 3. Enter the Address of the Same cell where you are making the hyperlink and Give name to the Link. [/FONT][/COLOR]

[COLOR=#242729][FONT=Arial]Step 4. Click Ok. Step 5. HyperLink is created.
[/FONT][/COLOR]
[COLOR=#242729][FONT=Arial]Note: Clicking on this Hyperlink, will do nothing because it is assigned to the same Cell Address.[/FONT][/COLOR]
[COLOR=#242729][FONT=Arial]
Step 6. Now Press Alt + F11 

Step 7. Copy paste the below Code[/FONT][/COLOR]
[COLOR=#242729][FONT=Arial]Run Excel Macro by Clicking on a Hyperlink

[/FONT][/COLOR]
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">[COLOR=#101094]Private[/COLOR][COLOR=#101094]Sub[/COLOR][COLOR=#303336] Worksheet_FollowHyperlink[/COLOR][COLOR=#303336]([/COLOR][COLOR=#101094]ByVal[/COLOR][COLOR=#303336] Target [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Hyperlink[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]  
    [/COLOR][COLOR=#858C93]'Check if the Target Address is same as you have given  [/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'In the above example i have taken A4 Cell, so I am  [/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'Comparing this with $A$4  [/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] Target[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Address [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]"$A$4"[/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]  
        [/COLOR][COLOR=#858C93]'Write your all VBA Code, which you want to execute  [/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#858C93]'Or Call the function or Macro which you have  [/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#858C93]'written or recorded.  [/COLOR][COLOR=#303336]
        MsgBox [/COLOR][COLOR=#7D2727]"Write your Code here to be executed"[/COLOR][COLOR=#303336]  
        [/COLOR][COLOR=#101094]Exit[/COLOR][COLOR=#101094]Sub[/COLOR][COLOR=#303336]  
    [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336]  
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]Sub

[/COLOR]</code>[COLOR=#242729][FONT=Arial]In the Above Code we are comparing the Cell Address and then Executing a Set of Code or Function. There is another way of doing this also. We can Compare with the Target Name and execute the Code.
 In the above Example as i have given the Name of the Hyperlink Target as MyMacro.

[/FONT][/COLOR]
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">[COLOR=#101094]Private[/COLOR][COLOR=#101094]Sub[/COLOR][COLOR=#303336] Worksheet_FollowHyperlink[/COLOR][COLOR=#303336]([/COLOR][COLOR=#101094]ByVal[/COLOR][COLOR=#303336] Target [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Hyperlink[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]  
    [/COLOR][COLOR=#858C93]'Check if the Target Name is same as you have given  [/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'In the above example i have given the Name of the HyperLink  [/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'is MyMacro.  [/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] Target[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Name [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]"mymacro"[/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]  
        [/COLOR][COLOR=#858C93]'Write your all VBA Code, which you want to execute  [/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#858C93]'Or Call the function or Macro which you have  [/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#858C93]'written or recorded.  [/COLOR][COLOR=#303336]
        MsgBox [/COLOR][COLOR=#7D2727]"Write your Code here to be executed"[/COLOR][COLOR=#303336]  
        [/COLOR][COLOR=#101094]Exit[/COLOR][COLOR=#101094]Sub[/COLOR][COLOR=#303336]  
    [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]If[/COLOR]</code>[COLOR=#101094][FONT=Consolas]End[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Sub[/FONT][/COLOR]

Here is the link to the article:

In Excel, can I use a hyperlink to run vba macro? - Stack Overflow
 
Upvote 0
Logit said:
Here is an example (I haven't tried it yet). Very simple ... test it out.

Code: 
[COLOR=#242729][FONT=Arial]es you can, follow the below Simple Steps to do so:
[/FONT][/COLOR][COLOR=#242729][FONT=Arial]Step 1. Select the Cell Where you want to make the Hyperlink 

Step 2. Righ Click –> Hyperlink… 

Step 3. Enter the Address of the Same cell where you are making the hyperlink and Give name to the Link. [/FONT][/COLOR]

[COLOR=#242729][FONT=Arial]Step 4. Click Ok. Step 5. HyperLink is created.
[/FONT][/COLOR]
[COLOR=#242729][FONT=Arial]Note: Clicking on this Hyperlink, will do nothing because it is assigned to the same Cell Address.[/FONT][/COLOR]
[COLOR=#242729][FONT=Arial]
Step 6. Now Press Alt + F11 

Step 7. Copy paste the below Code[/FONT][/COLOR]
[COLOR=#242729][FONT=Arial]Run Excel Macro by Clicking on a Hyperlink

[/FONT][/COLOR]
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">[COLOR=#101094]Private[/COLOR][COLOR=#101094]Sub[/COLOR][COLOR=#303336] Worksheet_FollowHyperlink[/COLOR][COLOR=#303336]([/COLOR][COLOR=#101094]ByVal[/COLOR][COLOR=#303336] Target [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Hyperlink[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]  
    [/COLOR][COLOR=#858C93]'Check if the Target Address is same as you have given  [/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'In the above example i have taken A4 Cell, so I am  [/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'Comparing this with $A$4  [/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] Target[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Address [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]"$A$4"[/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]  
        [/COLOR][COLOR=#858C93]'Write your all VBA Code, which you want to execute  [/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#858C93]'Or Call the function or Macro which you have  [/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#858C93]'written or recorded.  [/COLOR][COLOR=#303336]
        MsgBox [/COLOR][COLOR=#7D2727]"Write your Code here to be executed"[/COLOR][COLOR=#303336]  
        [/COLOR][COLOR=#101094]Exit[/COLOR][COLOR=#101094]Sub[/COLOR][COLOR=#303336]  
    [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336]  
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]Sub

[/COLOR]</code>[COLOR=#242729][FONT=Arial]In the Above Code we are comparing the Cell Address and then Executing a Set of Code or Function. There is another way of doing this also. We can Compare with the Target Name and execute the Code.
 In the above Example as i have given the Name of the Hyperlink Target as MyMacro.

[/FONT][/COLOR]
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">[COLOR=#101094]Private[/COLOR][COLOR=#101094]Sub[/COLOR][COLOR=#303336] Worksheet_FollowHyperlink[/COLOR][COLOR=#303336]([/COLOR][COLOR=#101094]ByVal[/COLOR][COLOR=#303336] Target [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Hyperlink[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]  
    [/COLOR][COLOR=#858C93]'Check if the Target Name is same as you have given  [/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'In the above example i have given the Name of the HyperLink  [/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'is MyMacro.  [/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] Target[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Name [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]"mymacro"[/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]  
        [/COLOR][COLOR=#858C93]'Write your all VBA Code, which you want to execute  [/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#858C93]'Or Call the function or Macro which you have  [/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#858C93]'written or recorded.  [/COLOR][COLOR=#303336]
        MsgBox [/COLOR][COLOR=#7D2727]"Write your Code here to be executed"[/COLOR][COLOR=#303336]  
        [/COLOR][COLOR=#101094]Exit[/COLOR][COLOR=#101094]Sub[/COLOR][COLOR=#303336]  
    [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]If[/COLOR]</code>[COLOR=#101094][FONT=Consolas]End[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Sub[/FONT][/COLOR]

Here is the link to the article:

In Excel, can I use a hyperlink to run vba macro? - Stack Overflow
Click to expand...

Hello Logit,

I received your PM but I could not reply because your storage is full. Can you please try and clear some space because I would really and truly appreciate the help!

Thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
If/Else conditions assigned to an image to run various actions
Replies
4
Views
84
SanjayGMusafir
SanjayGMusafir
gheyman
  • Solved
Missing Macros
2
Replies
10
Views
80
Joe4
Joe4
N
  • Solved
Make Copy and Paste Run Faster with Assignments
2
Replies
10
Views
333
CephasOz
CephasOz
A
  • Question
Help Using VBA To Create A Workbook Containing VBA
Replies
2
Views
385
Aldk_1
A
D
  • Solved
I can't assign a macro to a button.
Replies
5
Views
651
Joe4
Joe4

Forum statistics

Threads
1,214,583
Messages
6,120,380
Members
448,955
Latest member
BatCoder

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top