Runtime error 1004

Vincent88

Active Member
Joined
Mar 5, 2021
Messages
375
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
Hi Guys, Please help to review what should be corrected when I run a code to create a new worksheet, this error message pop up. However a new worksheet is created anywhere. See the screenshot of error msg and debug.

VBA Code:
Private Sub Workbook_SheetBeforeRightClick(ByVal sh As Object, ByVal Target As Range, Cancel As Boolean)
      
   If sh.Name = "Data" Then Exit Sub
        
   If Target.CountLarge > 1 Then Exit Sub
  
   Application.DisplayAlerts = False
   Application.EnableEvents = False
   Application.ScreenUpdating = False
    
   'CREATE CONSECUTIVE MONTH WORKSHEET
    Select Case Target.Address
        Case "$A$1"
            Cancel = True
            AddNextMonth1
        
    'UPDATE THE LAST FIVE DAYS DATA
        Case "$A$2"
            Cancel = True
            FindLastCell_In_ColumnA4
            LastMonthData17
    End Select
    
'UPDATE USED NAMES AND CREATE DROPDOWN LIST TO COLUMN A
If Not Intersect(Target, Range("A3:A" & Range("A" & Rows.Count).End(xlUp).Row + 1)) Is Nothing Then 
 
  If Target.Offset(-1) <> "" Then
     Cancel = True
      
     Dim lrA As Long
     Dim colAarray As Object
     Dim clA As Range
     Dim SortedColA_array As Variant
  
    'Creating a array list
    Set colAarray = CreateObject("System.Collections.ArrayList")
    
    'Physical Source in Column L
    lrA = Range("A2").End(xlDown).Row
    For Each clA In Range("A3:A" & lrA)
          If Not colAarray.contains(clA.Value) Then colAarray.Add clA.Value
    Next clA
      
    colAarray.Sort
    SortedColA_array = colAarray.toarray
    
    'Output SortedColA_array to Sheet("Data") Column O
    Sheets("Data").Range("T2:T" & Sheets("Data").Range("T2").End(xlDown).Row).ClearContents
    Sheets("Data").Range("T2").Resize(UBound(SortedColA_array) + 1, 1).Value = Application.Transpose(SortedColA_array)

    End If

    'Compare Sheet("Data") Column N and Column O to find the Unused Names
     Const F = "TRANSPOSE(IF(ISNA(MATCH(S2:S#,T2:T¤,0)),S2:S#))"
 
        Dim V
        With Sheets("Data")
            V = .Cells(.Rows.Count, 21).End(xlUp).Row:  If V > 1 Then .Range("U2:U" & V).Clear
            V = Filter(.Evaluate(Replace(Replace(F, "#", .[S1].End(xlDown).Row), "¤", .[S1].End(xlDown).Row)), False, False)
            If UBound(V) > -1 Then .[U2].Resize(UBound(V) + 1).Value2 = Application.Transpose(V)
        End With
 
            Dim strSortedAgt As String
            Dim lr As Long
            
    'Create DropDown List to Column A
     lr = Sheets("Data").Range("U2").End(xlDown).Row
     strSortedAgt = "='Data'!$U$2:$U$" & lr
 
   With Target.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=strSortedAgt     
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
   End With
  
   End If
  
   Application.DisplayAlerts = True
   Application.EnableEvents = True
   Application.ScreenUpdating = True
    
End Sub
 

Attachments

  • error1004.png
    error1004.png
    11.6 KB · Views: 12
  • error1004code.png
    error1004code.png
    19.4 KB · Views: 12

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
2,050
Office Version
  1. 2016
Platform
  1. Windows
Try to add sh if it works.
If Not Intersect(Target, Range("A3:A" & Range("A" & sh.Rows.Count).End(xlUp).Row + 1)) Is Nothing Then
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,811
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I'd add sh to all the Range calls too:

Code:
If Not Intersect(Target, sh.Range("A3:A" & sh.Range("A" & sh.Rows.Count).End(xlUp).Row + 1)) Is Nothing Then
 
  • Like
Reactions: Zot
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,331
Messages
5,836,683
Members
430,444
Latest member
WrenchBoy

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
Top