none
STATIC TIME STAMP IN MULTIPLE COLUMNS RRS feed

  • Question

  • I have a single spreadsheet for Monday - Friday.  There are multiple columns for each day.  It has been asked of me the following:

    When the customer name is typed for the first time in columns: E, T, AH, AV, BJ  rows 5-28 then the static date & time would appear in another column on the same row.  I would be adding columns (M, AB, AQ, BF, BU)  for date/time.  

    Example: Customer name typed in E5 the date time would show in M5 for Monday.  For Tuesday, it would be T5 with date in AB5, etc.

    I have very little experience with VBA and have searched the internet for a solution, there are many conditions and have tried for using solutions for just one column, but none have worked.  

    Thank you

    Linda


    woodlinda

    Tuesday, February 24, 2015 4:14 PM

Answers

  • You can have only one Worksheet_Change event procedures per worksheet. You have to combine the two Worksheet_Change event procedure into one:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Columns("C:BB")) Is Nothing Then
           Rows(Target.Row + 1).Hidden = False
        End If
        If Not Intersect(Range("E5:E28,T5:T28,AH5:AH28,AV5:AV28,BJ5:BJ28"), _
                Target) Is Nothing Then
            Application.EnableEvents = False
            Intersect(Range("E5:E28,T5:T28,AH5:AH28,AV5:AV28,BJ5:BJ28"), _
                Target).Offset(0, 8) = Now
            Application.EnableEvents = True
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by WoodLinda Tuesday, February 24, 2015 11:04 PM
    Tuesday, February 24, 2015 8:15 PM

All replies

  • Right-click the sheet tab.

    Select View Code from the context menu.

    Copy the following code into the module:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("E5:E28,T5:T28,AH5:AH28,AV5:AV28,BJ5:BJ28"), _
                Target) Is Nothing Then
            Application.EnableEvents = False
            Intersect(Range("E5:E28,T5:T28,AH5:AH28,AV5:AV28,BJ5:BJ28"), _
                Target).Offset(0, 8) = Now
            Application.EnableEvents = True
        End If
    End Sub

    This will enter the current date and time in the cell 8 columns to the right of the cell that has been changed.

    Save the workbook in a macro-enabled format (.xlsm, .xlsb or .xls but not .xlsx)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, February 24, 2015 5:20 PM
  • I have other codes, autosave/autoclose, message balloons, and the below code.  After copying your code an error message appears: Compile error:  ambiguous name detected:

    It highlighted the line Private sub.........

    I researched this error.  It said something already has the same name which I assume is the below code.  So I changed the word "change" to something else, but the code did not work.  Guess I need a little more help.

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, ActiveSheet.Columns("C:BB")) Is Nothing Then
      Rows(Target.ROW + 1).Hidden = False

        End If
    End Sub

      

    woodlinda

    Tuesday, February 24, 2015 6:20 PM
  • You can have only one Worksheet_Change event procedures per worksheet. You have to combine the two Worksheet_Change event procedure into one:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Columns("C:BB")) Is Nothing Then
           Rows(Target.Row + 1).Hidden = False
        End If
        If Not Intersect(Range("E5:E28,T5:T28,AH5:AH28,AV5:AV28,BJ5:BJ28"), _
                Target) Is Nothing Then
            Application.EnableEvents = False
            Intersect(Range("E5:E28,T5:T28,AH5:AH28,AV5:AV28,BJ5:BJ28"), _
                Target).Offset(0, 8) = Now
            Application.EnableEvents = True
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by WoodLinda Tuesday, February 24, 2015 11:04 PM
    Tuesday, February 24, 2015 8:15 PM