wait for user action in VBA code
 

Do you know how to adapt the code,
Private Sub Worksheet_Change(ByVal Target As Range)
to execute when one particular cell is changed rather than any cell in the
worksheet?

"Tom Ogilvy" wrote:

in general, no.

You would need to interact with the user with an inputbox, msgbox, or
userform and take the appropriate action.

However, clever use of events and so forth may allow you to achieve your
objective.

--
Regards,
Tom Ogilvy

"benb" wrote in message
...
Is there a way in VBA to stop executing the code, wait for the user to
copy &
paste or enter a value into a particular cell (perhaps perform some other
action like opening files to find the value in question), then finish
executing the code after having entered the value? Thanks, as usual, for
your help.

wait for user action in VBA code
 

If Target.address ="$C$1" then

Your Code

End IF

If you need to do a range try

Dim R as range
Set R = Intersect(Target.address, Range("A:A"))

If R is not nothing then
Your Code
End If

HT

--
CBrin

-----------------------------------------------------------------------
CBrine's Profile: http://www.excelforum.com/member.php...fo&userid=1470
View this thread: http://www.excelforum.com/showthread.php?threadid=26323

wait for user action in VBA code
 

The event will fire on the change of every cell. You just structure the
code so nothing happens except for cells of interest. Target will hold a
range reference for the cells that changed.

You can use test like

If target.count 1 then exit sub

'Single Column
if Target.column = 8 then
do something

' single cell
if Target.Address = "$A$5" then
do something

multiple cells
if Not intersect(Range("A1,B9:F12,G24"), target) is nothing then
' do something

Private Sub Worksheet_Change(ByVal Target As Range)
if target.count 1 then exit sub
it Target.Row = 3 and Target.Row <=5 then
msgbox "Processing a cell in rows 3 to 5 inclusive"
End if
End Sub

--
Regards,
Tom Ogilvy

"benb" wrote in message
...
Do you know how to adapt the code,
Private Sub Worksheet_Change(ByVal Target As Range)
to execute when one particular cell is changed rather than any cell in the
worksheet?

"Tom Ogilvy" wrote:

in general, no.

You would need to interact with the user with an inputbox, msgbox, or
userform and take the appropriate action.

However, clever use of events and so forth may allow you to achieve your
objective.

--
Regards,
Tom Ogilvy

"benb" wrote in message
...
Is there a way in VBA to stop executing the code, wait for the user to
copy &
paste or enter a value into a particular cell (perhaps perform some
other
action like opening files to find the value in question), then finish
executing the code after having entered the value? Thanks, as usual,
for
your help.

wait for user action in VBA code
 

Nice, thanks Tom.

"Tom Ogilvy" wrote:

The event will fire on the change of every cell. You just structure the
code so nothing happens except for cells of interest. Target will hold a
range reference for the cells that changed.

You can use test like

If target.count 1 then exit sub

'Single Column
if Target.column = 8 then
do something

' single cell
if Target.Address = "$A$5" then
do something

multiple cells
if Not intersect(Range("A1,B9:F12,G24"), target) is nothing then
' do something

Private Sub Worksheet_Change(ByVal Target As Range)
if target.count 1 then exit sub
it Target.Row = 3 and Target.Row <=5 then
msgbox "Processing a cell in rows 3 to 5 inclusive"
End if
End Sub

--
Regards,
Tom Ogilvy

"benb" wrote in message
...
Do you know how to adapt the code,
Private Sub Worksheet_Change(ByVal Target As Range)
to execute when one particular cell is changed rather than any cell in the
worksheet?

"Tom Ogilvy" wrote:

in general, no.

You would need to interact with the user with an inputbox, msgbox, or
userform and take the appropriate action.

However, clever use of events and so forth may allow you to achieve your
objective.

--
Regards,
Tom Ogilvy

"benb" wrote in message
...
Is there a way in VBA to stop executing the code, wait for the user to
copy &
paste or enter a value into a particular cell (perhaps perform some
other
action like opening files to find the value in question), then finish
executing the code after having entered the value? Thanks, as usual,
for
your help.

wait for user action in VBA code
 

Thank you.

"CBrine" wrote:


If Target.address ="$C$1" then

Your Code

End IF

If you need to do a range try

Dim R as range
Set R = Intersect(Target.address, Range("A:A"))

If R is not nothing then
Your Code
End If

HTH


--
CBrine


------------------------------------------------------------------------
CBrine's Profile: http://www.excelforum.com/member.php...o&userid=14705
View this thread: http://www.excelforum.com/showthread...hreadid=263234