"Clickable" cell 2

[malaygal]

I have been struggling on this for quite a while and can not find anything on ggole.

I am trying to make some cells (about 5000) in my excel "clickable", something like a hyperlink but instead of a link, I need it to launch a function (macro). I need to pass the value of the cell to the function and perform a find (search) on another worksheet on the same workbook

Any help will be greatly appreciated.

 

[SkipVought]

Hi,

There are no "clickable" cell events, like buttons.

However, there is a SelectionChange event. So if you select a DIFFERENT cell than the cell that is currently selected (in other words, if A1 is selected, you cannot click on A1 and expect anything to happen) that will fire the SelectionChange event...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   if target.count>1 then exit sub
   if not intersect(target, [YourRangeOfCells]) is nothing then
      YourFunction target.value
   end if
End Sub

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[malaygal]

Skip,

Thank you so much. If you only know how many days I have labored for this (I am quite new with VBA)

 

[SkipVought]

BTW, I failed to mention that that code goes in the SHEET Code Sheet, and not a module.

Right-click the Sheet Tab and select View Code

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[strongm]

There's always the Worksheet's BeforeDoubleClick event, if you are happy to have the users doubleclick to invoke the function. However, this would make the running of the function a user-driven event, which is probably not your intention.

 

[MajP]

Here is an idea using a class module.

Class: ClickSheet

Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer

Private WithEvents mSheet As Worksheet
Private mRange As Excel.Range


Public Property Get ClickSheet() As Worksheet
  Set ClickSheet = mSheet
End Property

Public Property Set ClickSheet(ByVal theSheet As Worksheet)
  Set mSheet = theSheet
  MsgBox mSheet.Name
End Property
Public Property Set ClickRange(ByVal theRange As Excel.Range)
  Set mRange = theRange
End Property

Private Sub mSheet_SelectionChange(ByVal Target As Range)
Dim lngArr As Variant
Dim Item As Variant
If InRange(Target, mRange) Then
   lngArr = Array(vbKeyUp, vbKeyDown, vbKeyLeft, vbKeyRight, vbKeyTab, vbKeyReturn _
   , vbKeyHome, vbKeyEnd, vbKeyPageDown, vbKeyPageUp)
   ' check if any of the navigation keys are pressed
   For Each Item In lngArr
      ' if so, skip the event handler
      If CBool(GetAsyncKeyState(Item) And &H8000) Then
         Exit Sub
      End If
  Next
   MsgBox "You Clicked Cell : " & Target.Address
  'Add code here
End If
End Sub

Function InRange(Range1 As Range, Range2 As Range) As Boolean
' returns True if Range1 is within Range2
Dim InterSectRange As Range
    Set InterSectRange = Application.Intersect(Range1, Range2)
    InRange = Not InterSectRange Is Nothing
    Set InterSectRange = Nothing
End Function

In a standard module instantiate one or more ClickSheets

Public cfSheet As ClickSheet

Public Sub initWS()
 Set cfSheet = New ClickSheet
 Set cfSheet.ClickSheet = Worksheets("Sheet1")
 Set cfSheet.ClickRange = Worksheets("Sheet1").Range("A1:F10")
End Sub

and in the ThisWorkbook

Private Sub Workbook_Open()
  Call initWS
End Sub