Invalid property assignment

[rjplante]

My code is listed below. I am trying to implement an audit trail and also run a macro that will change the time format entered into a select group of cells. I have generated multiple unions of cells, but I keep betting a compile error with the text "Wrong number of arguments or Invalid property assignment".

I don't know how to fix this issue.

Thanks for the help,

Robert

Dim PreviousValue

Private Sub Worksheet_Change(ByVal Target As Range)

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

ActiveSheet.Unprotect Password:="bioe1025"

Dim c As Range, d As Range

'==================================
Dim e As Range
Dim f As Range
Dim RngA As Range
Dim RngB As Range
Dim RngC As Range
'==================================

'Set d = Intersect(Union(Range("I11"), Range("L37:L39")), Target)

'=======================================
Set RngA = Union(Range("I11"), Range("L37:L39"))

Set RngB = Union(Range("L3:P3"), Range("I9:L9"), Range("S26:V26"), _
Range("S27:V27"), Range("AF29:AI29"), Range("S30:V30"), Range("AF30:AI30"), _
Range("S31:V31"))

Set RngC = Union(Range("E3:G3"), Range("L5:Q5"), _
Range("AD5:AF5"), Range("AT7"), Range("AD11:AF11"), Range("AD15:AF15"), _
Range("AT19"), Range("AT23"), Range("L26:R26"), Range("L27:R27"), Range("L30:R30"), Range("S30:V30"), _
Range("L31:R31"), Range("S31:V31"), Range("L33:N35"), _
Range("O33:Q35"), Range("R33:T35"), Range("U33:W35"), Range("X33:Z35"), _
Range("AA33:AC35"), Range("AD33:AF35"), Range("AG33:AI35"), Range("L36:N36"), _
Range("O36:Q36"), Range("R36:T36"), Range("U36:W36"), Range("X36:Z36"), _
Range("AA36:AC36"), Range("AD36:AF36"), Range("AG36:AI36"), Range("L40:N40"), _
Range("O40:Q40"), Range("R40:T40"), Range("U40:W40"), Range("X40:Z40"), _
Range("AA40:AC40"), Range("AD40:AF40"), Range("AG40:AI40"), Range("A46:N53"))
'===================================

Set d = Intersect(RngA, Target)
Set e = Intersect(RngB, Target)
Set f = Intersect(RngC, Target)

If d Is Nothing Then Exit Sub

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

Application.EnableEvents = True

ActiveSheet.Protect Password:="bioe1025"

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

If ThisWorkbook.Sheets("QC5003.4A-1 FINAL QC CONICAL").Range("U46").Value <> "C" Then
    Exit Sub

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

    Application.ScreenUpdating = False

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

    ThisWorkbook.ActiveSheet.Unprotect Password:="bioe1025"

    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
    ActiveCell.Offset(0, 2).Value = Time
    ActiveCell.Offset(0, 3).Value = Sheets("QC5003.1 PCB Worksheet CON-1").Name
    ActiveCell.Offset(0, 4).Value = Target.Address  'Enters the cell name to the next cell.
    
    If Target.Cells(1, 1) = d Then
        ActiveCell.Offset(0, 5).Value = Format(PreviousValue, "hh:mm")  
        ActiveCell.Offset(0, 6).Value = Format(Target.Value, "hh:mm")  
    ElseIf Target.Cells(1, 1) = f Then
        ActiveCell.Offset(0, 5).Value = PreviousValue  
        ActiveCell.Offset(0, 6).Value = Target.Value  
    End If
    
    'Enters the password to protect the workbook.
    ThisWorkbook.ActiveSheet.Protect Password:="bioe1025"
    
    ThisWorkbook.Sheets("Audit Log Conicals").Visible = False

    ThisWorkbook.Sheets("QC5003.1 PCB Worksheet CON-1").Activate
 
    Application.ScreenUpdating = True

End If

End Sub
'---------------------------------------------------

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

End Sub

 

[RoryA]

See my last post.

 

[rjplante]

Thanks Rory. I believe I finally got it to run well.