Pleeeeease Help

[seljo]

Ok, Here is my problem, I am wanting to put a time stamp in cell D5 when cell B5 is altered, so I have that part. I was able to do this with a VBA I got from this site (Thanks to pennysaver and everyone else). Now the problem is that I want to do this same procedure again in the same spread sheet but this time when cell E5 is altered cell F5 denotes the change. I have tried entering in the same VBA code twice denoting the changes in range and offset but I get an error message of Ambiguous name detected: Worksheet_Change. So I changed the title to Monday_change, which is the title to one of my tabs, which gets rid of the error message but then the spread sheet does nothing when information is changed in cell E5. Any help here would be greatly appreciated. By the way here is the code I am currently using.

Private Sub Worksheet_Change(ByVal Target As Range) 
  Dim keyRange As Range 
    Set keyRange = Range("b5:b44") 
    If Not Intersect(keyRange, Target) Is Nothing Then 
      ActiveSheet.Unprotect "password" 
        Target.Offset(0, 2) = Time 
      ActiveSheet.Protect "password" 
    End If 
End Sub 

Private Sub Monday_Change(ByVal Target As Range) 
  Dim keyRange As Range 
    Set keyRange = Range("e5:e44") 
    If Not Intersect(keyRange, Target) Is Nothing Then 
      ActiveSheet.Unprotect "password" 
        Target.Offset(0, 1) = Time 
      ActiveSheet.Protect "password" 
    End If 
End Sub

 

[Joe Was]

You can only have one Event of a type in a workbook. You can never have more than one Sub with the same name.

To make an Event work on more than one thing you need to modify the code to work with more than one test!

 

[Smitty]

Does this do it for you:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> keyRange <SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#00007F">Set</SPAN> keyRange = Range("B5:B44, E5:E44")
        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(keyRange, Target) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        ActiveSheet.Unprotect "password"
            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Column
                <SPAN style="color:#00007F">Case</SPAN> 2  <SPAN style="color:#007F00">'   Column B</SPAN>
                    Target.Offset(, 2) = Time
                <SPAN style="color:#00007F">Case</SPAN> 5  <SPAN style="color:#007F00">'   Column E</SPAN>
                    Target.Offset(, 1) = Time
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
        ActiveSheet.Protect "password"
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty

 

[seljo]

Got it

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim keyRange As Range
    Set keyRange = Range("b5:b44,e5:e44")
    If Not Intersect(keyRange, Target) Is Nothing Then
      ActiveSheet.Unprotect "password"
        Target.Offset(0, 1) = Time
      ActiveSheet.Protect "password"
    End If
End Sub

EDIT: Added Code tags - Smitty

 

[dcardno]

You can only have one "Worksheet_Change" event routine (I don't know what "Monday_Change" will respond to...)

You could try something like:

Private Sub Worksheet_Change(ByVal Target As Range) 
Dim keyRange As Range 
Set keyRange = union(Range("b5:b44"),Range("e5:e44")) 
If Not Intersect(keyRange, Target) Is Nothing Then 
ActiveSheet.Unprotect "password" 
If target.column = 2 Then
   Target.Offset(0, 2) = Time
Else
   Target.Offset(0, 1) = Time
End If
ActiveSheet.Protect "password" 
End If 
End Sub