'Event' macro

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" <[email protected]> wrote in message
news:[email protected]...
> Hi all
>
> Is it possible to run a macro by entering data in a cell, and pressing
> Enter?
>
> Many thanks
>
> George
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
news:uh3X#[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> news:uh3X#[email protected]...
>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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" <[email protected]> wrote in message
>> news:uh3X#[email protected]...
>>> 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" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> >> news:uh3X#[email protected]...
> >>> 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" <[email protected]> wrote in message
> >>>> news:[email protected]...
> >>>>> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>>>> news:uh3X#[email protected]...
>>>>> 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" <[email protected]> wrote in message
>>>>>> news:[email protected]...
>>>>>>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >>>> news:uh3X#[email protected]...
> >>>>> 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" <[email protected]> wrote in message
> >>>>>> news:[email protected]...
> >>>>>>> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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" <[email protected]> wrote in message
>>>>>> news:uh3X#[email protected]...
>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>> news:[email protected]...
>>>>>>>>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:uh3X#[email protected]...
> >> 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" <[email protected]> wrote in message
> >>> news:[email protected]...
> >>>> 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" <[email protected]> wrote in message
news:#[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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>
>
>