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)


"George Gee" wrote in message
...
Hi all

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

Many thanks

George

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







Bob Phillips wrote:
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.





"George Gee" wrote in message
...
Hi all

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

Many thanks

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



"George Gee" wrote in message
...
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







Bob Phillips wrote:
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.





"George Gee" wrote in message
...
Hi all

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

Many thanks

George

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


Mangesh Yadav wrote:
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



"George Gee" wrote in message
...
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







Bob Phillips wrote:
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.





"George Gee" wrote in message
...
Hi all

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

Many thanks

George

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


George Gee wrote:
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


Mangesh Yadav wrote:
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



"George Gee" wrote in message
...
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







Bob Phillips wrote:
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.





"George Gee" wrote in message
...
Hi all

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

Many thanks

George

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)


"George Gee" wrote in message
...
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


George Gee wrote:
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


Mangesh Yadav wrote:
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



"George Gee" wrote in message
...
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







Bob Phillips wrote:
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.





"George Gee" wrote in message
...
Hi all

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

Many thanks

George

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


Bob Phillips wrote:
Use the change event as I showed, then it only runs when you change
the data, as you originally asked.


"George Gee" wrote in message
...
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


George Gee wrote:
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


Mangesh Yadav wrote:
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



"George Gee" wrote in message
...
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







Bob Phillips wrote:
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.





"George Gee" wrote in message
...
Hi all

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

Many thanks

George

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)


"George Gee" wrote in message
...
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


Bob Phillips wrote:
Use the change event as I showed, then it only runs when you change
the data, as you originally asked.


"George Gee" wrote in message
...
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


George Gee wrote:
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


Mangesh Yadav wrote:
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



"George Gee" wrote in message
...
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







Bob Phillips wrote:
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.





"George Gee" wrote in message
...
Hi all

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

Many thanks

George

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


Bob Phillips wrote:
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.





"George Gee" wrote in message
...
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


Bob Phillips wrote:
Use the change event as I showed, then it only runs when you change
the data, as you originally asked.


"George Gee" wrote in message
...
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


George Gee wrote:
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


Mangesh Yadav wrote:
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



"George Gee" wrote in message
...
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







Bob Phillips wrote:
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.





"George Gee" wrote in message
...
Hi all

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

Many thanks

George

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)


"George Gee" wrote in message
...
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


Bob Phillips wrote:
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.

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



Bob Phillips wrote:
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 ;-)

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)


"George Gee" wrote in message
...
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



Bob Phillips wrote:
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 ;-)

Sorry,

should be

Worksheet_Change


Mangesh




"George Gee" wrote in message
...
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


Mangesh Yadav wrote:
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



"George Gee" wrote in message
...
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







Bob Phillips wrote:
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.





"George Gee" wrote in message
...
Hi all

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

Many thanks

George

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




"George Gee" wrote in message
...
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



Bob Phillips wrote:
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 ;-)

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




"George Gee" wrote in message
...
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

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 Yadav wrote:
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" wrote in message
...
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

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" wrote in message
...
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 Yadav wrote:
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" wrote in message
...
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