Worksheet_change Error 91

[michaelg777]

First time writing worksheet_change code and I have gotten 2 codes to work, but I cannot get them to work together. The first piece of code is just to hide and unhide tabs, and it works until I paste the second piece of code which is to replace cell values when entered into a specific range. Also I get the error when I type one of the values outside of the defined range. Could someone please assist with what I am missing in my code to have both work at the same time?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("E1")) Is Nothing Then
Application.ScreenUpdating = False

Sheets("3 Year - Output").Visible = xlVeryHidden
Sheets("4 Year - Output").Visible = xlVeryHidden
Sheets("5 Year - Output").Visible = xlVeryHidden

Select Case Target.Value
Case "3 Year Output"
Sheets("3 Year - Output").Visible = True
Case "4 Year Output"
Sheets("4 Year - Output").Visible = True
Case "5 Year Output"
Sheets("5 Year - Output").Visible = True
Case Else
'All sheets remain hidden
End Select
Application.ScreenUpdating = True
End If

Dim rng2 As Range
Set rng2 = Intersect(Target, Range("$F$4:$G$100000"))
If rng2 = "0" Then
rng2.Replace what:="0", Replacement:="New York"
End If
If rng2 = "1" Then
rng2.Replace what:="1", Replacement:="New Jersey"
End If
"
End If
End Sub

 

[Comfy]

You cannot change workbook values using workbook/sheet events, Unless they are returned to a User Defined Function.

 

[michaelg777]

Thanks. So if i made a function first that replaced values, I could set the worksheet to trigger that function in those selected cells?

You cannot change workbook values using workbook/sheet events, Unless they are returned to a User Defined Function.

 

[Comfy]

No, a function will not be able to change values when enter by a user.

You will have to use another cell to display "New York" or "New Jersey"

E.G.

Excel 2010
	A	B
2	0	New York
3	1	New Jersey
4	0	New York
5	1	New Jersey
Sheet1

Cell Formulas
Range	Formula
B2	=CHOOSE(A2+1,"New York","New Jersey")

 

[Andrew Poulsom]

You cannot change workbook values using workbook/sheet events, Unless they are returned to a User Defined Function.

That is absolutely not true.

 

[Comfy]

That is absolutely not true.

My apologies, I must be confusing this with something else.

 

[michaelg777]

Yeah, I can make it work by itself but when I use the top code it draws an error if I use the first code or if I type a 0,1 in a cell not in rng2.

That is absolutely not true.

 

[Jonmo1]

That is absolutely not true.

+1


I think the problem is that you're not verifying that the cell which changed is within your specified range
The line I highlighted red does NOT actually verify if Target exists within Range("$F$4:$G$100000")
In reality, it basically says...
If Target does exist within Range("$F$4:$G$100000"), then set rng2 to be that intersecting cell.
Otherwise, rng2 remains to be a range object variable without a range assigned to it.

Dim rng2 As Range
 Set rng2 = Intersect(Target, Range("$F$4:$G$100000"))
 If rng2 = "0" Then
 rng2.Replace what:="0", Replacement:="New York"
 End If
 If rng2 = "1" Then
 rng2.Replace what:="1", Replacement:="New Jersey"
 End If

You have to do an If Not Intersect(...) Is Nothing
Like you did in the first part of the code.


In this case, I'd do it this way.

Dim rng2 As Range
 Set rng2 = Intersect(Target, Range("$F$4:$G$100000"))
 If Not rng2 Is Nothing Then
     If rng2 = "0" Then
         rng2.Replace what:="0", Replacement:="New York"
     End If
     If rng2 = "1" Then
         rng2.Replace what:="1", Replacement:="New Jersey"
     End If
 End If

 

[Comfy]

Again, Sorry Michael:

Application.EnableEvents = False
If Not Intersect(Target, Range("$F$4:$G$100000")) Is Nothing Then
Select Case Target.Value
    Case 0
        Target.Value = "New York"
    Case 1
        Target.Value = "New Jersey"
End Select
End If
Application.EnableEvents = True

 

[Jonmo1]

My apologies, I must be confusing this with something else.

Probably mixed that with the rule
"A Custom Function (UDF) cannot change values in cells if that function is called from a worksheet formula"