Way to execute vba code out of a cell w a double.click

YesConsulting

New Member
Joined
Jan 7, 2019
Messages
11
Office Version
365
Platform
Windows, MacOS
In Access & VB a field has the functions/methods where you can execute code subroutine on double.click.

I see how to call a subroutine from a button...

It seems there should be a call like Range.ondblclick
 

Some videos you may like

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.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
If this something like you want:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

When you double click on any cell in column(A) the below script will run.
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  1/7/2019  9:11:12 PM  EST
Cancel = True
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Target.Value = "Mr. Excel"
Target.Offset(, 1).Value = Date
End If
End Sub
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.
Thanks a bunch!
Its easy when you see it....
 

YesConsulting

New Member
Joined
Jan 7, 2019
Messages
11
Office Version
365
Platform
Windows, MacOS
Thanks for the code & example.

It is so easy when you know how to do it...and impossible when you do not.
I looked all over trying to find out what called the method...thinking it needed to be tied somehow from the UI.

Your example is great... showing how to limit the effect of dbl click to a set of cells.

I suspect that if you wanted different cells to have dbl.click functionality then they all go into this routine
have to use the intersect to isolate the cell.
I'll test it.

I wonder if you can use a case function?

Anyway... you can tell I am just beginning to look at the excel vba model

thx
 

YesConsulting

New Member
Joined
Jan 7, 2019
Messages
11
Office Version
365
Platform
Windows, MacOS
Hi... your avatar is applicable!

I have looked all over the faq's and board and cant find this question/answer... I hope I do not stress the thread... I didn’t know where to put "forum" based questions.

I have had the site time out a few times... and a few times it has stranded a post I was writing.
The site gives no indication that it is about to time out the logon.. everything looks the same.. But when go to post, have to log on again, and the post got lost.
I finally smartened up..
I thought there may be a timeout setting, but I did not find one..

Is there one?
What is the timeout time?

thx.
Michael
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
I seldom run into a time out problem on any forum. If you need a long response, write in Word or such and then copy and paste.

Look for Intersect() for other examples. Yes, Select Case True, or such is easy way to do things based on a Case...
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
If you want to use double click with case.

I need a example of what you want.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
Here is two example using case and specific cell.

Range("A1") and Range("G4")

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  1/7/2019  10:29:30 PM  EST
Cancel = True
If Target.Address = "$A$1" Then
    Select Case Target.Value
        Case "John": Target.Offset(, 1).Value = "Doe"
        Case "Jane": Target.Offset(, 1).Value = "Smith"
    End Select
End If
If Target.Address = "$G$4" Then
    Select Case Target.Value
        Case "John": Target.Offset(, 1).Value = "Doe"
        Case "Jane": Target.Offset(, 1).Value = "Smith"
    End Select
End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,099,510
Messages
5,469,042
Members
406,631
Latest member
hotspot1972

This Week's Hot Topics

Top