'Event' macro

  • Thread starter Thread starter George Gee
  • Start date Start date
G

George Gee

Hi all

Is it possible to run a macro by entering data in a cell, and pressing
Enter?

Many thanks

George
 
It is

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
'do your stuff
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Many thanks.

This may not be what I am looking for!
I have 38 cells that I will be entering a football result into (one each
week).
Range (N3:N40).
I have 38 different macros already written, to assign.

I could assign them to 38 buttons or graphics, but do not fancy making and
assigning 38 of them!
I thought maybe a particular macro could be assigned to a cell
and could be run by entering the football score into the cell.

Any comment
Thanks again.

George
 
Private Sub Worksheet_Change(ByVal Target As Range)

if target.address = "$A$1"
call macro1
end if
End Sub

is a simple example. Use the Select Case for all 38 conditions.

Mangesh
 
Mangeshh

Thanks for that.

I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Target.Address = "$N$3" Then
Call AstonAway
End If
End Sub

However, as soon as I select the cell the macro runs.
I need to be able to select the cell, input the data,
*then* run the macro.

Am I doing something wrong?

George Gee
 
Update

I have thought of a workaround.

I have made the target cell, the cell to the right of the one
in which I am inputting the data, input the data, and press 'Enter'
this selects the cell to the right, and runs the macro.

Unless there is a better solution, I will stick with this.

Thanks for your help so far.

George Gee
 
Use the change event as I showed, then it only runs when you change the
data, as you originally asked.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Thanks for coming back.

I would gladly use the code, if I could understand it!

What do I change (if anything) to make this work?
How do I assign 1 out of 38 different macros to a particular cell?

I can record macros easily enough, but I am new to VBA!


George Gee
 
Here is a starter

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: Call Macro1
Case 2: Call Macro2
Case 3: Call Macro3
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--

HTH

RP
(remove nothere from the email address if mailing direct)
 
I'm sorry, but I think I'm wasting your time.

If you could explain what is supposed to happen.

What does the Range "A1:H10" signify?

Do I need to change it, to suit my requirements?



George Gee
 
You are not wasting my time, I can stop any time I want <g>

Your original question was "Is it possible to run a macro by entering data
in a cell, and pressing Enter?" The code I gave does that, and tests for
data being changed in A1:H10. You can change that to the cells that you want
to 'watch'. It then tests the value that has just been entered and runs a
different macro accordingly.

Unfortunately, one of the problems we have is not knowing the poster's level
of experience, judging the answer can be difficult ;-)

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
OK, it looks like I'm getting there!
I have to refine the macros a little,
I will have a play with this, and see what happens.

Sincere thanks for your patience.

George Gee
 
No problem.

I suggest you start a new thread if you have any further problems, it will
probably get picked up more readily.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Sorry,

should be

Worksheet_Change


Mangesh




George Gee said:
Mangeshh

Thanks for that.

I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Target.Address = "$N$3" Then
Call AstonAway
End If
End Sub

However, as soon as I select the cell the macro runs.
I need to be able to select the cell, input the data,
*then* run the macro.

Am I doing something wrong?

George Gee
Click to expand...
 
A simple way for you to understand would be (after modifying Bob's code a
bit):
Replaced his with statement with the following:

Here You check the target address (or the cell you have edited), and
accordingly runs the macro.


Private Sub Worksheet_Change(ByVal Target As Range)

Const WS_RANGE As String = "A1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target
Select Case .Address
Case "$A$1": Call macro1
Case "$A$2": Call macro2
Case "$A$3": Call macro3
'etc.
End Select
End With

End If

ws_exit:
Application.EnableEvents = True
End Sub


Mangesh
 
Mangesh and Bob

Thanks for your continuing help.
I am starting to understand bits of this,
I have thus far: (just using 3 of the 38 macros)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Const WS_RANGE As String = "N3:N40"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target
Select Case .Address
Case "$N$3": Call AstonAway
Case "$N$4": Call EverHome
Case "$N$5": Call NewcasHome
'etc.
End Select
End With

End If

ws_exit:
Application.EnableEvents = True
End Sub

But!!!!!

As soon as I try to select say "N3", the macro runs.
This is not what I want.
I have had to change the last action of the macro
to select "N3", or I would not even be able to select it!

Here is a typical macro:

Sub AstonAway()
'
' AstonAway Macro
' Macro recorded 30/07/2004 by George Gee
'

