Data Validation Navigation - only once variable

[bluegrassstateworker]

I have the below code in use (thanks, L. Howard Kittle ) and noticed
that while my navigation is correct, every time I hit the enter key in
subsequent cells, I am annoyingly navigated to the RFP worksheet (which
I only need to do so once). The code is below:
*********
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E23").Value = "RFP" Then
Sheets("RFP").Activate
Sheets("RFP").Range("A1").Select
End If
End Sub
*********
My thought was to define a variable and include in the If statement to
read something like:
IF Range(E23) .... AND VARIABLESET=T Then
But, since I would initialize the variable, it would be reset every
time the spreadsheet is open. Anyone have a thought to keep this
routine from running after a value is inserted?

 

[Guest]

Add a VBA module to your workbook. In it, add:

Option Explicit
Global BeenThereAlready As Boolean

In the ThisWorkbook module of your workbook, add:

Private Sub Workbook_Open()
BeenThereAlready = False
End Sub

Finally, change the Worksheet_Change event code you already have to the
following:

Private Sub Worksheet_Change(ByVal Target As Range)
If (Range("E23").Value = "RFP") And _
(BeenThereAlready = False) Then
Sheets("RFP").Activate
Sheets("RFP").Range("A1").Select
BeenThereAlready = True
End If
End Sub

This will take the user to the RFP sheet only the first time he/she enters
RFP in cell E23.

Hope this helps,

Hutch