98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA - Run-time error '1004'
Excel VBA - Run-time error '1004'
Resolved · High Priority · Version 2010
Chris has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel VBA Advanced course
Excel Forecasting and Data Analysis course
Excel VBA - Run-time error '1004'
Hi guys
I have created a couple of sheet event macros and two of them seem to be conflicting with each other.
The first looks to the right of the cell clicked, and then checks if there is a duplicate of that cell above or below. If there is a duplicate it deletes the entire row, if not it shows a message box saying "you can't delete the last copy".
The second set of macros are macros that unhide and hide a specific set of columns to the right of the sheet.
The code and error message is below.. Someone help please!!
Error message:
"Run-time error'1004':
Method 'Intersect' of object '_Global' failed"
Code:
'delete macro
If Selection.Count = 1 Then
If Not Intersect(Target, Range("B15:B32")) Is Nothing Then
If ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(1,1).Value Or ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(-1, 1).Value Then
ActiveCell.EntireRow.Select
Selection.EntireRow.Delete
Range("a1").Select
Else
MsgBox "Cannot delete the only copy"
Range("a1").Select
End If
End If
End If
'unhide macro
If Selection.Count = 1 Then
If Not Intersect(Target, Range("S11")) Is Nothing Then
Columns("T:T").Select
Selection.EntireColumn.Hidden = False
Range("S1").Select
End If
End If
'hide macro
If Selection.Count = 1 Then
If Not Intersect(Target, Range("T11")) Is Nothing Then
Columns("T:T").Select
Selection.EntireColumn.Hidden = True
Range("S1").Select
End If
End If
The line highlighted yellow in debug mode is the "if not intersect..." line in the unhide macro, or hide macro, depending on which one is first in the module. Strangely, despite the error message, the action of the delete macro gets carried out perfectly, yet the error message still pops up.
RE: Excel VBA - Run-time error '1004'
Hi Chris,
Thank you for the forum question.
Yes it is normal that worksheets events can give us conflicts.
I managed to handle it by keep them by calling the events as you can see in the code below.
I hope this can help you.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call ChangeEvent1(Target)
Call ChangeEvent2(Target)
End Sub
Private Sub ChangeEvent1(ByVal Target As Range)
If Selection.Count = 1 Then
If Not Intersect(Target, Range("B15:B32")) Is Nothing Then
If ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(1, 1).Value Or ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(-1, 1).Value Then
ActiveCell.EntireRow.Select
Selection.EntireRow.Delete
Range("a1").Select
Else
MsgBox "Cannot delete the only copy"
Range("a1").Select
End If
End If
End If
Exit Sub
End Sub
Private Sub ChangeEvent2(ByVal Target As Range)
If Selection.Count = 1 Then
If Not Intersect(Target, Range("S11")) Is Nothing Then
Columns("T:T").Select
Selection.EntireColumn.Hidden = False
Range("S1").Select
End If
End If
'hide macro
If Selection.Count = 1 Then
If Not Intersect(Target, Range("T11")) Is Nothing Then
Columns("T:T").Select
Selection.EntireColumn.Hidden = True
Range("S1").Select
End If
End If
Exit Sub
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 20 Aug 2015: Automatically marked as resolved.
Training information:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Closing Multiple Open Worksheets At OnceWhen multiple Excel worksheets are opening, rather than performing a File > Close menu option multiple times, hold down the the SHIFT key and select the File > Close All menu option. |