Double-click to run macro?
 

Greetings,

I'd like to create some code which would allow a user to double-click a cell
to run a macro. To make it easy, there's only 1 macro in the workbook and I
want the user to double-click cell A1.

I'm relatively new to VBA, and I'm having trouble figuring out at which
level I need to put this double-click code (in addition to how to make it in
the first place). Any suggestions? Thanks in advance!

Double-click to run macro?
 

How about adding a button from the Forms toolbar so that it sits over A1.

Then rightclick on that button and choose assign macro.

I think people would find a button easier to deal with--Hey, it's a button. I'm
gonna click it!

JAnderson wrote:

Greetings,

I'd like to create some code which would allow a user to double-click a cell
to run a macro. To make it easy, there's only 1 macro in the workbook and I
want the user to double-click cell A1.

I'm relatively new to VBA, and I'm having trouble figuring out at which
level I need to put this double-click code (in addition to how to make it in
the first place). Any suggestions? Thanks in advance!

--

Dave Peterson

Double-click to run macro?
 

J,

I think Dave's idea of using a button might be better than using a cell, except users would
click, not double-click it. This would be more familiar to users. You can assign a macro
to any graphic you've put on the worksheet (right-click it, and click "assign macro"), and
there's a button in Autoshapes (in "Basic shapes") on the Drawing Toolbar that would be a
good choice. You can put text in it, color it, and have a great time.

If you still want to use a cell, put this in the sheet module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True ' prevent double-click from causing Edit Mode in cell
MyMacro
End Sub

Or you could put the code directly in the sub above, if it's the only place from where it'll
be called.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Dave Peterson" wrote in message
...
How about adding a button from the Forms toolbar so that it sits over A1.

Then rightclick on that button and choose assign macro.

I think people would find a button easier to deal with--Hey, it's a button. I'm
gonna click it!

JAnderson wrote:

Greetings,

I'd like to create some code which would allow a user to double-click a cell
to run a macro. To make it easy, there's only 1 macro in the workbook and I
want the user to double-click cell A1.

I'm relatively new to VBA, and I'm having trouble figuring out at which
level I need to put this double-click code (in addition to how to make it in
the first place). Any suggestions? Thanks in advance!

--

Dave Peterson

Double-click to run macro?
 

I like the button idea- I never even thought of it. I used the Control
Toolbox to insert a Command Button, but when I right-click it, I don't get
the "Assign Macro" option. Interestingly, I get the "Assign Macro" option if
I draw a shape from the Drawing toolbar. None of the AutoShapes options
gives me a button.

Anyway, the reason I didn't want a button on the spreadsheet is because once
the macro is run, the report is shared both electronically and in print
version with other users.

With your code, if I used it for the double-click to link to the macro, how
do I link it to a specific cell? For example, I only want a user to
double-click in cell A1 to run the macro, but be able to edit every other
cell in the sheet.

Thanks for all your help!

"Earl Kiosterud" wrote:

J,

I think Dave's idea of using a button might be better than using a cell, except users would
click, not double-click it. This would be more familiar to users. You can assign a macro
to any graphic you've put on the worksheet (right-click it, and click "assign macro"), and
there's a button in Autoshapes (in "Basic shapes") on the Drawing Toolbar that would be a
good choice. You can put text in it, color it, and have a great time.

If you still want to use a cell, put this in the sheet module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True ' prevent double-click from causing Edit Mode in cell
MyMacro
End Sub

Or you could put the code directly in the sub above, if it's the only place from where it'll
be called.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Dave Peterson" wrote in message
...
How about adding a button from the Forms toolbar so that it sits over A1.

Then rightclick on that button and choose assign macro.

I think people would find a button easier to deal with--Hey, it's a button. I'm
gonna click it!

JAnderson wrote:

Greetings,

I'd like to create some code which would allow a user to double-click a cell
to run a macro. To make it easy, there's only 1 macro in the workbook and I
want the user to double-click cell A1.

I'm relatively new to VBA, and I'm having trouble figuring out at which
level I need to put this double-click code (in addition to how to make it in
the first place). Any suggestions? Thanks in advance!

--

Dave Peterson

Double-click to run macro?
 

If you used a button from the Forms toolbar, you would have seen that "assign
macro" option.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)

if intersect(target,me.range("a1")) is nothing then
'do nothing special
else
Cancel = True ' prevent double-click from causing Edit Mode in cell
MyMacro
end if
End Sub

If you right click on the button, you can choose "Format control", then
"Properties" and uncheck the print object box. This'll stop the button from
being printed.



JAnderson wrote:

I like the button idea- I never even thought of it. I used the Control
Toolbox to insert a Command Button, but when I right-click it, I don't get
the "Assign Macro" option. Interestingly, I get the "Assign Macro" option if
I draw a shape from the Drawing toolbar. None of the AutoShapes options
gives me a button.

Anyway, the reason I didn't want a button on the spreadsheet is because once
the macro is run, the report is shared both electronically and in print
version with other users.

With your code, if I used it for the double-click to link to the macro, how
do I link it to a specific cell? For example, I only want a user to
double-click in cell A1 to run the macro, but be able to edit every other
cell in the sheet.

Thanks for all your help!

"Earl Kiosterud" wrote:

J,

I think Dave's idea of using a button might be better than using a cell, except users would
click, not double-click it. This would be more familiar to users. You can assign a macro
to any graphic you've put on the worksheet (right-click it, and click "assign macro"), and
there's a button in Autoshapes (in "Basic shapes") on the Drawing Toolbar that would be a
good choice. You can put text in it, color it, and have a great time.

If you still want to use a cell, put this in the sheet module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True ' prevent double-click from causing Edit Mode in cell
MyMacro
End Sub

