Argument not optional

rjplante

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
574
Office Version
  1. 365
Platform
  1. Windows
I have the code below which is in the worksheet code window. I also have another macro the runs to clear out this form (macro A). When I try to run macro A I get a "compile error - argument not optional" crash. The line highlighted in yellow appears in red below. Also the union in this line is highlighted with the system highlight color and that appears in blue below.

What is wrong with the code?

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
Dim d As Range
Dim e As Range
Dim f As Range
Dim g As Range
Dim RngA As Range
Dim RngB As Range
Dim RngC As Range
Dim RngD As Range

'Sets the cells assigned to RngA
Set RngA = Union(Range("I11), Range(L37:L39"))

'Sets the cells assigned to RngB
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"))

'Sets the cells assigned to RngC
Set RngC = Union(Range("E3:G3"), Range("L5:Q5"), Range("AD5:AF5"), _
Range("AD11:AF11"), Range("AD15:AF15"), Range("L26:R26"), Range("L27:R27"), _
Range("L30:R30"), Range("L31:R31"), 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"))

'Sets the cells assigned to RngD
Set RngD = 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"))

'Sets the target range of cells to monitor for changes.
Set d = Intersect(RngA, Target)
Set e = Intersect(RngB, Target)
Set f = Intersect(RngC, Target)
Set g = Intersect(RngD, Target)

'Sets conditions under which to process changes.
If Not d Is Nothing Then  ' process cells in d

    '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.
    
        If Target.Cells(1, 1) = d Then
            ActiveCell.Offset(0, 5).Value = Format(PreviousValue, "hh:mm")  'Enters the previous value of the cell.
            ActiveCell.Offset(0, 6).Value = Format(Target.Value, "hh:mm")  'Enters the new value of the cell.
        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
    
ElseIf Not e Is Nothing Then  ' process cells in e
    
    '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

        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  '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.
    
        If Target.Cells(1, 1) = e Then
            ActiveCell.Offset(0, 5).Value = Format(PreviousValue, "mm/dd/yy")  'Enters the previous value of the cell.
            ActiveCell.Offset(0, 6).Value = Format(Target.Value, "mm/dd/yy")  'Enters the new value of the cell.
        End If
        'Enters the password to protect the workbook.
        ThisWorkbook.ActiveSheet.Protect Password:="bioe1025"
    
        'Hides the Audit Log Conicals 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
    
ElseIf Not f Is Nothing Or Not g Is Nothing Then  ' process cells in f and h
  
    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  
        ActiveCell.Offset(0, 5).Value = PreviousValue  
        ActiveCell.Offset(0, 6).Value = Target.Value  
    
        ThisWorkbook.ActiveSheet.Protect Password:="bioe1025"
    
        'Hides the Audit Log Conicals 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 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
Set RngA = Union(Range("I11), Range(L37:L39"))
Should be
Set RngA = Union(Range("I11"), Range("L37:L39"))

Hope that helps.
 
Upvote 0
The argument not optional error comes from this line:

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

... which should be

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

I didn't look at the rest of the code.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Not running code
Replies
0
Views
212
MagicCarpet
M
M
  • Question Question
Using VBA code in a protected excel sheet
Replies
2
Views
539
MagicCarpet
M
J
  • Question Question
VBA Macros running slow out of nowhere
Replies
4
Views
3K
Tanquen
T
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
N
  • Question Question
AutoFilter Field Macro Returns a Header Row only
2
Replies
11
Views
206
MARK858
MARK858

Forum statistics

Threads
1,224,521
Messages
6,179,285
Members
452,902
Latest member
Knuddeluff

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