wait for user action in VBA code

[Guest]

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.

 

[Tom Ogilvy]

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.

 

[Guest]

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?

 

[CBrine]

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

 

[Tom Ogilvy]

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

 

[Guest]

Nice, thanks Tom.

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

 

[Guest]

Thank you.