excel vba runtime

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA - Run-time error '1004'

Excel VBA - Run-time error '1004'

resolvedResolved · High Priority · Version 2010

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 courses

 

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Closing Multiple Open Worksheets At Once

When 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.

Close All menu option is only displayed when Shift key is down

View all Excel hints and tips


Server loaded in 0.07 secs.