|
Update Values as soon as a Worksheet becomes active?
I noticed that when I start the Worksheet, the following action is at the top
of my module and does not execute until I click on something on the Worksheet. In my Worksheet Module I have one SUB(). Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not ActiveSheet.Previous Is Nothing Then Range("A1").Value = ActiveSheet.Previous.Range("A1").Value End If €¦more code... I suspect that by the name of the Sub evens are monitored only when an event is activated. Now, how do I code a sub() to execute as soon as the Worksheet becomes active? And where do I put it? |
Update Values as soon as a Worksheet becomes active?
Hi CRayF,
Try the Worksheet_Activate() event. --- Regards, Norman "CRayF" wrote in message ... I noticed that when I start the Worksheet, the following action is at the top of my module and does not execute until I click on something on the Worksheet. In my Worksheet Module I have one SUB(). Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not ActiveSheet.Previous Is Nothing Then Range("A1").Value = ActiveSheet.Previous.Range("A1").Value End If .more code... I suspect that by the name of the Sub evens are monitored only when an event is activated. Now, how do I code a sub() to execute as soon as the Worksheet becomes active? And where do I put it? |
Update Values as soon as a Worksheet becomes active?
That didn't work.
I tried adding it to the top of the Module... I then tried adding Private Sub Worksheet_Activate(ByVal Target As Range) That didn't work either. And where do I put it? Private Sub Worksheet_Activate() '------------------------------------------------------------------------ ' [ C2 ] Pull Previous Race Account Balance if available ' *Note This is being populates at startup '------------------------------------------------------------------------ If Not ActiveSheet.Previous Is Nothing Then Range("C2").Value = ActiveSheet.Previous.Range("C4").Value End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) '------- Main File Names used for this WorkBOOK --------------- '--*** Use Program Input Worksheet as a Master for changes *** '-------------------------------------------------------------- Dim srcProgramDataInputWs As Worksheet .... ....more code... .... "Norman Jones" wrote: Hi CRayF, Try the Worksheet_Activate() event. --- Regards, Norman "CRayF" wrote in message ... I noticed that when I start the Worksheet, the following action is at the top of my module and does not execute until I click on something on the Worksheet. In my Worksheet Module I have one SUB(). Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not ActiveSheet.Previous Is Nothing Then Range("A1").Value = ActiveSheet.Previous.Range("A1").Value End If .more code... I suspect that by the name of the Sub evens are monitored only when an event is activated. Now, how do I code a sub() to execute as soon as the Worksheet becomes active? And where do I put it? |
Update Values as soon as a Worksheet becomes active?
Hi CRayF,
That didn't work I pasted the following into Sheet2's code module in a new workbook '=============== Private Sub Worksheet_Activate() If Not ActiveSheet.Previous Is Nothing Then Me.Range("C2").Value = Me.Previous.Range("C4").Value End If End Sub ..<<=============== I then made an entry in cell C2 on Sheet1 (say "EUREKA"). Returning to Sheet2, cell C4 recorded "EUREKA" So, for me at least, it did work. --- Regards, Norman "CRayF" wrote in message ... That didn't work. I tried adding it to the top of the Module... I then tried adding Private Sub Worksheet_Activate(ByVal Target As Range) That didn't work either. And where do I put it? Private Sub Worksheet_Activate() '------------------------------------------------------------------------ ' [ C2 ] Pull Previous Race Account Balance if available ' *Note This is being populates at startup '------------------------------------------------------------------------ If Not ActiveSheet.Previous Is Nothing Then Range("C2").Value = ActiveSheet.Previous.Range("C4").Value End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) '------- Main File Names used for this WorkBOOK --------------- '--*** Use Program Input Worksheet as a Master for changes *** '-------------------------------------------------------------- Dim srcProgramDataInputWs As Worksheet ... ...more code... ... "Norman Jones" wrote: Hi CRayF, Try the Worksheet_Activate() event. --- Regards, Norman "CRayF" wrote in message ... I noticed that when I start the Worksheet, the following action is at the top of my module and does not execute until I click on something on the Worksheet. In my Worksheet Module I have one SUB(). Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not ActiveSheet.Previous Is Nothing Then Range("A1").Value = ActiveSheet.Previous.Range("A1").Value End If .more code... I suspect that by the name of the Sub evens are monitored only when an event is activated. Now, how do I code a sub() to execute as soon as the Worksheet becomes active? And where do I put it? |
Update Values as soon as a Worksheet becomes active?
Hi CRayF,
I then made an entry in cell C2 on Sheet1 (say "EUREKA"). Returning to Sheet2, cell C4 recorded "EUREKA Should have read: I then made an entry in cell C4 on Sheet1 (say "EUREKA"). Returning to Sheet2, cell C2 recorded "EUREKA (I inadvertently inverted the C2, C4 cell references!) --- Regards, Norman "Norman Jones" wrote in message ... Hi CRayF, That didn't work I pasted the following into Sheet2's code module in a new workbook '=============== Private Sub Worksheet_Activate() If Not ActiveSheet.Previous Is Nothing Then Me.Range("C2").Value = Me.Previous.Range("C4").Value End If End Sub .<<=============== I then made an entry in cell C2 on Sheet1 (say "EUREKA"). Returning to Sheet2, cell C4 recorded "EUREKA" So, for me at least, it did work. --- Regards, Norman "CRayF" wrote in message ... That didn't work. I tried adding it to the top of the Module... I then tried adding Private Sub Worksheet_Activate(ByVal Target As Range) That didn't work either. And where do I put it? Private Sub Worksheet_Activate() '------------------------------------------------------------------------ ' [ C2 ] Pull Previous Race Account Balance if available ' *Note This is being populates at startup '------------------------------------------------------------------------ If Not ActiveSheet.Previous Is Nothing Then Range("C2").Value = ActiveSheet.Previous.Range("C4").Value End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) '------- Main File Names used for this WorkBOOK --------------- '--*** Use Program Input Worksheet as a Master for changes *** '-------------------------------------------------------------- Dim srcProgramDataInputWs As Worksheet ... ...more code... ... "Norman Jones" wrote: Hi CRayF, Try the Worksheet_Activate() event. --- Regards, Norman "CRayF" wrote in message ... I noticed that when I start the Worksheet, the following action is at the top of my module and does not execute until I click on something on the Worksheet. In my Worksheet Module I have one SUB(). Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not ActiveSheet.Previous Is Nothing Then Range("A1").Value = ActiveSheet.Previous.Range("A1").Value End If .more code... I suspect that by the name of the Sub evens are monitored only when an event is activated. Now, how do I code a sub() to execute as soon as the Worksheet becomes active? And where do I put it? |
Update Values as soon as a Worksheet becomes active?
Here's what's happing. I click on a a cell that creates a new target
Worksheet built from a template. When the new target is build, this new Worksheet becomes Active. I am hoping to have the value update immediately. What is happing is that if I click off that Worksheet and return, it works fine. But it is not being populated once I am initially placed there my the earlier SUB(). "Norman Jones" wrote: Hi CRayF, I then made an entry in cell C2 on Sheet1 (say "EUREKA"). Returning to Sheet2, cell C4 recorded "EUREKA Should have read: I then made an entry in cell C4 on Sheet1 (say "EUREKA"). Returning to Sheet2, cell C2 recorded "EUREKA (I inadvertently inverted the C2, C4 cell references!) --- Regards, Norman "Norman Jones" wrote in message ... Hi CRayF, That didn't work I pasted the following into Sheet2's code module in a new workbook '=============== Private Sub Worksheet_Activate() If Not ActiveSheet.Previous Is Nothing Then Me.Range("C2").Value = Me.Previous.Range("C4").Value End If End Sub .<<=============== I then made an entry in cell C2 on Sheet1 (say "EUREKA"). Returning to Sheet2, cell C4 recorded "EUREKA" So, for me at least, it did work. --- Regards, Norman "CRayF" wrote in message ... That didn't work. I tried adding it to the top of the Module... I then tried adding Private Sub Worksheet_Activate(ByVal Target As Range) That didn't work either. And where do I put it? Private Sub Worksheet_Activate() '------------------------------------------------------------------------ ' [ C2 ] Pull Previous Race Account Balance if available ' *Note This is being populates at startup '------------------------------------------------------------------------ If Not ActiveSheet.Previous Is Nothing Then Range("C2").Value = ActiveSheet.Previous.Range("C4").Value End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) '------- Main File Names used for this WorkBOOK --------------- '--*** Use Program Input Worksheet as a Master for changes *** '-------------------------------------------------------------- Dim srcProgramDataInputWs As Worksheet ... ...more code... ... "Norman Jones" wrote: Hi CRayF, Try the Worksheet_Activate() event. --- Regards, Norman "CRayF" wrote in message ... I noticed that when I start the Worksheet, the following action is at the top of my module and does not execute until I click on something on the Worksheet. In my Worksheet Module I have one SUB(). Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not ActiveSheet.Previous Is Nothing Then Range("A1").Value = ActiveSheet.Previous.Range("A1").Value End If .more code... I suspect that by the name of the Sub evens are monitored only when an event is activated. Now, how do I code a sub() to execute as soon as the Worksheet becomes active? And where do I put it? |
Update Values as soon as a Worksheet becomes active?
Hi CRayF,
Here's what's happing. I click on a a cell that creates a new target Worksheet built from a template. When the new target is build, this new Worksheet becomes Active. I am hoping to have the value update immediately. In that case, the sheet template file would need to hold the suggested code. I pasted the suggested code into a worksheet's code module and then saved that sheet as a sheet template. I then added a new worksheet, based on this template, between Sheets 1 and 2 of an open workbook; the new template sheet's C2 cell correctly reported the entry in cell C4 in Sheet1. That said, I doubt that I would use a template sheet in this manner. Why not add the sheet programmatically and populate the new C2 cell in this code? Perhaps, however, I have misunderstood your requirements. --- Regards, Norman "CRayF" wrote in message ... Here's what's happing. I click on a a cell that creates a new target Worksheet built from a template. When the new target is build, this new Worksheet becomes Active. I am hoping to have the value update immediately. What is happing is that if I click off that Worksheet and return, it works fine. But it is not being populated once I am initially placed there my the earlier SUB(). "Norman Jones" wrote: Hi CRayF, I then made an entry in cell C2 on Sheet1 (say "EUREKA"). Returning to Sheet2, cell C4 recorded "EUREKA Should have read: I then made an entry in cell C4 on Sheet1 (say "EUREKA"). Returning to Sheet2, cell C2 recorded "EUREKA (I inadvertently inverted the C2, C4 cell references!) --- Regards, Norman "Norman Jones" wrote in message ... Hi CRayF, That didn't work I pasted the following into Sheet2's code module in a new workbook '=============== Private Sub Worksheet_Activate() If Not ActiveSheet.Previous Is Nothing Then Me.Range("C2").Value = Me.Previous.Range("C4").Value End If End Sub .<<=============== I then made an entry in cell C2 on Sheet1 (say "EUREKA"). Returning to Sheet2, cell C4 recorded "EUREKA" So, for me at least, it did work. --- Regards, Norman "CRayF" wrote in message ... That didn't work. I tried adding it to the top of the Module... I then tried adding Private Sub Worksheet_Activate(ByVal Target As Range) That didn't work either. And where do I put it? Private Sub Worksheet_Activate() '------------------------------------------------------------------------ ' [ C2 ] Pull Previous Race Account Balance if available ' *Note This is being populates at startup '------------------------------------------------------------------------ If Not ActiveSheet.Previous Is Nothing Then Range("C2").Value = ActiveSheet.Previous.Range("C4").Value End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) '------- Main File Names used for this WorkBOOK --------------- '--*** Use Program Input Worksheet as a Master for changes *** '-------------------------------------------------------------- Dim srcProgramDataInputWs As Worksheet ... ...more code... ... "Norman Jones" wrote: Hi CRayF, Try the Worksheet_Activate() event. --- Regards, Norman "CRayF" wrote in message ... I noticed that when I start the Worksheet, the following action is at the top of my module and does not execute until I click on something on the Worksheet. In my Worksheet Module I have one SUB(). Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not ActiveSheet.Previous Is Nothing Then Range("A1").Value = ActiveSheet.Previous.Range("A1").Value End If .more code... I suspect that by the name of the Sub evens are monitored only when an event is activated. Now, how do I code a sub() to execute as soon as the Worksheet becomes active? And where do I put it? |
Update Values as soon as a Worksheet becomes active?
How would I do this? Can I simply write somehting in the Cell itself to
aquire value of Cell C4 from the Workshhet to the left. "Norman Jones" wrote: Hi CRayF, Here's what's happing. I click on a a cell that creates a new target Worksheet built from a template. When the new target is build, this new Worksheet becomes Active. I am hoping to have the value update immediately. In that case, the sheet template file would need to hold the suggested code. I pasted the suggested code into a worksheet's code module and then saved that sheet as a sheet template. I then added a new worksheet, based on this template, between Sheets 1 and 2 of an open workbook; the new template sheet's C2 cell correctly reported the entry in cell C4 in Sheet1. That said, I doubt that I would use a template sheet in this manner. Why not add the sheet programmatically and populate the new C2 cell in this code? Perhaps, however, I have misunderstood your requirements. --- Regards, Norman "CRayF" wrote in message ... Here's what's happing. I click on a a cell that creates a new target Worksheet built from a template. When the new target is build, this new Worksheet becomes Active. I am hoping to have the value update immediately. What is happing is that if I click off that Worksheet and return, it works fine. But it is not being populated once I am initially placed there my the earlier SUB(). "Norman Jones" wrote: Hi CRayF, I then made an entry in cell C2 on Sheet1 (say "EUREKA"). Returning to Sheet2, cell C4 recorded "EUREKA Should have read: I then made an entry in cell C4 on Sheet1 (say "EUREKA"). Returning to Sheet2, cell C2 recorded "EUREKA (I inadvertently inverted the C2, C4 cell references!) --- Regards, Norman "Norman Jones" wrote in message ... Hi CRayF, That didn't work I pasted the following into Sheet2's code module in a new workbook '=============== Private Sub Worksheet_Activate() If Not ActiveSheet.Previous Is Nothing Then Me.Range("C2").Value = Me.Previous.Range("C4").Value End If End Sub .<<=============== I then made an entry in cell C2 on Sheet1 (say "EUREKA"). Returning to Sheet2, cell C4 recorded "EUREKA" So, for me at least, it did work. --- Regards, Norman "CRayF" wrote in message ... That didn't work. I tried adding it to the top of the Module... I then tried adding Private Sub Worksheet_Activate(ByVal Target As Range) That didn't work either. And where do I put it? Private Sub Worksheet_Activate() '------------------------------------------------------------------------ ' [ C2 ] Pull Previous Race Account Balance if available ' *Note This is being populates at startup '------------------------------------------------------------------------ If Not ActiveSheet.Previous Is Nothing Then Range("C2").Value = ActiveSheet.Previous.Range("C4").Value End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) '------- Main File Names used for this WorkBOOK --------------- '--*** Use Program Input Worksheet as a Master for changes *** '-------------------------------------------------------------- Dim srcProgramDataInputWs As Worksheet ... ...more code... ... "Norman Jones" wrote: Hi CRayF, Try the Worksheet_Activate() event. --- Regards, Norman "CRayF" wrote in message ... I noticed that when I start the Worksheet, the following action is at the top of my module and does not execute until I click on something on the Worksheet. In my Worksheet Module I have one SUB(). Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not ActiveSheet.Previous Is Nothing Then Range("A1").Value = ActiveSheet.Previous.Range("A1").Value End If .more code... I suspect that by the name of the Sub evens are monitored only when an event is activated. Now, how do I code a sub() to execute as soon as the Worksheet becomes active? And where do I put it? |
Update Values as soon as a Worksheet becomes active?
Hi CRayF,
Your initial request was: Now, how do I code a sub() to execute as soon as the Worksheet becomes active? And where do I put it? As indicated ealier in this thread, that requirement could be satisfied with Worksheet_Activate event code. How would I do this? Can I simply write somehting in the Cell itself to aquire value of Cell C4 from the Workshhet to the left. Perhaps, you need the Worksheet_SelectionChange event, e.g.: '========================== Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Not Intersect(Target, Me.Range("C2")) Is Nothing Then Target.Value = Me.Previous.Range("C4").Value End If End Sub '========================== The above code will populate cell C2, on the sheet holding the code, with the value of cell C4 on the preceding sheet, upon selection of the C2 cell. --- Regards, Norman "CRayF" wrote in message ... How would I do this? Can I simply write somehting in the Cell itself to aquire value of Cell C4 from the Workshhet to the left. "Norman Jones" wrote: Hi CRayF, Here's what's happing. I click on a a cell that creates a new target Worksheet built from a template. When the new target is build, this new Worksheet becomes Active. I am hoping to have the value update immediately. In that case, the sheet template file would need to hold the suggested code. I pasted the suggested code into a worksheet's code module and then saved that sheet as a sheet template. I then added a new worksheet, based on this template, between Sheets 1 and 2 of an open workbook; the new template sheet's C2 cell correctly reported the entry in cell C4 in Sheet1. That said, I doubt that I would use a template sheet in this manner. Why not add the sheet programmatically and populate the new C2 cell in this code? Perhaps, however, I have misunderstood your requirements. --- Regards, Norman "CRayF" wrote in message ... Here's what's happing. I click on a a cell that creates a new target Worksheet built from a template. When the new target is build, this new Worksheet becomes Active. I am hoping to have the value update immediately. What is happing is that if I click off that Worksheet and return, it works fine. But it is not being populated once I am initially placed there my the earlier SUB(). "Norman Jones" wrote: Hi CRayF, I then made an entry in cell C2 on Sheet1 (say "EUREKA"). Returning to Sheet2, cell C4 recorded "EUREKA Should have read: I then made an entry in cell C4 on Sheet1 (say "EUREKA"). Returning to Sheet2, cell C2 recorded "EUREKA (I inadvertently inverted the C2, C4 cell references!) --- Regards, Norman "Norman Jones" wrote in message ... Hi CRayF, That didn't work I pasted the following into Sheet2's code module in a new workbook '=============== Private Sub Worksheet_Activate() If Not ActiveSheet.Previous Is Nothing Then Me.Range("C2").Value = Me.Previous.Range("C4").Value End If End Sub .<<=============== I then made an entry in cell C2 on Sheet1 (say "EUREKA"). Returning to Sheet2, cell C4 recorded "EUREKA" So, for me at least, it did work. --- Regards, Norman "CRayF" wrote in message ... That didn't work. I tried adding it to the top of the Module... I then tried adding Private Sub Worksheet_Activate(ByVal Target As Range) That didn't work either. And where do I put it? Private Sub Worksheet_Activate() '------------------------------------------------------------------------ ' [ C2 ] Pull Previous Race Account Balance if available ' *Note This is being populates at startup '------------------------------------------------------------------------ If Not ActiveSheet.Previous Is Nothing Then Range("C2").Value = ActiveSheet.Previous.Range("C4").Value End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) '------- Main File Names used for this WorkBOOK --------------- '--*** Use Program Input Worksheet as a Master for changes *** '-------------------------------------------------------------- Dim srcProgramDataInputWs As Worksheet ... ...more code... ... "Norman Jones" wrote: Hi CRayF, Try the Worksheet_Activate() event. --- Regards, Norman "CRayF" wrote in message ... I noticed that when I start the Worksheet, the following action is at the top of my module and does not execute until I click on something on the Worksheet. In my Worksheet Module I have one SUB(). Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not ActiveSheet.Previous Is Nothing Then Range("A1").Value = ActiveSheet.Previous.Range("A1").Value End If .more code... I suspect that by the name of the Sub evens are monitored only when an event is activated. Now, how do I code a sub() to execute as soon as the Worksheet becomes active? And where do I put it? |
Update Values as soon as a Worksheet becomes active?
I don't want to have to select a cell for it to change. I want it to be there
as soon as the Worksheet is Selected. So, (trying to get a grasp on on this new stuff) I thought understood I would need the Private Sub Worksheet_Activate(), not the Private Sub Worksheet_SelectionChange. So, on the top of the current code in the Worksheet Module where I want the value to change, I coded this exactly as suggested and it almost works. I mean it DOES NOT populate UNTIL I click off the Worksheet and this back onto it. *If it matters, I am automatically placed onto this Worksheet (with the code) as soon as it gets built (from another module). Here is the what I have coded with a few lines out of the middle taken out that don't relate. '------- Main File Names used for this WorkBOOK --------------- '--*** Use Program Input Worksheet as a Master for changes *** '-------------------------------------------------------------- Dim srcProgramDataInputWs As Worksheet Dim srcProgramSummaryTemplateWs As Worksheet Dim srcProgramSummaryWs As Worksheet Dim srcBettingTemplateWs As Worksheet '-------------------------------------------------------------- '------- Set Variables to Workbook Names --------------- '--*** Use Program Input Worksheet as a Master for changes *** '-------------------------------------------------------------- Private Sub Worksheet_Activate() Set srcProgramSummaryTemplateWs = Sheets("@TemplateProgramSummary") Set srcProgramSummaryWs = Sheets("ProgramSummary") Set srcBettingTemplateWs = Sheets("@TempleteBetting") Set srcProgramDataInputWs = Sheets("ProgramDataInput") If Not ActiveSheet.Previous Is Nothing Then Range("C2").Value = ActiveSheet.Previous.Range("C4").Value End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ErrorHandler Application.EnableEvents = False Set srcProgramSummaryTemplateWs = Sheets("@TemplateProgramSummary") Set srcProgramSummaryWs = Sheets("ProgramSummary") Set srcBettingTemplateWs = Sheets("@TempleteBetting") Set srcProgramDataInputWs = Sheets("ProgramDataInput") Dim raceParkPrefix As Variant Dim raceParkName As Variant raceParkPrefix = Left(srcProgramDataInputWs.Range("H3").Value, 3) 'ex. PHX 'some other code... '------------------------------------------- ' [Q7] Static Cell Selection for DD Race Worksheet '------------------------------------------- If Target.Address = "$Q$7" Then If UCase(Range("R6")) = "TRUE" Then Range("R6") = "FALSE" Range("A16").Select Else Range("R6") = "TRUE" Range("A16").Select End If End If ' Cancel = True ErrorHandler: Application.EnableEvents = True End Sub Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrorHandler: Application.EnableEvents = False ' ErrorHandler: Application.EnableEvents = True End Sub |
Update Values as soon as a Worksheet becomes active?
Hi CRayF,
*If it matters, I am automatically placed onto this Worksheet (with the code) as soon as it gets built (from another module) Normally, manual or code selection of the worksheet should fire the activate event. Try posting this code snippet. --- Regards, Norman "CRayF" wrote in message ... I don't want to have to select a cell for it to change. I want it to be there as soon as the Worksheet is Selected. So, (trying to get a grasp on on this new stuff) I thought understood I would need the Private Sub Worksheet_Activate(), not the Private Sub Worksheet_SelectionChange. So, on the top of the current code in the Worksheet Module where I want the value to change, I coded this exactly as suggested and it almost works. I mean it DOES NOT populate UNTIL I click off the Worksheet and this back onto it. *If it matters, I am automatically placed onto this Worksheet (with the code) as soon as it gets built (from another module). Here is the what I have coded with a few lines out of the middle taken out that don't relate. '------- Main File Names used for this WorkBOOK --------------- '--*** Use Program Input Worksheet as a Master for changes *** '-------------------------------------------------------------- Dim srcProgramDataInputWs As Worksheet Dim srcProgramSummaryTemplateWs As Worksheet Dim srcProgramSummaryWs As Worksheet Dim srcBettingTemplateWs As Worksheet '-------------------------------------------------------------- '------- Set Variables to Workbook Names --------------- '--*** Use Program Input Worksheet as a Master for changes *** '-------------------------------------------------------------- Private Sub Worksheet_Activate() Set srcProgramSummaryTemplateWs = Sheets("@TemplateProgramSummary") Set srcProgramSummaryWs = Sheets("ProgramSummary") Set srcBettingTemplateWs = Sheets("@TempleteBetting") Set srcProgramDataInputWs = Sheets("ProgramDataInput") If Not ActiveSheet.Previous Is Nothing Then Range("C2").Value = ActiveSheet.Previous.Range("C4").Value End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ErrorHandler Application.EnableEvents = False Set srcProgramSummaryTemplateWs = Sheets("@TemplateProgramSummary") Set srcProgramSummaryWs = Sheets("ProgramSummary") Set srcBettingTemplateWs = Sheets("@TempleteBetting") Set srcProgramDataInputWs = Sheets("ProgramDataInput") Dim raceParkPrefix As Variant Dim raceParkName As Variant raceParkPrefix = Left(srcProgramDataInputWs.Range("H3").Value, 3) 'ex. PHX 'some other code... '------------------------------------------- ' [Q7] Static Cell Selection for DD Race Worksheet '------------------------------------------- If Target.Address = "$Q$7" Then If UCase(Range("R6")) = "TRUE" Then Range("R6") = "FALSE" Range("A16").Select Else Range("R6") = "TRUE" Range("A16").Select End If End If ' Cancel = True ErrorHandler: Application.EnableEvents = True End Sub Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrorHandler: Application.EnableEvents = False ' ErrorHandler: Application.EnableEvents = True End Sub |
Update Values as soon as a Worksheet becomes active?
OK, Norman,
With your help, I learned more about the Private Sub Worksheet_Activate(). Got it fixed. Program Summary is the main place racers are looked at as a whole. When Im ready, I use the B1 Cell to kick off building this new Race Worksheet. The Race Worksheet is built using the RaceTemplate; a Header and set of 12 rows that make up a 1 race template where I can back changes and maintain that the set of race rows look like. This set of rows are replicated as many times as there are races from the Program Input File. What I was trying to do is carry the balance forward from the previous race. BUT I WAS GOING ABOUT IT WRONG. Thanks to you asking for me about the snipet that called it, I got to thinking why the heck would I not automatically add it during the building of the Betting Sheet. Rather than when I got to it€¦ Kind of like DUH! Oh well€¦ I got it fixed and learned more about the Private Sub Worksheet_Activate() process.. |
| All times are GMT +1. The time now is 02:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com