Pleeeeease Help

seljo

New Member
Joined
Sep 29, 2006
Messages
12
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.

Code:
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Sep 29, 2006
Messages
12
Got it

Code:
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

Well-known Member
Joined
Nov 20, 2002
Messages
543
Office Version
  1. 2013
Platform
  1. Windows
You can only have one "Worksheet_Change" event routine (I don't know what "Monday_Change" will respond to...)

You could try something like:
Code:
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
 

Forum statistics

Threads
1,136,272
Messages
5,674,757
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top