subscript out of range when try write wrong sheet name in cell

[abdelfattah]

hi
I want fixing this error subscript ou of range when write error sheet name in cell A2
my goal if the sheet name is wrong then should populate MsgBox "please check the right sheet name"
and if sheet name is right then should activate sheet .
here is my code

Sub activate_sh()
Dim ws As Variant
For Each ws In Array("a", "b", "d")
If Worksheets(Worksheets("ACTIVATE").Range("A2").Text) <> ws.Name Then
MsgBox "please check  the  right sheet name"
Else
Worksheets(Worksheets("ACTIVATE").Range("A2").Text).Activate
End If
Next
End Sub

 

[Fluff]

How about

Sub abdelfattah()
   Dim Ary As Variant, Chk As Variant
   
   Ary = Array("a", "b", "d")
   With Worksheets("ACTIVATE").Range("A2")
      Chk = Application.Match(.Text, Ary, 0)
      If IsError(Chk) Then
         MsgBox "please check  the  right sheet name"
      Else
         Worksheets(.Text).Activate
      End If
   End With
End Sub

 

[abdelfattah]

that's awesome , could do that by sheet change event instead of button,please?

 

[Fluff]

Do you just want it to work on A2?

 

[abdelfattah]

yes

 

[Fluff]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Ary As Variant, Chk As Variant
   
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "A2" Then
      Ary = Array("a", "b", "d")
      Chk = Application.Match(Target.Value, Ary, 0)
      If IsError(Chk) Then
         MsgBox "please check  the  right sheet name"
      Else
         Worksheets(Target.Value).Activate
      End If
   End If
End Sub

 

[abdelfattah]

thanks I want clearing A2 after show message . I try adding as bold line but the message shows many times .

MsgBox "please check  the  right sheet name"
        Target.Value = "": Exit Sub

 

[Fluff]

Any thing else that you "forgot" to ask initially?

 

[abdelfattah]

sorry !! this is the last thing .

 

[Fluff]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Ary As Variant, Chk As Variant

   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "A2" Then
      Ary = Array("a", "b", "d")
      Chk = Application.Match(Target.Value, Ary, 0)
      If IsError(Chk) Then
         MsgBox "please check  the  right sheet name"
      Else
         Worksheets(Target.Value).Activate
         Application.EnableEvents = False
         Target.Value = ""
         Application.EnableEvents = True
      End If
   End If
End Sub