Getting code to run!

[Colleen45]

I have a problem getting this code to work

When ever one of the two names are selected the macro is supposed to run

code
Private Sub Worksheet_Change1(ByVal Target As Range)
If Target.Address(False, False) = "H6" Then
Select Case Target.Value
Case "Camps Bay", "Tokai": Call unhide_rows
End Select
End If
End Sub
/code

 

[Andrew Poulsom]

Remove the 1 in:

Private Sub Worksheet_Change1(ByVal Target As Range)

You can only have one Worksheet_Change event procedure and you certainly can't create more by changing the declaration.

 

[Colleen45]

Remove the 1 in:

Private Sub Worksheet_Change1(ByVal Target As Range)

You can only have one Worksheet_Change event procedure and you certainly can't create more by changing the declaration.

thanks Andrew,
I have already have code under procedure, and when i ran the code, it gave me ambiguous name errors.
if this is the code, that i have now, how would I merge the two (from my previous submission) to form one, these is where my vba knowledge is not at its best

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "Z6" Then
If Target.Value <> Target.Offset(-1).Value Or Target.Offset(-1).Value = "" Then
Application.EnableEvents = False
Target.ClearContents
Application.Goto reference:=Range("H6"), scroll:=True
MsgBox "Please choose a value then enter the same in Z6", vbExclamation
Application.EnableEvents = True
End If
End If
End Sub

 

[Andrew Poulsom]

Just add more code at the end:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(False, False) = "Z6" Then
        If Target.Value <> Target.Offset(-1).Value Or Target.Offset(-1).Value = "" Then
            Application.EnableEvents = False
            Target.ClearContents
            Application.Goto reference:=Range("H6"), Scroll:=True
            MsgBox "Please choose a value then enter the same in Z6", vbExclamation
            Application.EnableEvents = True
        End If
    ElseIf Target.Address(False, False) = "H6" Then
        Select Case Target.Value
            Case "Camps Bay", "Tokai": Call unhide_rows
        End Select
    End If
End Sub

 

[Colleen45]

Thanks Andrew,

I've done this and the macro is still not being called up, just wondering if "else" need to be there, as they are 2 seperate bits of code?

 

[Andrew Poulsom]

The code works fine for me (there is an ElseIf in there). Maybe events are disabled, so go to the Immediate window in the VBE, type:

Application.EnableEvents = True

and press Enter.

 

[Colleen45]

The code works fine for me (there is an ElseIf in there). Maybe events are disabled, so go to the Immediate window in the VBE, type:

Application.EnableEvents = True

and press Enter.

Let me approach this from the beginning:
If any of the names from list EK18:EK58 have been selected in H6, I would like rows A11:A14 to unhide.
Maybe this would makes things simpler

 

[Andrew Poulsom]

Isn't your event procedure firing when you change Z6 or H6?

 

[Colleen45]

Thats correct, when H6 name is selected the event does not fire, Z6 is out of the pic for now
and I did "Application.EnableEvents = True"

 

[Andrew Poulsom]

Did you try what I suggested in post #6?