Worksheet code not firing

[rjplante]

I have the code listed below, and only the first half is firing. The issue if that I originally had the first half of the macro on the change event worksheet code window. I learned that you can only have one change event macro listing per page, so I attempted to merge the two sections of code. The problem I am having is this, This sheet is a replication of a word document, so in order to make the Excel document line up with the look of the Word document, I have all the cells only 2.0 pixels wide. This means the every field for data entry is a merged cell.

If I modify the time formats, the second part of the macro fires recording the change on the audit trail. It only appears as a decimal on the audit tracking page instead of a time format. I would fix this by inserting an If/Then.

If Activecell = intersect (Range(X),range(Y), Range(Z)) then

format(Target.cell(1,1).value, "[h]:mm" and format(PreviousValue, "[h]:mm") 

End if

I don't know if this is correct ot not but it is where I would start.

-------------------------
the second problem is that If I modify any of the other fields (groups of merged cells from 3 to 6 cells wide), the audit macro does not run. The time fields are all 3 cells wide. They all run the first half of the change macro, but not the second.

I was wondering of I have to specify all of the cells on my form in the intersect line, otherwise it is not looking for them to change.

Thanks for he help,

Robert

Dim PreviousValue

Private Sub Worksheet_Change(ByVal Target As Range)

'   Corrects the time format entered as 1245 to 12:45.

'   Unlocks the worksheet to make changes to protected cells.
ActiveSheet.Unprotect Password:="bioe1025"

'   Defines the variables.
Dim c As Range, d As Range

'   Sets the target range of cells to monitor for changes.
Set d = Intersect(Union(Range("I11"), Range("L37:L39")), Target)

'   Sets conditions under which macro should be aborted.
If d Is Nothing Then Exit Sub

'   Turns off the 'change event' monitoring for the target range, for the _
'   following activity by the macro.
Application.EnableEvents = False

'   Changes the cells contents based on the length of the text string entered.
'   It also formats the cells contents to be in hours:minutes. If the length of time is 10.5 hours,_
'   it will be displayed as 10:30 for 10 hours and 30 minutes.
For Each c In d
    If IsNumeric(c) And c <> "" Then
        If Len(c) > 4 Then
            c = Format(c, "00\:00\:00")
            c.NumberFormat = "[h]:mm:ss"
        Else
            c = Format(c, "00\:00")
            c.NumberFormat = "[h]:mm"
        End If
    End If
Next

'   Turns on the 'change event' monitoring for the target range.
Application.EnableEvents = True

'   Locks the worksheet to remove access to protected cells.
ActiveSheet.Protect Password:="bioe1025"

'====================================================

'If signed indicator is P then abort.
If ThisWorkbook.Sheets("QC5003.4A-1 FINAL QC CONICAL").Range("U46").Value <> "C" Then
    Exit Sub

ElseIf Target.Cells(1, 1).Value <> PreviousValue Then

    'Suspends display updates as the macro executes.
    Application.ScreenUpdating = False

    'Makes the Audit Log Bag worksheet visible and active.
    Sheets("Audit Log Conicals").Visible = True
    Sheets("Audit Log Conicals").Activate

    'Enters the password to unlock the worksheet and allow data entry.
    ThisWorkbook.ActiveSheet.Unprotect Password:="bioe1025"

    'Adds the username to the second column in the first available row.
    ThisWorkbook.Sheets("Audit Log Conicals").Cells(65000, 2).End(xlUp).Offset(1, 0).Value = Sheets("Intro Page").Range("R15").Value
    ThisWorkbook.Sheets("Audit Log Conicals").Cells(65000, 2).End(xlUp).Select
    ActiveCell.Offset(0, 1).Value = Date  'Enters the date to the next cell
    ActiveCell.Offset(0, 2).Value = Time  'Enters the time to the next cell.
    ActiveCell.Offset(0, 3).Value = Sheets("QC5003.1 PCB Worksheet CON-1").Name  'Enters the sheet name to the next cell.
    ActiveCell.Offset(0, 4).Value = Target.Address  'Enters the cell name to the next cell.
    ActiveCell.Offset(0, 5).Value = PreviousValue  'Enters the previous value of the cell.
    ActiveCell.Offset(0, 6).Value = Target.Value  'Enters the new value of the cell.

    'Enters the password to protect the workbook.
    ThisWorkbook.ActiveSheet.Protect Password:="bioe1025"
    
    'Hides the Audit Log Bag worksheet.
    ThisWorkbook.Sheets("Audit Log Conicals").Visible = False

    'Makes the QC5003.1 PCB Worksheet CON-1 the active sheet.
    ThisWorkbook.Sheets("QC5003.1 PCB Worksheet CON-1").Activate
    
    'Restores display updates
    Application.ScreenUpdating = True

End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PreviousValue = Target.Cells(1, 1).Value

End Sub