Understanding change events

[xhat]

Howdy all. I'm dabbling in Excel VB programming, and have been
searching the web for understanding. While I have found much, it only
serves to increase my questions rather than answer them. So, I turn to
the all-powerful Google to shed light on this poor pilgrim's plight.

Here's the set-up: I have a worksheet that has a web query on it. The
query works just the way I need it to work, however, some of the data
it pulls in is "dirty" and causes some calculated cells on the sheet to
output erroneous totals. I have written a small chunk of code which
"cleans" the strings by lopping off bits of the string that aren't
needed. This works just fine.

(For clarity sake, this section describes exactly what I'm doing. I'm
pulling in table data from www.nfl.com, that has team standings. All
the guys in my fantasy league have drafted a set number (5) of teams,
and those teams' point totals for the season are totalled for each guy
to determine a tie-breaker, if necessary. The problem arises toward
the end of the season when nfl.com starts appending little "y's" and
"x's" and what not to division winners, playoff berth clinch, etc.
Since New York Jets now reads xy-New York Jets (I told you it was
fantasy) for example, the point totals for the Jets are not picked up
in my formula for the guy who owns the Jets. I have to remove "xy-"
from the name, then all is well.)

My goal is to have the macro that handles cleaning the strings be
automatically fired by the "Refresh Data" event. I've been looking at
the Change event, but my concern is that I'll put myself into a
never-ending loop as the macro "changes" the cell after refreshing,
thus triggering the macro, thus... you get the idea. I don't have an
understanding of the underlying functionality of change events and was
hoping someone could point me toward a good resource, or better yet,
give me the cliff notes on change events.

thanks

 

[Die_Another_Day]

I think the short answer to your question is to disable events when
firing off your code.
Sub YourSub()
Application.EnableEvents = False
'YourCode
Application.EnableEvents = True
End Sub

HTH

Die_Another_Day

 

[xhat]

D_A_D,
Yes, thanks, that makes sense. My larger question, however, is how to
detect and programmatically respond to any event in Excel. In this
case, I want to detect and respond to the "Refresh Data" event.
Further, I have to make sure I don't fire my macro code before the "RD"
event is finished, or I imagine I'll have a fine mess.

I guess what I'm really looking for is a primer on Events in Excel, and
how to work with them.

xhat

 

[Guest]

Give this site a look...

http://www.cpearson.com/excel/events.htm

 

[xhat]

Jim,

Funny you should send that link. I came across that site about an hour
after posting. I'm reading now. It looks like good info. Thanks for
posting.

eric

 

[NickHK]

If your query is not set to refresh every "x" minute then you just call the
..Refresh yourself when necessary.
And if .BackgroundRefresh=False, you code will wait until the refresh is
complete before continuing with the processing.
There is no _RefreshComplete events that you can respond to.

As for the prepended "xy-" characters, it depends how you are currently
matching the points to the teams, but...
Cells.Find(What:="New York Jets", After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)

Or loopp through the range checking the values with LIKE "New York".

NickHK

 

[xhat]

Nick,

Those are great ideas, though I don't fully understand them all. As I
said, I'm dabbling. But those give me ideas to keep doing further
reading. I read the article that Jim posted the link to, and it is a
great basic intro into Excel Events and how to capture them. I'm
thinking I'll code the worksheet now to automatically refresh the data
on opening. Maybe that's a simple setting available in Excel, but I'd
rather work it through the code as a means of learning. Anyway, thanks
for the ideas.