How can I assign a macro to a cell with text in it and do this with 2+ cells each referencing their own macro?

[ceroberts75]

Per my reply on this thread, would like to expand on this further.

I have a doc, that has columns pulling content from another sheet. Each column has a title, and I want to be able to click on each title to sort by the macro I have created for each option. Below shows 2, but there are 6 columns that I would like to have the text be connected to a macro.

Until I found this page, I had to use individual images/icons.

Thank you much in advance!

eg-
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.CountLarge > 1 Then Exit Sub
If Target.Address(0, 0) <> "C10,E10" Then Exit Sub
Application.ScreenUpdating = False
If Target = "Store List" Then
Call Sort_Store_List_By_Store_Name
If Target = "Last Visit 1" Then
Call Sort_Store_List_By_LastVisit_1
End If
Application.ScreenUpdating = True
End Sub

 

[ceroberts75]

I was able to figure out this part of it to allow what I want, but not double tapping.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count <> 1 Then Exit Sub
    
    Select Case True
        Case Not Intersect(Target, Range("K10")) Is Nothing
            Call Sort_Store_List_By_Store_Name
        Case Not Intersect(Target, Range("E10")) Is Nothing
            Call Sort_Store_List_By_LastVisit_1
        Case Not Intersect(Target, Range("F10")) Is Nothing
            Call Sort_Store_List_By_LastVisit_2
        Case Not Intersect(Target, Range("G10")) Is Nothing
            Call Sort_Store_List_By_BBY_Cluster
        Case Not Intersect(Target, Range("J10")) Is Nothing
            Call Sort_Store_List_By_Store_Name
    
    End Select
End Sub

 

[Logit]

How about thsi, no double tap involved.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count <> 1 Then Exit Sub
   
    Select Case True
        Case Not Intersect(Target, Range("K10")) Is Nothing
            MsgBox "K10 click"
        Case Not Intersect(Target, Range("E10")) Is Nothing
            MsgBox "E10 click"
        Case Not Intersect(Target, Range("F10")) Is Nothing
            MsgBox "F10 click"
        Case Not Intersect(Target, Range("G10")) Is Nothing
            MsgBox "G10 click"
        Case Not Intersect(Target, Range("J10")) Is Nothing
            MsgBox "J10 click"
   
    End Select
End Sub

 

[ceroberts75]

the purpose for using the double tap is so that if i needed to, I could actually select the cell and change the text.

With the single tap, even if you arrow over to the cell, it still activates the macro leaving the cell non-changable

 

[ceroberts75]

and yes, i found the magbox version...but adjusted it to my needs.

 

[Logit]

I misunderstood when you said 'but not double tapping.'

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Selection.Count <> 1 Then Exit Sub
    
    Select Case True
        Case Not Intersect(Target, Range("K10")) Is Nothing
            MsgBox "K10 click"
        Case Not Intersect(Target, Range("E10")) Is Nothing
            MsgBox "E10 click"
        Case Not Intersect(Target, Range("F10")) Is Nothing
            MsgBox "F10 click"
        Case Not Intersect(Target, Range("G10")) Is Nothing
            MsgBox "G10 click"
        Case Not Intersect(Target, Range("J10")) Is Nothing
            MsgBox "J10 click"
    
    End Select
End Sub

 

[Logit]

Creating a macro that activates upon a single left-click without using the Worksheet_BeforeDoubleClick or Worksheet_SelectionChange events is a bit tricky, since these are the standard ways to trigger actions based on user interactions with cells. However, there's an interesting workaround using forms and ActiveX controls. Here's a step-by-step guide:

1. Create a Button:

o Go to the "Developer" tab in Excel.
o Click "Insert" in the Controls group.
o Select "Button (Form Control)" and draw the button on your sheet.
2. Assign a Macro to the Button:

o Right-click the button and select "Assign Macro".
o Create a new macro or choose an existing one. For example, create a macro named MyMacro:

Sub MyMacro()  
MsgBox "Button clicked!"
End Sub

3. Use ActiveX Control for Cell Click:

o Insert an "ActiveX control" like a command button.
o Go to "Developer" tab > "Insert" > "ActiveX Controls" > "Command Button".
o Draw the button over the cell where you want the action to occur.
o
4. Edit the Command Button Click Event:

o Right-click the button and select "View Code".
o Insert your desired macro code. For example:

Private Sub CommandButton1_Click()
MsgBox "Cell clicked!"
End Sub

Make the Command Button Transparent:

 Right-click the command button, select "Properties".
 Set the BackStyle property to fmBackStyleTransparent.

 This step ensures the cell beneath can be edited:

 You can use a temporary worksheet selection change to monitor edits.
 Alternatively, use a hidden cell to store the value and sync it with the visible cell when clicked.

Here’s how you can sync cell values:

Private Sub CommandButton1_Click()

Dim cell As Range
Dim inputValue As Variant

Set cell = Me.Range("A1") ' The cell beneath the button

inputValue = Application.InputBox("Edit Cell Value", "Edit", cell.Value, Type:=2)

    If inputValue = False Then
        cell.Value = inputValue
    End If
   
End Sub

With this approach, you create an illusion of activating macros with a single click, while still allowing cell content editing. Remember to save the workbook as a macro-enabled file (.xlsm) to retain the functionality.
[/CODE]

 

[ceroberts75]

thank you for that. I know it took some time to do it!

I actually started with inserting activex combobox to use a dropdown from it. Ive done it before to change atributes using a dropdown. But for some reason I was unable to get it to work with all these macros.