"after update" type macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Papa Jonah - 30 Apr 2007 16:22 GMT

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

Reply to this Message

Bob Phillips - 30 Apr 2007 17:02 GMT

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.

Signature

HTH

Bob

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

>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
[quoted text clipped - 5 lines]
> TIA
> Papa

Reply to this Message

Papa Jonah - 30 Apr 2007 18:26 GMT

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!

> Option Explicit
>
[quoted text clipped - 29 lines]
> > TIA
> > Papa

Reply to this Message

okrob - 30 Apr 2007 18:48 GMT

On Apr 30, 12:26 pm, Papa Jonah <PapaJo...@discussions.microsoft.com>
wrote:

> Bob,
> As usual, you have helped me greatly. But, now I have encountered a problem
[quoted text clipped - 62 lines]
>
> - 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

Reply to this Message

Bob Phillips - 30 Apr 2007 18:58 GMT

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

Signature

HTH

Bob

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

> Bob,
> As usual, you have helped me greatly. But, now I have encountered a
[quoted text clipped - 61 lines]
>> > TIA
>> > Papa

Reply to this Message

Papa Jonah - 30 Apr 2007 22:16 GMT

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.

> Private Sub Worksheet_Change(ByVal Target As Range)
> 'Enter comment text here
[quoted text clipped - 81 lines]
> >> > TIA
> >> > Papa

Reply to this Message

Gord Dibben - 30 Apr 2007 23:48 GMT

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

>That seems to work great Bob, thanks.
>I am also trying to do the same thing for another column. But it seems to
[quoted text clipped - 91 lines]
>> >> > TIA
>> >> > Papa

Reply to this Message