Vincent88
Active Member
- Joined
- Mar 5, 2021
- Messages
- 382
- Office Version
- 2019
- Platform
- Windows
- 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