Invalid property assignment

rjplante

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
574
Office Version
  1. 365
Platform
  1. Windows
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

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

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
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". The line in red is what is highlighted when I choose debug. But the word Union, highlighted in blue is also highlighted in the program default color.

I have looked over the union formatting and I think I have accounted for all commas and quotes and the code seems to be correct unless there is a max limit to the number of ranges that can be defined by a union.

Thanks for the help,

Robert

Rich (BB code): 
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
 
Upvote 0
Union can only take 30 arguments.
 
Upvote 0
Thanks Rory. I have made the changes highlighted below, and now the macro doesn't crash, but it doesn't run and record the changes either. This macro is saved in the worksheets code window. I just don't know what it is I am doing wrong with this code, that I can't get it to execute when a change is made to the other cells that don't contain a time value format.

Is it a problem having more than one target intersect? Thanks for the suggestions.

Thanks,

Robert

Rich (BB code): 
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

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


'   Sets the target range of cells to monitor for changes.
'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"))

Set RngG = Union(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)
Set h = Intersect(RngG, 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

    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) = e Then
        ActiveCell.Offset(0, 5).Value = Format(PreviousValue, "mm/dd/yy")  
        ActiveCell.Offset(0, 6).Value = Format(Target.Value, "mm/dd/yy")  
    ElseIf Target.Cells(1, 1) = f Or h Then
        ActiveCell.Offset(0, 5).Value = PreviousValue  
        ActiveCell.Offset(0, 6).Value = Target.Value  
    End If

    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
 
Upvote 0
Your sub exits if the changed cell is not in rngA - Union(Range("I11"), Range("L37:L39"))
Is that your issue?
Note: you don't need Union there:
Code: 
Set rngA = Range("I11,L37:L39")
does the same thing.
 
Upvote 0
Your sub exits if the changed cell is not in rngA - Union(Range("I11"), Range("L37:L39"))
Is that your issue?
Note: you don't need Union there:
Code: 
Set rngA = Range("I11,L37:L39")
does the same thing.
 
Upvote 0
You need something more like:
Code: 
If not d is nothing then
   ' process cells in d
elseif not e is nothing then
   ' process cells in e
'etc.
 
Upvote 0
I just changed the line If d is nothing from code to comment and now it crashes on the line highlighted below with the "Run-Time error '424' Object Required" error message. I made a change to a cell that is not is d.

It seems I am only making it worse.


Rich (BB code): 
'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
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Using XLOOKUP in VBA to lookup value underneath specific header name (columns can change depending on worksheet)
Replies
0
Views
163
Hailee994
H
M
  • Question Question
Using VBA code in a protected excel sheet
Replies
2
Views
539
MagicCarpet
M
M
  • Question Question
Not running code
Replies
0
Views
212
MagicCarpet
M
K
  • Locked
  • Question Question
Vba code for User history log which is giving me a run-time error "13" mismatch
Replies
2
Views
165
Fluff
Fluff
J
  • Question Question
Copy Picture - VBA
2
Replies
10
Views
202
Logit
Logit

Forum statistics

Threads
1,224,527
Messages
6,179,348
Members
452,907
Latest member
Roland Deschain

Share this page

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
Back
Top