[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Expect to have the way

Posted on 2015-02-03
6
Medium Priority
?
96 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 11

Assisted Solution

by:jkpieterse
jkpieterse earned 664 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 1336 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 11

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 11

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 1336 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

649 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