Run macro by double-clicking a cell?

djsmarties

Board Regular
Joined
Sep 10, 2002
Messages
95
Hi, i would like to run a macro that shows a pop-up list by double clicking on the specific cell.
so far i have a button to start the macro, but thats really uncomfortable.
Basically i would like to double click a cell and let the pop-up list appear.
Ideas anyone?
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
On 2002-09-23 19:04, djsmarties wrote:
Hi, i would like to run a macro that shows a pop-up list by double clicking on the specific cell.
so far i have a button to start the macro, but thats really uncomfortable.
Basically i would like to double click a cell and let the pop-up list appear.
Ideas anyone?

Excel2000

<pre>
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$B$10" Then
MsgBox "running your macro"
'// run your macro here
End If
End Sub
</pre>
<META content=Excel.Sheet name=ProgId><META content="Microsoft Excel 9" name=Generator><LINK href="./sht_files/filelist.xml" rel=File-List><STYLE id=AAA_Instructions_VBAEnter_25139_Styles></STYLE>  <DIV id=AAA_Instructions_VBAEnter_25139 align=center x:publishsource="Excel"><TABLE style="TABLE-LAYOUT: fixed; WIDTH: 458pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=610 border=0 x:str><COLGROUP><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 320pt; mso-width-source: userset; mso-width-alt: 15616" width=427><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 914" width=25><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl2425139 style="WIDTH: 23pt; HEIGHT: 13.5pt" width=30 height=18> </TD><TD class=xl2425139 style="WIDTH: 48pt" width=64> </TD><TD class=xl2425139 style="WIDTH: 320pt" width=427> </TD><TD class=xl2425139 style="WIDTH: 48pt" width=64> </TD><TD class=xl2425139 style="WIDTH: 19pt" width=25> </TD></TR><TR style="HEIGHT: 24.75pt" height=33><TD class=xl2425139 style="HEIGHT: 24.75pt" height=33> </TD><TD class=xl4125139 style="BORDER-RIGHT: white 2pt double" colSpan=3>WorkSheet_BeforeDoubleClick</TD><TD class=xl2425139> </TD></TR><TR style="HEIGHT: 9.75pt; mso-height-source: userset" height=13><TD class=xl2425139 style="HEIGHT: 9.75pt" height=13> </TD><TD class=xl3325139> </TD><TD class=xl3325139> </TD><TD class=xl3325139> </TD><TD class=xl2425139> </TD></TR><TR style="HEIGHT: 24.75pt" height=33><TD class=xl2425139 style="HEIGHT: 24.75pt" height=33> </TD><TD class=xl3625139>STEP</TD><TD class=xl3725139><SPAN style="mso-spacerun: yes"> </SPAN>'<FONT class=font825139> </FONT><FONT class=font725139>HOW<SPAN style="mso-spacerun: yes">  </SPAN></FONT><FONT class=font925139>J</FONT></TD><TD class=xl4525139>OK</TD><TD class=xl2425139> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl2425139 style="HEIGHT: 15.75pt" height=21> </TD><TD class=xl3825139 x:num>1</TD><TD class=xl3525139 style="BORDER-LEFT: medium none; WIDTH: 320pt" width=427><SPAN style="mso-spacerun: yes"> </SPAN>Press<FONT class=font625139> Alt-F11</FONT><FONT class=font525139> to display the Visual Basic editor (VBE).</FONT></TD><TD class=xl4425139>þ</TD><TD class=xl2425139> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl2425139 style="HEIGHT: 15pt" height=20> </TD><TD class=xl2525139><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2525139><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl3925139> </TD><TD class=xl2425139> </TD></TR><TR style="HEIGHT: 51pt" height=68><TD class=xl2425139 style="HEIGHT: 51pt" height=68> </TD><TD class=xl3825139 x:num>2</TD><TD class=xl2925139 style="BORDER-LEFT: medium none; WIDTH: 320pt" width=427><SPAN style="mso-spacerun: yes"> </SPAN>In the<FONT class=font625139> Project Explorer window</FONT><FONT class=font525139>, find an entry for the sheet you're working with. (For example, look for Sheet1 if the cells you're working on are on that sheet.) If you don't see the </FONT><FONT class=font625139>Project Explorer window</FONT><FONT class=font525139>, press </FONT><FONT class=font625139>Ctrl-R</FONT><FONT class=font525139>.</FONT></TD><TD class=xl4025139>þ</TD><TD class=xl2425139> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl2425139 style="HEIGHT: 15pt" height=20> </TD><TD class=xl2525139><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2525139><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl3925139> </TD><TD class=xl2425139> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl2425139 style="HEIGHT: 15pt" height=20> </TD><TD class=xl3825139 x:num>3</TD><TD class=xl3025139 style="BORDER-LEFT: medium none; WIDTH: 320pt" width=427><SPAN style="mso-spacerun: yes"> </SPAN>Double-click<FONT class=font525139> the sheet entry in the </FONT><FONT class=font625139>Project Explorer window</FONT><FONT class=font525139>.</FONT></TD><TD class=xl4025139>þ</TD><TD class=xl2425139> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl2425139 style="HEIGHT: 15pt" height=20> </TD><TD class=xl2525139><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2525139><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl3925139> </TD><TD class=xl2425139> </TD></TR><TR style="HEIGHT: 38.25pt" height=51><TD class=xl2425139 style="HEIGHT: 38.25pt" height=51> </TD><TD class=xl3825139 x:num>4</TD><TD class=xl2925139 style="BORDER-LEFT: medium none; WIDTH: 320pt" width=427><SPAN style="mso-spacerun: yes"> </SPAN>Above the code window, you'll find<FONT class=font625139> two drop-down lists</FONT><FONT class=font525139>. In the list on the left, choose </FONT><FONT class=font625139>Worksheet</FONT><FONT class=font525139>. In the list on the right, choose </FONT><FONT class=font625139>BeforeDoubleClick</FONT><FONT class=font525139>.</FONT></TD><TD class=xl4025139>þ</TD><TD class=xl2425139> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl2425139 style="HEIGHT: 15pt" height=20> </TD><TD class=xl2525139><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2525139><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl3925139> </TD><TD class=xl2425139> </TD></TR><TR style="HEIGHT: 38.25pt" height=51><TD class=xl2425139 style="HEIGHT: 38.25pt" height=51> </TD><TD class=xl3825139 x:num>5</TD><TD class=xl2925139 style="BORDER-LEFT: medium none; WIDTH: 320pt" width=427><SPAN style="mso-spacerun: yes"> </SPAN>A procedure template for <FONT class=font625139>WorkSheet_BeforeDoubleClick</FONT><FONT class=font525139> now appears in the code window. Between the </FONT><FONT class=font625139>Private Sub</FONT><FONT class=font525139> line and the </FONT><FONT class=font625139>End Sub </FONT><FONT class=font525139>line, type in your procedure.</FONT></TD><TD class=xl4025139>þ</TD><TD class=xl2425139> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl2425139 style="HEIGHT: 15pt" height=20> </TD><TD class=xl2525139><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2525139><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl3925139> </TD><TD class=xl2425139> </TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl2425139 style="HEIGHT: 25.5pt" height=34> </TD><TD class=xl3825139>eg.</TD><TD class=xl2825139 style="WIDTH: 320pt" width=427>For example your <FONT class=font625139>WorkSheet_BeforeDoubleClick</FONT><FONT class=font525139> procedure would look like this:</FONT></TD><TD class=xl4025139 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2425139> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl2425139 style="HEIGHT: 15pt" height=20> </TD><TD class=xl2525139><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2525139><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl3925139> </TD><TD class=xl2425139> </TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl2425139 style="HEIGHT: 25.5pt" height=34> </TD><TD class=xl3825139>Code:</TD><TD class=xl3125139 style="WIDTH: 320pt" width=427>Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)</TD><TD class=xl4025139 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2425139> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2425139 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2325139> </TD><TD class=xl3125139 style="WIDTH: 320pt" width=427><SPAN style="mso-spacerun: yes">    </SPAN>If Target.Address = "$B$10" Then</TD><TD class=xl2225139> </TD><TD class=xl2425139> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2425139 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2725139><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2825139 style="WIDTH: 320pt" width=427><SPAN style="mso-spacerun: yes">   </SPAN>MsgBox "running your macro"</TD><TD class=xl2625139> </TD><TD class=xl2425139> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2425139 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2325139> </TD><TD class=xl3125139 style="WIDTH: 320pt" width=427><SPAN style="mso-spacerun: yes">   </SPAN>'// run your macro here</TD><TD class=xl2225139> </TD><TD class=xl2425139> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2425139 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2325139> </TD><TD class=xl3125139 style="WIDTH: 320pt" width=427><SPAN style="mso-spacerun: yes">  </SPAN>End If</TD><TD class=xl2225139> </TD><TD class=xl2425139> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2425139 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2725139><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl3225139 style="WIDTH: 320pt" width=427>End Sub</TD><TD class=xl2625139> </TD><TD class=xl2425139> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl2425139 style="HEIGHT: 13.5pt" height=18> </TD><TD class=xl2425139> </TD><TD class=xl2425139> </TD><TD class=xl2425139> </TD><TD class=xl2425139> </TD></TR><TR style="HEIGHT: 30.75pt" height=41><TD class=xl2425139 style="HEIGHT: 30.75pt" height=41> </TD><TD class=xl2425139> </TD><TD class=xl3425139 style="WIDTH: 320pt" width=427><SUP>µ</SUP><FONT class=font1125139> HTH - Ivan F Moala </FONT><FONT class=font1025139><SUP>µ</SUP></FONT></TD><TD class=xl2425139> </TD><TD class=xl2425139> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl2425139 style="HEIGHT: 13.5pt" height=18> </TD><TD class=xl2425139> </TD><TD class=xl2425139> </TD><TD class=xl2425139> </TD><TD class=xl2425139> </TD></TR></TBODY></TABLE></DIV>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,964
Members
413,955
Latest member
FalcoDaz

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
Top