Static Date - across several sheets

[nutrastat]

<code>Hi,

Having searched </code><code>MANY </code><code>previous threads I have finally found the following code that was submitted by phamtom1975 back in Dec 2003 and it works wonderfully, where if 1 is entered into A1 then the current date and time is put into E1.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If [A1] = "1" Then
'If A1 is formatted as text, then you will need to change "1" to "100%"
If [E1] = "" Then
[E1] = Now
End If
End If
End Sub

</code><code>Then, even if you delete the number in A1 the date and time will not change in E1 - great. Can anybody help me develop this, as I have never messed around with VB before to get it to allow me to use this same code across several worksheets</code><code>?

On each sheet (Laundry, Bars and Garbage) there are 5 rows with columns headed Name, Position and Date/Time. Therefore, on the Laundry sheet when somebody puts in their Name & Position, the Date/Time row will automatically insert the date & time. The same goes for rows 2 to 5 on this sheet, and for rows 1 to 5 on the remaining two sheets (i.e. Bars & Garbage).

If possible I would like to be able to have just the one formula to work with all 20 sheets that we use, as I have only used 3 worksheet titles as an example. Or, will I have to place smaller sections of code for each worksheet in the VB editor?

Any help you can give would be very much appreciated. Thanks

Graham
</code><code>

</code><code>
</code>

 

[VoG]

Hello and welcome to MrExcel.

The following will place a time stamp in column C when anything is entered in column A in any sheet. If you need to exclude any sheets please list their names and the code can be amended.

Right click the Excel logo just to the left of File on the menu bar, select View Code and paste in:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Then
    With Target.Offset(, 2)
        If .Value = "" Then
            Application.EnableEvents = False
            .Value = Now
            Application.EnableEvents = True
        End If
    End With
End If
End Sub

 

[nutrastat]

Wow,

Thanks for such a prompt response!

This code will work, but unfortunately it is too broad for me.

As I do not know VBA the difficulty I have is trying to be quite specific with the columns/rows where the Name, Position, date/time will appear, especially as the areas will be different on each sheet.

If it is not too much trouble, please could you just get it working for Laundry, Bars & Garbage sheets? Laundry Names will appear D6:D10, Position E6:E10 and the date/times will appear F6:F10. However, on the Bars they start at C4:C8 and Garbage B8:B12. This also happens on the other sheets, but if I can see how the code works, I should be able to make the changes.

Thanks in anticipation of any help that you can give.

Br

Graham

 

[VoG]

That gets a tad more difficult

Assuming that in all cases you want the timestamp to appear in column F try

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim ary1, ary2, Idx
ary1 = Array("Laundry", "Bars", "Garbage")
ary2 = Array("D6:D10", "C4:D8", "B8:B12")
If Target.Count > 1 Then Exit Sub
Idx = Application.Match(Sh.Name, ary1, 0)
If IsError(Idx) Then Exit Sub
If Not Intersect(Target, Range(ary2(Idx - 1))) Is Nothing Then
    With Range("F" & Target.Row)
        If .Value = "" Then
            Application.EnableEvents = False
            .Value = Now
            Application.EnableEvents = True
        End If
    End With
End If
End Sub

 

[nutrastat]

Hi,

I DO thank you very much for the help you have given me so far. Would you be kind enough to help me with final two minor changes...

Could the date/time only be entered if both the Name AND Position are entered. At the moment it only works on the name field.

Still using your current code defining the first array (Sheet Name), second array (name fields), could you please remove the 'F' field assumption. Instead could you always place it in the second columns from the name field (array 2 column) as the position column will be next and THEN the date/Time will appear.

In this way it will help me tremendously as I can then simply enter the sheet name, the position of the name column, knowing that the Date/Time will appear in the second column to the right.

Thanks again,

Graham

 

[VoG]

Try

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim ary1, ary2, ary3, Idx
Dim Filled As Boolean, Myoffset As Integer
ary1 = Array("Laundry", "Bars", "Garbage")
ary2 = Array("D6:E10", "C4:D8", "B8:C12")
ary3 = Array(4, 3, 2)
If Target.Count > 1 Then Exit Sub
Idx = Application.Match(Sh.Name, ary1, 0)
If IsError(Idx) Then Exit Sub
If Not Intersect(Target, Range(ary2(Idx - 1))) Is Nothing Then
    If Target.Column = ary3(Idx - 1) Then
        Filled = Target.Value <> "" And Target.Offset(, 1) <> ""
        Myoffset = 2
    Else
        Filled = Target.Value <> "" And Target.Offset(, -1) <> ""
        Myoffset = 1
    End If
    If Filled Then
        With Target.Offset(, Myoffset)
            If .Value = "" Then
                Application.EnableEvents = False
                .Value = Now
                Application.EnableEvents = True
            End If
        End With
    End If
End If
End Sub

 

[nutrastat]

VoG,

I do not know how to adequately thank you - but thanks anyway.

There is no way that I would have been able to put together this code; especially in the short time that it has taken you.

As I continue with this new project there may be another odd bit of help that may be needed, and I do hope that I will be able to call upon your skills once again.

Cheers, and have a good weekend.

Br

Graham