Solved

Expect to have the way

Posted on 2015-02-03
6
91 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

830 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