Activate sheet with cell value

[adamsm]

Hi,

How could I make the following code to activate the sheet that a user types on Cell G5.

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("G5").Value = "FALSE" Then


Worksheets("Mail").Activate
      End If
      
  
End Sub

Suppose if the user types "Mail" in cell G5 I want the sheet Mail to be activated. If the user types "Mysheet" I want the referenced sheet to activated.

Any help on this would be kindly appreciated.

Thanks in advance.

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "G5" Then
    On Error Resume Next
    Sheets(Target.Value).Select
    On Error GoTo 0
End If
End Sub

 

[Sektor]

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not IsEmpty(Range("G5")) Then
        Worksheets(Range("G5")).Activate
    End If
End Sub

 

[adamsm]

Thanks for the help. The first code works better. With the second one im getting debug message. Suppose if I want to extend the range from A5 to G5 how should I change the range of the code for that?

Any help would be kindly appreciated.

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A5:G5")) Is Nothing Then
    On Error Resume Next
    Sheets(Target.Value).Select
    On Error GoTo 0
End If
End Sub

 

[Sektor]

Ah, yes. VoG's code is more reliable. I skipped error checking 'cause assumed that sheets' names are pre-defined.

 

[adamsm]

If I want to clear the contents after the required sheet is activated where should I insert the line Sheets(Target.Value).ClearContents

or is this a wrong line of code?

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A5:G5")) Is Nothing Then
    On Error Resume Next
    With Sheets(Target.Value)
        .UsedRange.ClearContents
        .Select
    End With
    On Error GoTo 0
End If
End Sub

 

[adamsm]

I'm sorry but it does not clear the range from the sheet where the code is embedded. What may be the reason for this?

 

[VoG]

Unclear. Do you want to clear the whole sheet or the cell that you typed the sheet name into?