Solved

Expect to have the way

Posted on 2015-02-03
6
90 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 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