• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 97
  • Last Modified:

Expect to have the way

Hi,
Is there any specific way to auto. fire one VBA event, upon that I have finished editing one field, on the Excel sheet?
0
HuaMinChen
Asked:
HuaMinChen
  • 2
  • 2
  • 2
3 Solutions
 
jkpieterseCommented:
Sure. RIght-click the sheet tab and select "View code". At the top of the screen you should see two dropdowns, from the left one select "Worksheet" and from the right one select "Change". Remove everything EXCEPT this code:
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Open in new window

Write the code (or the call to a subroutine) in that sub.
0
 
zorvek (Kevin Jones)ConsultantCommented:
Yes.

Place this code in the code behind the worksheet which you want to monitor:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
        ' Do something when A1 on this worksheet changes
    End If

End Sub

The code will run when any cell on the worksheet is changed. It will look specifically for a change to A1 and then do something.

Kevin
0
 
HuaMinChenBusiness AnalystAuthor Commented:
Thanks all.

I have these
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("L3")) Is Nothing And Not Intersect(Target, Me.Range("L4")) Is Nothing And Not Intersect(Target, Me.Range("L5")) Is Nothing Then
        Refresh_sheet
    End If
...
End Sub
Sub Refresh_sheet()
    ...
End Sub

Open in new window


but after I've put values into cells L3, L4 and L5, the 2nd event has been fired as expected.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
HuaMinChenBusiness AnalystAuthor Commented:
Typo:
... the 2nd event has not been fired as expected.
0
 
zorvek (Kevin Jones)ConsultantCommented:
Change:

        If Not Intersect(Target, Me.Range("L3")) Is Nothing And Not Intersect(Target, Me.Range("L4")) Is Nothing And Not Intersect(Target, Me.Range("L5")) Is Nothing Then

to:

        If Not Intersect(Target, Me.Range("L3")) Is Nothing Or Not Intersect(Target, Me.Range("L4")) Is Nothing Or Not Intersect(Target, Me.Range("L5")) Is Nothing Then

Kevin
0
 
jkpieterseCommented:
Or:

If Not Intersect(Target, Me.Range("L3:L5")) Is Nothing Then
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now