G
George Gee
Hi all
Is it possible to run a macro by entering data in a cell, and pressing
Enter?
Many thanks
George
Is it possible to run a macro by entering data in a cell, and pressing
Enter?
Many thanks
George
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
Bob Phillips
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)
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)
G
George Gee
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
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
M
Mangesh Yadav
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
if target.address = "$A$1"
call macro1
end if
End Sub
is a simple example. Use the Select Case for all 38 conditions.
Mangesh
G
George Gee
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
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
G
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
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
B
Bob Phillips
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)
data, as you originally asked.
--
HTH
RP
(remove nothere from the email address if mailing direct)
G
George Gee
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
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
B
Bob Phillips
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)
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)
G
George Gee
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
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
B
Bob Phillips
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)
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)
G
George Gee
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
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
B
Bob Phillips
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)
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)
M
Mangesh Yadav
Sorry,
should be
Worksheet_Change
Mangesh
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
M
Mangesh Yadav
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
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
G
George Gee
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 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>
M
Mangesh Yadav
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
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Use
Private Sub Worksheet_Change(ByVal Target As Range)
as the first line
Mangesh
G
George Gee
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
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>
B
Bob Phillips
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)
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>
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.