activate code on single click

  • Thread starter Thread starter Patrick Molloy
  • Start date Start date
P

Patrick Molloy

you can't single click - this would be the same as selecting the cell, so
you can use the SelectionChange method
 
Single click? No. But if you are willing to use a double click (which I
think is more natural and it would avoid accidentally changing a value when
the user clicks into a cell by mistake)...

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
If InStr(1, "*Yes*No*", "*" & Target.Value & "*", vbTextCompare) Then
Cancel = True
Target.Value = Mid("YesNo", 1 - 3 * (Len(Target.Value) = 3), 3)
End If
End Sub

You can add code to filter this for specific ranges if that is what you
need.
 
Yes. By using a hyperlink and a hyperlink event macro.

First select A1 and:
Insert > Hyperlink... > to a place in this document >
then pick cell A1 and give it the name "no"

This looks pretty useless. We have created a hyperlink that goes nowhere!

However, then insert the following event macro in the worksheet code area:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
For Each h In ActiveSheet.Hyperlinks
If h.SubAddress = "Sheet1!A1" Then
h.TextToDisplay = "yes"
End If
Next
End Sub


Now if we click on A1, the macro fires and changes the visible text to "yes"

Without the hyperlink, we would have to rely on a double-click event.
 
clever.

Gary''s Student said:
Yes. By using a hyperlink and a hyperlink event macro.

First select A1 and:
Insert > Hyperlink... > to a place in this document >
then pick cell A1 and give it the name "no"

This looks pretty useless. We have created a hyperlink that goes nowhere!

However, then insert the following event macro in the worksheet code area:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
For Each h In ActiveSheet.Hyperlinks
If h.SubAddress = "Sheet1!A1" Then
h.TextToDisplay = "yes"
End If
Next
End Sub


Now if we click on A1, the macro fires and changes the visible text to
"yes"

Without the hyperlink, we would have to rely on a double-click event.
Click to expand...
 
The Target object is the Hyperlink object that was clicked on, so this can
be further simplified by using the code shown below.

Also, if you plan on using this with multiple links, then use Select Case
statements in place of the If, Then, Else statements.

Example:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim l_strSheetName As String
l_strSheetName = Target.Parent.Name
Select Case Target.SubAddress
Case "A1"
Target.TextToDisplay = "yes"
End Select
End Sub


--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
 
Is there a way to activate code on a "Single" click in the activecell /
selected cell on sheet.

i have "no" in cell "a1" and it is selected. i want it to change to "yes" if
i single clik on "a1"

Probably not possible hey?

Sunil
 
Back
Top