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

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

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

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

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

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