How to Run a Macro from clicking a cell

[Romileyrunner1]

Hi,
have a number of macros wrtten that are currently activated by buttons on
top of cells.
Here is one such macro:
Sub Printall()
'
' Printall Macro
' Macro recorded 17/09/2009 by User
'

'
Range("A10:CF53").Select
ActiveSheet.PageSetup.PrintArea = "$A$10:$CF$53"
Range("A1").Select
End Sub

How do I use this to now change to just a cell (double-click) activated macro?
Do I enter the sheet name and cell reference somewhere?
Have read the following from an earlier question but frankly don`t follow
it!!! Sorry>


The following in the Worksheet Selection Change event should do the
trick

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B4")) Is Nothing Then Range("B4")
="24"

End Sub

Thanks
RR1

 

[LesG]

assuming you are going to replace controls (buttons) with lables in cells,
press Alt F11 an insert (and amend) the following in the worksheet...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Value = "value in cell" Then
macroname
End If
End Sub

hope this helps, if so click yes

 

[Romileyrunner1]

Hi, LesG

Can you see what I`m doing wrong? It doesn`t seem to be woking for me!
I`ve typed the following along with my other macros.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Value = "Print KS2" Then
Printks2 Macro
End If
End Sub

Printks2 is the name of the macro I want to use. (should I not put the word
Macro after it?)
I have entered Print KS2 in a blank cell.
When I double click in it, it just wants to edit the typing.
Do I need to do something else to that cell?

Also, because the Macro begins "Private Sub Worksheet..." will others be
able to use this; is it part of this worksheet?

Thanks, LesG

RR1

 

[Dave Peterson]

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

if target.cells.count > 1 then exit sub 'single cell at a time

if lcase(target.value) = lcase("Print KS2") Then
call Printks2
End If

End Sub

You don't need the word "call", but it doesn't hurt. I like it because it's
self-documenting (to me, anyway).

The real problem is that excel doesn't have a single click event. The
worksheet_selectionchange event fires when you select the cell (either by using
the keyboard arrows or single clicking in the cell).

If you want to use double clicking, then you want to use a different event.

Try:

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

If Target.Cells.Count > 1 Then Exit Sub 'single cell at a time

If LCase(Target.Value) = LCase("Print KS2") Then
Cancel 'stop that editing
Call Printks2
End If

End Sub

(remove the worksheet_selection procedure.

 

[Romileyrunner1]

Thanks Dave,
Tried this and still no joy: in fact other macros started to have problems.
You mentioned `double clicking` being a `different event`: quite happy o use
double clicking in a cell to begin the macro if that might work.
Thanks.
RR1

 

[Dave Peterson]

I don't understand. Which version of the code didn't work?

 

[Gord Dibben]

Dave included the doubleclick event code in his post.

Delete the selectionchange event code and replace with the doubleclick code.


Gord Dibben MS Excel MVP

 

[LesG]

Hi RR1,

The "SelectionChange" part of the statement refers to any movemen twhen you
move from the the cuurent cell... The command then works on the ACTIVE cell.
need the word "maro" after .. you do not need the wor macro after Printks2...
to be 100% correct you could say :

If ActiveCell.Value = "Print KS2" Then
call Printks2
End If


hope this helps..

Regards

Les

 

[LesG]

sorry my keyboard (and the fact that I have been watching rugby) are not
conducive to coherent sentences... the remarks bellow sould have been...

The "SelectionChange" part of the statement refers to any movement when you
move from the the cuurent cell in a workbook... The command then works on
the ACTIVECELL statement.

You MUDT not use the word "maro" after Printks2...

To be 100% correct you could say :
If ActiveCell.Value = "Print KS2" Then
call Printks2
End If

 

[Dave Peterson]

Actually, I wouldn't use Activecell.

I'd use the variable that was passed to the procedure (Target). And I'd make
sure that the case didn't matter:


if lcase(Target.value) = lcase("print ks2") then

 

[LesG]

Hi Dave... this is quite handy. Am I correct in assuming that the
"lcase(Target.value)" forces the target value to be lowercase?

 

[Dave Peterson]

exactly.

Then "print ks2" will be treated the same as "PrinT KS2" or "PRINT ks2" or ...