Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event Problem
I would like to have a change event for two seperate ranges: "AQ3" and
"AP6." However, I want different operations done, depending on which range is changed. If someone can help me get the main sections of code, I will be able to fill in the operations. John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event Problem
Hi John,
Try something like: '============= Private Sub Worksheet_Change(ByVal Target As Range) Const sAdd1 As String = "AQ3" Const sAdd2 As String = "AP6" If Not Intersect(Range(sAdd1), Target) Is Nothing Then 'Do something, e.g.; MsgBox "Action1!" End If If Not Intersect(Range(sAdd2), Target) Is Nothing Then 'Do something else, e.g.; MsgBox "Action2!" End If End Sub '<<============= --- Regards, Norman "mastermind" wrote in message ps.com... I would like to have a change event for two seperate ranges: "AQ3" and "AP6." However, I want different operations done, depending on which range is changed. If someone can help me get the main sections of code, I will be able to fill in the operations. John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event Problem
For some reason the change event will only run once. I don't know what
would cause that to happen, but that is what it is doing. Do you know of anything that would cause that, or perhaps how to correct that problem? The full section of code I now have is: Private Sub Worksheet_Change(ByVal Target As Range) Const sAdd1 As String = "AQ3" Const sAdd2 As String = "AP6" Const sAdd3 As String = "AP8" If Not Intersect(Range(sAdd1), Target) Is Nothing Then If Target.Value < "" Then Application.EnableEvents = False ActiveSheet.Name = "Report " & Range(sAdd1).Value End If End If If Not Intersect(Range(sAdd2), Target) Is Nothing Then If Target.Value < "" Then Application.EnableEvents = False 'Determines number of days between two dates NumDays = Range(sAdd2).Value - Range("$BA$12").Value 'Performs a sort of modulus Do While NumDays 7 NumDays = NumDays - 7 Loop ActiveSheet.Unprotect ' Determines weekday If NumDays = 1 Then Range(sAdd3).Value = "Monday" ElseIf NumDays = 2 Then Range(sAdd3).Value = "Tuesday" ElseIf NumDays = 3 Then Range(sAdd3).Value = "Wednesday" ElseIf NumDays = 4 Then Range(sAdd3).Value = "Thursday" ElseIf NumDays = 5 Then Range(sAdd3).Value = "Friday" ElseIf NumDays = 6 Then Range(sAdd3).Value = "Saturday" Else Range(sAdd3).Value = "Sunday" End If ActiveSheet.Protect End If End If End Sub If you have any ideas for simplifying the code please let me know. John |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event Problem
Hi John,
For some reason the change event will only run once. That is because your code disables events, but fails to restore the setting. If Application settings are to be changed, it is advisable to include the restoration of the settings in an error handler, so that ifr the code errors fpr any reason, the original setting will be applied. Additionally, the code responds to changes in AQ3 to change the sheets name; this action would not trigger an event and, therfore, it is unnecessary to disable events in this portion of your code. You have not declared the variable NumDays; it is good practice, and strongly recommended, that all variables be explicitly declared. Given the numerous If ... ElseIf clauses, I have replaced these with a Select Case structure which, in my opinion, is preferable. Addressing these points, try therefo '============= Private Sub Worksheet_Change(ByVal Target As Range) Const sAdd1 As String = "AQ3" Const sAdd2 As String = "AP6" Const sAdd3 As String = "AP8" Dim NumDays As Long On Error GoTo XIT If Not Intersect(Range(sAdd1), Target) Is Nothing Then MsgBox "ONE" If Target.Value < "" Then ActiveSheet.Name = "Report " & Range(sAdd1).Value End If End If If Not Intersect(Range(sAdd2), Target) Is Nothing Then If Target.Value < "" Then MsgBox "TWO" Application.EnableEvents = False 'Determines number of days between two dates NumDays = Range(sAdd2).Value - Range("$BA$12").Value 'Performs a sort of modulus Do While NumDays 7 NumDays = NumDays - 7 Loop ActiveSheet.Unprotect ' Determines weekday With Range(sAdd3) Select Case NumDays Case 1: .Value = "Monday" Case 2: .Value = "Tuesday" Case 3: .Value = "Wednesday" Case 4: .Value = "Thursday" Case 5: .Value = "Friday" Case 6: .Value = "Saturday" Case Else: .Value = "Sunday" End Select End With ActiveSheet.Protect End If End If XIT: Application.EnableEvents = True End Sub '<<============= --- Regards, Norman "mastermind" wrote in message oups.com... For some reason the change event will only run once. I don't know what would cause that to happen, but that is what it is doing. Do you know of anything that would cause that, or perhaps how to correct that problem? The full section of code I now have is: Private Sub Worksheet_Change(ByVal Target As Range) Const sAdd1 As String = "AQ3" Const sAdd2 As String = "AP6" Const sAdd3 As String = "AP8" If Not Intersect(Range(sAdd1), Target) Is Nothing Then If Target.Value < "" Then Application.EnableEvents = False ActiveSheet.Name = "Report " & Range(sAdd1).Value End If End If If Not Intersect(Range(sAdd2), Target) Is Nothing Then If Target.Value < "" Then Application.EnableEvents = False 'Determines number of days between two dates NumDays = Range(sAdd2).Value - Range("$BA$12").Value 'Performs a sort of modulus Do While NumDays 7 NumDays = NumDays - 7 Loop ActiveSheet.Unprotect ' Determines weekday If NumDays = 1 Then Range(sAdd3).Value = "Monday" ElseIf NumDays = 2 Then Range(sAdd3).Value = "Tuesday" ElseIf NumDays = 3 Then Range(sAdd3).Value = "Wednesday" ElseIf NumDays = 4 Then Range(sAdd3).Value = "Thursday" ElseIf NumDays = 5 Then Range(sAdd3).Value = "Friday" ElseIf NumDays = 6 Then Range(sAdd3).Value = "Saturday" Else Range(sAdd3).Value = "Sunday" End If ActiveSheet.Protect End If End If End Sub If you have any ideas for simplifying the code please let me know. John |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event Problem
Hi John,
I should have added that, as you have disabled events, you will need to restore this setting: in the immediate window type Application.EnableEvents = True and hit the enter key. --- Regards, Norman |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event Problem
Hi John,
Please remove the lines: MsgBox "ONE" and MsgBox "TWO" These represent test lines which I should have deleted. --- Regards, Norman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MsgBox in Enter event causes combobox not to run Change event | Excel Programming | |||
Change Event Problem | Excel Programming | |||
Problem w/ worksheet change event | Excel Programming | |||
Worksheet Change Event Problem | Excel Programming | |||
change event/after update event?? | Excel Programming |