"after update" type macro

[Papa Jonah]

I know that in Access, I can cause things to happen when a field has been
changed. I want to do something similar in Excel. If any cell in a given
column (G) changes, I want a macro to run that will force the user to enter
data in another cell.
How do I make one of these "after update"- type things happen?

TIA
Papa

 

[Bob Phillips]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'Enter comment text here
Const WS_RANGE As String = "G:G" '<== change to suit

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

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Papa Jonah]

Bob,
As usual, you have helped me greatly. But, now I have encountered a problem
that arises if I do not exit the cell that triggers this code.
In other words, if I hit enter or tab...
If I hit enter, and then go into your code I can get it to work. Ultimately
what your code allows me to do is open a dialogue box that askes for the name
of the approving official - which I want to have recorded in column J of the
same row.
)The reason for the dialogue box is to ensure that the name gets entered.)
However, my code for taking the name and putting it in the cell is:
myname = InputBox("Who is the approving reviewer of this change?", "Name of
approver", "")
Selection.Offset(-1, 3).Select 'this assumes that after the update in
column G
'enter was hit so -1 gets me back to the correct row.
Selection.Value = myname

Is there a way to specify the cell by column without changing the row from
the cell which triggered the whole thing?
Am I rambling?
In summary, if I change G:99, I want to force a name to be entered into J:99.

Thanks for your help!

 

[okrob]

Bob,
As usual, you have helped me greatly. But, now I have encountered a problem
that arises if I do not exit the cell that triggers this code.
In other words, if I hit enter or tab...
If I hit enter, and then go into your code I can get it to work. Ultimately
what your code allows me to do is open a dialogue box that askes for the name
of the approving official - which I want to have recorded in column J of the
same row.
)The reason for the dialogue box is to ensure that the name gets entered.)
However, my code for taking the name and putting it in the cell is:
myname = InputBox("Who is the approving reviewer of this change?", "Name of
approver", "")
Selection.Offset(-1, 3).Select 'this assumes that after the update in
column G
'enter was hit so -1 gets me back to the correct row.
Selection.Value = myname

Is there a way to specify the cell by column without changing the row from
the cell which triggered the whole thing?
Am I rambling?
In summary, if I change G:99, I want to force a name to be entered into J:99.

Thanks for your help!










- Show quoted text -

You need to change the selection.offset to Target.Offset and change
the -1 to 0. This will accomplish your goal whether you "Enter" or
"Tab" out of the cell...
myname = InputBox("Who is the approving reviewer of this change?",
"Name of approver ", "")
Target.Offset(0, 3).Select 'this assumes that after the update in
Column G and keeps the offset in the same row as the entered data
Selection.Value = myname

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
'Enter comment text here
Const WS_RANGE As String = "G:G" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
myname = InputBox("Who is the approving reviewer of this
change?", _
"Name of approver", "")
Target.Offset(0, 3).Value = myname
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Papa Jonah]

That seems to work great Bob, thanks.
I am also trying to do the same thing for another column. But it seems to
be unresponsive. I have basically duplicated your code making changes for
the column. I also tried changing the "WS_Range" to "WS_Range2" - that seems
to be irrelevant. I would think I could do this more than once.

Any thoughts?

Thanks again.

 

[Gord Dibben]

Papa

Try this without the WS_RANGE

Private Sub Worksheet_Change(ByVal Target As Range)
'Enter comment text here

On Error GoTo ws_exit
Application.EnableEvents = False


If Intersect(Range(Target(1).Address), _
Range("A:A, G:G, M:M")) Is Nothing Then Exit Sub

With Target
myname = InputBox("Who is the approving reviewer of this change?", _
"Name of approver", "")
Target.Offset(0, 3).Value = myname
End With

ws_exit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP