Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Understanding change events
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Understanding change events
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Understanding change events
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 Die_Another_Day wrote: 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 wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Understanding change events
Give this site a look...
http://www.cpearson.com/excel/events.htm -- HTH... Jim Thomlinson "xhat" wrote: 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 Die_Another_Day wrote: 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 wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Understanding change events
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 Jim Thomlinson wrote: Give this site a look... http://www.cpearson.com/excel/events.htm -- HTH... Jim Thomlinson "xhat" wrote: 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 Die_Another_Day wrote: 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 wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Understanding change events
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" wrote in message oups.com... 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 Die_Another_Day wrote: 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 wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Understanding change events
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. e NickHK wrote: 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" wrote in message oups.com... 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 Die_Another_Day wrote: 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 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine 2 worksheet change events | Excel Discussion (Misc queries) | |||
Cell change events | Excel Discussion (Misc queries) | |||
Multiple combobox and change events | Excel Programming | |||
Multiple change events on same sheet | Excel Programming | |||
Multiple change events on same sheet | Excel Programming |