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
On Mon, 30 Apr 2007 14:16:03 -0700, Papa Jonah
<(E-Mail Removed)> wrote:
>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.
>
>
>"Bob Phillips" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:C7362D40-91AF-484D-8164-(E-Mail Removed)...
>> > 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!
>> >
>> >
>> >
>> > "Bob Phillips" wrote:
>> >
>> >> 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" <(E-Mail Removed)> wrote in message
>> >> news:FD396738-8495-4271-8CDA-(E-Mail Removed)...
>> >> >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
>> >>
>> >>
>> >>
>>
>>
>>
|
Similar Threads
