Ambiguous worksheet change

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello
I created the code below but it does not work - gives the error of
"Ambiguous name detected: worksheet_Change"

so, this is what I want .... for this worksheet only
if cell B 29 equals "Spec" or "blanket" then unhide rows 35 thru 37 if cell
B29 equals "Biology" then unhide row 34

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("B29").Address Then
If Target = "Spec" Or Target = "Blanket" Then
Rows("35:37").Select
Selection.EntireRow.Hidden = False
If Target = "Biology" Then
Rows("34").Select
Selection.EntireRow.Hidden -False
End If
End If
End If

End Sub
thank you so much
 
You have two procedures with the same name in that module.

Depending on what they do, you can delete one (maybe it's not useful
anymore???). Or you'll have to combine the code into one procedure.

And watch your typing. I bet the minus in this line was supposed to be an equal
sign:
Selection.EntireRow.Hidden -False
 
Combine your TWO events. I would have written this this way.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$29" Then Exit Sub

If Target = "Spec" Or Target = "Blanket" Then _
Rows("35:37").Hidden = False

If Target = "Biology" Then Rows("34").Hidden = False
End Sub
 
Thank You Dave and Don
I combined both events - Now I do not get the error message but the second
part ,or the part that I added to the procedure does not work. If cell B29
says "Biology" (this is from a drop down validation list) row 34 does not
unhide same with if cell B29 says Yes or Lab rows 35 thru 37 do not unhide.
Your help is really appreciated I've been doing this for 6 hours Again
Thanks a million
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("N29").Address Then
If Target = "Yes" Or Target = "Lab " Then
Range("O32").Select
Else
Range("Q29").Select


If Target.Address <> "$B$29" Then Exit Sub
If Target = "Spec" Or Target = "Blanket" Then _
Rows("35:37").Hidden = False

If Target = "Biology" Then Rows("34").Hidden = False
 
Maybe...

Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$N$29" Then
If Target.Value = "Yes" _
Or Target.Value = "Lab " Then
Range("O32").Select
Else
Range("Q29").Select
End If
ElseIf Target.Address = "$B$29" Then
If Target.Value = "Spec" _
Or Target.Value = "Blanket" Then
Rows("35:37").Hidden = False
ElseIf Target.Value = "Biology" Then
Rows("34").Hidden = False
End If
End If

End Sub
 
I'm a bit confused by your explanation. Send me a workbook with clear
explanation of what you want and your efforts so far.
 
Back
Top