?
Solved

Change events puzzle me...

Posted on 2014-01-22
6
Medium Priority
?
311 Views
Last Modified: 2014-01-27
I have been learning vba now for about 1 year and the light is slowly going on but not burning bright at all...so I am still very "beginner-ish" but am getting better.

In that time I have gotten to know how to handle ranges and rows quite well per my previous lot of questions.

Having said that I have come to somewhat of a cross roads with my project and I have to wonder is there a better way.

Having said that I am going to describe in detail what I am trying to do and see if there is indeed a better way.

So without further adieu here is what I have.

I have a  data set that dynamically changing all of the time.

Let's say that there are 1000 rows.

What I want to know is:

Let's say that there is a change in the row 24, column G.  Let's say that in Column G there is a simple formula that says if e24>f24 then 1, otherwise "".  Well, e24 suddenly = 9 and there is nothing in f24 so G24 = 1.  This is the kick off to the change event.   The rest of the code after this I am handle.  I even have the code needed to "reset" G24 for the next time.

What I do not need is for any other row to be affected whatsoever.  I am only interested in row 24 because G24 fired.

Now if G56, which also has a similar formula to G24 (all cells in column G do between G10:G1010) then I would like to see a change for only things in row 56.

...and so on down the whole range.

What I DO NOT WANT IS A LOOP.  THEY TAKE TOO LONG PLEASE AND THANK YOU.

I was playing around with:

Range("G10:G1010").specialcells(xlCellsTypeFormula, 2).Calculate = "1"

...but I am not sure how to manipulate it further than that.

So in short, I need a "change event" or "calculate event" or something else that is lightning fast for these ranges.   What ever happen in that particular row on affects that particular row and the rest of the range is unaffected.

Any help or illumination on this problem would be much appreciated.

thanks,
0
Comment
Question by:BostonBob
[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
  • 3
  • 2
6 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 39802290
Sorry your post is too confusing and long. I read it twice and lost between what is and what should and what you want.

Can you in 1 sentence ask a question on what exactly you want ?
gowflow
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39802324
the Workbook_SheetChange event provides a Target range property that you can interrogate to get the cell that has changed.

There is also a Worksheet_Change event, attached to each worksheet but often easier to trap the workbook event and then filter out the sheets that need to be checked.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    MsgBox "chaneg - " & Target.Address
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Open in new window

note that only cells that get changed 'manually' trigger the event. Cells that change due to formula do not trigger event.
0
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 39802391
I would add on:


note that only cells that get changed 'manually' trigger the event. Cells that change due to formula do not trigger event.

That if the event is a Calculate event then YES [Cells that change due to formula WILL trigger event.]

gowflow
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:BostonBob
ID: 39802735
Gowflow,

You are working on my other solution.

In one sentence:  In my other solution,....is there an overall better way to accomplish what I am trying to do there?   (All of this will be automated, that test10 sheet is just an example).

thanks!
0
 

Author Comment

by:BostonBob
ID: 39808380
So in my other sheet I have something that will automatically make a change event.   Not manually.  

But is there a different way all together?  Willing to try new options!

thanks!
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39811303
Thank you for the appreciation. You may put your comments in the other question that  I will keep open this one will not be monitored.
gowflow
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Suggested Courses

771 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