VBA Worksheet Change Event is firing unexpectedly

[xDear]

Hello everyone,

I am looking for help with a VBA Worksheet Change Event.

The following code worked flawlessly until I attempted to insert a row at row 21. It is designed to activate code to display other sheets that are related to the process that is selected when the dropdown is changed in cell A1 and I thought I had error issues handled. I am able to insert text in other cells, choose dropdown selections in data validated cells, all without issue. I am confused as to why trying to insert a row somewhere other than the designated trigger cell (A1) is producing this error (Runtime error '13': Type mismatch). The error occurs at the line stating:

If Target = Range ("A1") Then

This workbook is in Excel 2010 running on Windows 7. The subroutine is in the Sheet1 worksheet I would greatly appreciate any help that someone might offer; so far I have not found any information about why this might happen.

The complete subroutine is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)

'This subroutine determines if a change has been made to the LOB Process drop down on cell A1
'and deletes the existing "Scorecard Rules" worksheet, if it exists

'Screen updating and Display Alerts are turned off to speed up process
Application.ScreenUpdating = False
Application.DisplayAlerts = False


'Determine if change was made to cell A1

If Target = Range("A1") Then

'Error handling code is employed if there is no Sheet named "Scorecard Rules" found
On Error GoTo NotFound

'If a Sheet named "Scorecard Rules" already exists it is deleted

Sheets("Scorecard Rules").Select
ActiveWindow.SelectedSheets.Delete

Sheets("Reporting").Select

'Error handling code
NotFound:
Sheets("Reporting").Select

'Once there is no "Scorecard Rules" sheet the IDScrCrd subroutine (Module 1) is called
' to generate the correct sheet which corresponds to the LOB Process chosen in cell A1

IDScrCrd

End If

'Turn the Display Alerts and ScreenUpdating function back on
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[Joe4]

Place this row at the top of your code, and it should handle that particular issue:

    If Target.Count > 1 Then Exit Sub

 

[Jonmo1]

This is an incorrect conclusion

'Determine if change was made to cell A1

If Target = Range("A1") Then

That does not determine if a change was made to A1.
It actually only determines if the value of the cell that was changed (Target) is equal to the value in A1.

So Say in A1 you have Hello

If I type Hello in G32
Then G32 is the Target of the Worksheet_Change event code.
So G32=A1 is TRUE


The method to determine if the cell that changed was A1 is as follows

If Target.Address = "$A$1"

Or

If Not Intersect(Target, Range("A1")) Is Nothing Then

 

[Joe4]

Good catch, Jonmo1.
Can't believe I looked right past that...

 

[xDear]

Thank you Jonmo1 and Joe4!!
So much more for me to learn about VBA.
By replacing
If Target = Range("A1") Then
with
If Target.Address = "$A$1"
a number of other oddities were also resolved, such as not being able to use the "Delete" key in other cells containing "filler" text.

Can additional code be placed in the Worksheet_Change Event subroutine without impacting what is already there?
I have found a subroutine to direct the tab order of the fields that require data to be entered, but it is also tied to the Worksheet_Change event.

If it can be done, where would the best place to insert it be?

 

[Jonmo1]

You're welcome.

There can be only 1 Worksheet_Change event code for a sheet.
They can be merged together into one, but there's no rulebook on how to do it.
It will be on a case by case basis how you combine them, depending on what the codes do and how they're written.

If you post both codes seperately, we can try to combine them into 1.
And that would probably be worthy of a New Thread.

 

[xDear]

Thanks again Jonmo1. I will post a new thread, Combining two processes under one Worksheet Change Event.