Solved

Expect to have the way

Posted on 2015-02-03
6
89 Views
Last Modified: 2016-02-10
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
Comment
Question by:HuaMinChen
  • 2
  • 2
  • 2
6 Comments
 
LVL 11

Assisted Solution

by:jkpieterse
jkpieterse earned 166 total points
ID: 40587932
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
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 334 total points
ID: 40587934
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
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40587950
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 10

Author Comment

by:HuaMinChen
ID: 40587952
Typo:
... the 2nd event has not been fired as expected.
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 334 total points
ID: 40587975
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
 
LVL 11

Expert Comment

by:jkpieterse
ID: 40588020
Or:

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Copy VBA code from old hard disk 2 27
InternetExplorer object in Excel VBA. 4 21
Easy Excel formula needed 4 23
Excel VBA Conceal connection string for SQL 9 13
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

1 Experts available now in Live!

Get 1:1 Help Now