'
Range("E5").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]="""","""",HLOOKUP(RC[-2],'Points
Gained'!R1C5:R40C30,3,FALSE))"
Selection.Copy
Range("E6:E30").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("E5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("AB1:AE1").Select
Selection.Copy
Range("C37:F37").PasteSpecial xlPasteValues
Range("C5:G28").Select
Selection.Sort Key1:=Range("D5"), Order1:=xlDescending,
Key2:=Range("G5") _
, Order2:=xlAscending, Header:=xlNo, OrderCustom:=1,
MatchCase:=False, _
Orientation:=xlTopToBottom
Range("N3").Select
End Sub


What is now happening, is that on pressing 'Enter'
The selection moves down one cell, to "N4",
and runs the macro associated with *that* 'Case' !!!

To sum up:
I want to select cell "N3", enter a score, *then* run
the macro associated with that cell.

Regards: George Gee <g>
 
Don't use

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Use

Private Sub Worksheet_Change(ByVal Target As Range)

as the first line

Mangesh
 
Thanks Mangesh!

I can see it now!
Although, where *Worksheet_SelectionChange* came from,
I have no idea.

All code has been copied from these posts, (strange).

Sincere appologies for being so dense, and many thanks for
your perseverance.

Thanks Bob

George Gee




Mangesh said:
Don't use

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Use

Private Sub Worksheet_Change(ByVal Target As Range)

as the first line

Mangesh




George Gee said:
Mangesh and Bob

Thanks for your continuing help.
I am starting to understand bits of this,
I have thus far: (just using 3 of the 38 macros)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Const WS_RANGE As String = "N3:N40"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target
Select Case .Address
Case "$N$3": Call AstonAway
Case "$N$4": Call EverHome
Case "$N$5": Call NewcasHome
'etc.
End Select
End With

End If

ws_exit:
Application.EnableEvents = True
End Sub

But!!!!!

As soon as I try to select say "N3", the macro runs.
This is not what I want.
I have had to change the last action of the macro
to select "N3", or I would not even be able to select it!

Here is a typical macro:

Sub AstonAway()
'
' AstonAway Macro
' Macro recorded 30/07/2004 by George Gee
'

'
Range("E5").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]="""","""",HLOOKUP(RC[-2],'Points
Gained'!R1C5:R40C30,3,FALSE))"
Selection.Copy
Range("E6:E30").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("E5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("AB1:AE1").Select
Selection.Copy
Range("C37:F37").PasteSpecial xlPasteValues
Range("C5:G28").Select
Selection.Sort Key1:=Range("D5"), Order1:=xlDescending,
Key2:=Range("G5") _
, Order2:=xlAscending, Header:=xlNo, OrderCustom:=1,
MatchCase:=False, _
Orientation:=xlTopToBottom
Range("N3").Select
End Sub


What is now happening, is that on pressing 'Enter'
The selection moves down one cell, to "N4",
and runs the macro associated with *that* 'Case' !!!

To sum up:
I want to select cell "N3", enter a score, *then* run
the macro associated with that cell.

Regards: George Gee <g>
Click to expand...
Click to expand...
 
It came from Mangesh's first post, he accidentally used SelectionChange, and
he corrected it, but that was in another arm of the thread, so you probably
missed it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


George Gee said:
Thanks Mangesh!

I can see it now!
Although, where *Worksheet_SelectionChange* came from,
I have no idea.

All code has been copied from these posts, (strange).

Sincere appologies for being so dense, and many thanks for
your perseverance.

Thanks Bob

George Gee




Mangesh said:
Don't use

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Use

Private Sub Worksheet_Change(ByVal Target As Range)

as the first line

Mangesh




George Gee said:
Mangesh and Bob

Thanks for your continuing help.
I am starting to understand bits of this,
I have thus far: (just using 3 of the 38 macros)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Const WS_RANGE As String = "N3:N40"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target
Select Case .Address
Case "$N$3": Call AstonAway
Case "$N$4": Call EverHome
Case "$N$5": Call NewcasHome
'etc.
End Select
End With

End If

ws_exit:
Application.EnableEvents = True
End Sub

But!!!!!

As soon as I try to select say "N3", the macro runs.
This is not what I want.
I have had to change the last action of the macro
to select "N3", or I would not even be able to select it!

Here is a typical macro:

Sub AstonAway()
'
' AstonAway Macro
' Macro recorded 30/07/2004 by George Gee
'

'
Range("E5").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]="""","""",HLOOKUP(RC[-2],'Points
Gained'!R1C5:R40C30,3,FALSE))"
Selection.Copy
Range("E6:E30").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("E5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("AB1:AE1").Select
Selection.Copy
Range("C37:F37").PasteSpecial xlPasteValues
Range("C5:G28").Select
Selection.Sort Key1:=Range("D5"), Order1:=xlDescending,
Key2:=Range("G5") _
, Order2:=xlAscending, Header:=xlNo, OrderCustom:=1,
MatchCase:=False, _
Orientation:=xlTopToBottom
Range("N3").Select
End Sub


What is now happening, is that on pressing 'Enter'
The selection moves down one cell, to "N4",
and runs the macro associated with *that* 'Case' !!!

To sum up:
I want to select cell "N3", enter a score, *then* run
the macro associated with that cell.

Regards: George Gee <g>
Click to expand...
Click to expand...
Click to expand...
 
Back
Top