Or you could put the code directly in the sub above, if it's the only place from where it'll
be called.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Dave Peterson" wrote in message
...
How about adding a button from the Forms toolbar so that it sits over A1.

Then rightclick on that button and choose assign macro.

I think people would find a button easier to deal with--Hey, it's a button. I'm
gonna click it!

JAnderson wrote:

Greetings,

I'd like to create some code which would allow a user to double-click a cell
to run a macro. To make it easy, there's only 1 macro in the workbook and I
want the user to double-click cell A1.

I'm relatively new to VBA, and I'm having trouble figuring out at which
level I need to put this double-click code (in addition to how to make it in
the first place). Any suggestions? Thanks in advance!

--

Dave Peterson




--

Dave Peterson

Double-click to run macro?
 

That's about as thorough as it gets- thank you both for all of your help!


"Dave Peterson" wrote:

If you used a button from the Forms toolbar, you would have seen that "assign
macro" option.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)

if intersect(target,me.range("a1")) is nothing then
'do nothing special
else
Cancel = True ' prevent double-click from causing Edit Mode in cell
MyMacro
end if
End Sub

If you right click on the button, you can choose "Format control", then
"Properties" and uncheck the print object box. This'll stop the button from
being printed.



JAnderson wrote:

I like the button idea- I never even thought of it. I used the Control
Toolbox to insert a Command Button, but when I right-click it, I don't get
the "Assign Macro" option. Interestingly, I get the "Assign Macro" option if
I draw a shape from the Drawing toolbar. None of the AutoShapes options
gives me a button.

Anyway, the reason I didn't want a button on the spreadsheet is because once
the macro is run, the report is shared both electronically and in print
version with other users.

With your code, if I used it for the double-click to link to the macro, how
do I link it to a specific cell? For example, I only want a user to
double-click in cell A1 to run the macro, but be able to edit every other
cell in the sheet.

Thanks for all your help!

"Earl Kiosterud" wrote:

J,

I think Dave's idea of using a button might be better than using a cell, except users would
click, not double-click it. This would be more familiar to users. You can assign a macro
to any graphic you've put on the worksheet (right-click it, and click "assign macro"), and
there's a button in Autoshapes (in "Basic shapes") on the Drawing Toolbar that would be a
good choice. You can put text in it, color it, and have a great time.

If you still want to use a cell, put this in the sheet module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True ' prevent double-click from causing Edit Mode in cell
MyMacro
End Sub

Or you could put the code directly in the sub above, if it's the only place from where it'll
be called.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Dave Peterson" wrote in message
...
How about adding a button from the Forms toolbar so that it sits over A1.

Then rightclick on that button and choose assign macro.

I think people would find a button easier to deal with--Hey, it's a button. I'm
gonna click it!

JAnderson wrote:

Greetings,

I'd like to create some code which would allow a user to double-click a cell
to run a macro. To make it easy, there's only 1 macro in the workbook and I
want the user to double-click cell A1.

I'm relatively new to VBA, and I'm having trouble figuring out at which
level I need to put this double-click code (in addition to how to make it in
the first place). Any suggestions? Thanks in advance!

--

Dave Peterson




--

Dave Peterson

Double-click to run macro?
 

J,

Oops. I left out the part about having it respond only to one cell. Dave's taken care of
that. Consider that users aren't used to clicking or double-clicking a cell to get an
action.

The button I referred to is actually called "Bevel." It looks pretty much like a button.
You can put text in it, and format the text. You can set it up to print or not print by
right-clicking it, Format Autoshape - Properties tab. You can even change its color in the
macro to make it appear it's a lighted button. Then turn if off later. Very cool.

By way of follow-up on using a control from the Controls Toolbox, you double-click it, and
it puts you in the "code behind the sheet" module for the sheet the control is in.
Something like:

Private Sub CommandButton1_Click()
' your code here
End Sub

Controls work differently from forms objects.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"JAnderson" wrote in message
...
I like the button idea- I never even thought of it. I used the Control
Toolbox to insert a Command Button, but when I right-click it, I don't get
the "Assign Macro" option. Interestingly, I get the "Assign Macro" option if
I draw a shape from the Drawing toolbar. None of the AutoShapes options
gives me a button.

Anyway, the reason I didn't want a button on the spreadsheet is because once
the macro is run, the report is shared both electronically and in print
version with other users.

With your code, if I used it for the double-click to link to the macro, how
do I link it to a specific cell? For example, I only want a user to
double-click in cell A1 to run the macro, but be able to edit every other
cell in the sheet.

Thanks for all your help!

"Earl Kiosterud" wrote:

J,

I think Dave's idea of using a button might be better than using a cell, except users
would
click, not double-click it. This would be more familiar to users. You can assign a
macro
to any graphic you've put on the worksheet (right-click it, and click "assign macro"),
and
there's a button in Autoshapes (in "Basic shapes") on the Drawing Toolbar that would be a
good choice. You can put text in it, color it, and have a great time.

If you still want to use a cell, put this in the sheet module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True ' prevent double-click from causing Edit Mode in cell
MyMacro
End Sub

Or you could put the code directly in the sub above, if it's the only place from where
it'll
be called.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Dave Peterson" wrote in message
...
How about adding a button from the Forms toolbar so that it sits over A1.

Then rightclick on that button and choose assign macro.

I think people would find a button easier to deal with--Hey, it's a button. I'm
gonna click it!

JAnderson wrote:

Greetings,

I'd like to create some code which would allow a user to double-click a cell
to run a macro. To make it easy, there's only 1 macro in the workbook and I
want the user to double-click cell A1.

I'm relatively new to VBA, and I'm having trouble figuring out at which
level I need to put this double-click code (in addition to how to make it in
the first place). Any suggestions? Thanks in advance!

--

Dave Peterson